ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
1
For what characters to use to construct your custom format click here
WARNING: NOT ALL FORMATTING CHARACTERS ARE ACCEPTED
- apparently the spreadsheet format uses a different library than the TEXT function! -		123-button formatsUser defined formats created with crosshatches (#) appear to be unreliable!123-button formatsDecember
2
2
3
Format usedCustom formattedCommentTEXT function formatted resultNameformatted dataFormatCustom format if differentIssues6:06:29 PMh:mm:ss a~000000000000000010000000812:34:56.0001/1/2010 0:00:001900-01-02 00:00:002025-10-26 18:06:29.238 Z1.500000000000E+000
4
0.00000.12346four decimals0.1235Normal12,345.123456789#,##0.###############October 26MMMM d00000000001000000010000012/24/2009 0:00:001900-01-03 00:00:002025-10-26 18:06:29.238 V15.000000000000E+000
5
eeee, yyyy-MM-ddSunday, 2025-10-26Day of the week plus dateSunday, 2025-10-261,000 Rounded1,000#,##010/26/25M/d/yy12/25/2009 0:00:001900-01-04 00:00:002025-10-26 18:06:29.238 Z150.000000000000E+000
6
yyyy G2025 Year and Era2025 1,000.12 2 Decimals1,000.12#,##0.00October 26, 2025MMMM d, yyyy48:3312/26/2009 0:00:001900-01-05 00:00:0012/30/1899 1:30:001.500000000000E+003
7
[h]:mm:ss.SSS2226:06:29.238Hours (>24), minutes, seconds and milliseconds[6]:06:29.238(1,000) Financial Rounded(1,000)#,##0;(#,##0)4/3/1897 21:01:2510/26/2025 18:06:29 M/d/yyyy H:mm:ss12/27/2009 0:00:001900-01-06 00:00:001/12/1900 22:49:4815.000000000000E+003
8
YYYY 'week' w2011 week wYear and week in the year2011 week 1(1,000.12) Financial(1,000.12)#,##0.00;(#,##0.00)1102962:06:29 [h]:mm:ss12/28/2009 0:00:001900-01-07 00:00:00150.000000000000E+003
9
'0'.000000000000000E000'0'.000012345670000E096Scientific format, mantissa range [ 0, 1 ) 0.123456700000000E1071.03E+03 Scientific1.03E+020.00E+0018:06:29H:mm:ss12/29/2009 0:00:001900-01-08 00:00:001.500000000000E+006
10
0.000000000000000E0001.234567000000000E106Scientific format, mantissa range [ 1, 10 )1.234567000000000E106$1,000 Currency$1,000#,##0¤#,##0Currency symbols appears not to be added to the format, or not to be retrieved123,456,789#,##,##0;(#,##,##0)These seem to be no more hard coded! 12/30/2009 0:00:001900-01-09 00:00:0015.000000000000E+006
11
*~###############0.00~1234.56Padded number, padding character used: ~~1234.56$1,000.12 Currency$1,000.12#,##0.00¤#,##0.00Currency symbols appears not to be added to the format, or not to be retrieved(123,456,789)#,##0;(#,##0)These seem to be no more hard coded! 12/31/2009 0:00:001900-01-10 00:00:00150.000000000000E+006
12
kk:mm:ss.SSSkk:00:00.999Just after midnight, 1-24 hours clockkk:00:01.001123.457#,##0.000;(#,##0.000)These seem to be no more hard coded!1/1/2010 0:00:001900-01-11 00:00:001.500000000000E+009
13
HH:mm:ss.SSS00:00:00.999Just after midnight, 0-23 hours clock00:00:00.99910% Percent rounded10%00%When the format is retrieved it only shows 0 ; but 0% can be set as format(123.457)#,##0.000;(#,##0.000)These seem to be no more hard coded!10%'+'86.12%0.869846341/2/2010 0:00:001900-01-12 00:00:0015.000000000000E+009
14
KK:mm:ss.SSS aKK:00:00.999 PMJust after noon, 0-11 hours clockKK:00:01.001 a10.12% Percent10.25%0.000.00%When the format is retrieved it only shows 0.00 ; but 0.00% can be set as format1.01E+030.00E+001/3/2010 0:00:001900-01-13 00:00:00150.000000000000E+009
15
hh:mm:ss.SSS a 12:00:00.999 PMJust after noon, 1-12 hours clock12:00:00.999 PM9/26/2008 Date9/26/2008M/d/yyyywww.google.com1/4/2010 0:00:001900-01-14 00:00:001.500000000000E+012
16
15:59:00 Time15:59:00H:mm:ss5/18/19031/5/2010 0:00:001900-01-15 00:00:0015.000000000000E+012
17
MM-yyyy10-2025Month and year10-20259/26/2008 15:59:00 Date time9/26/2008 15:59:00M/d/yyyy H:mm:ss1/6/2010 0:00:001900-01-16 00:00:00150.000000000000E+012
18
MMM-yyyyOct-2025Month (abbr. name) and yearOct-202524:01:00 Hours24:01:00[h]:mm:ssAppears to be a special format, not compatible with the TEXT function10-202512-20251/7/2010 0:00:001900-01-17 00:00:001.500000000000E+015
19
[m]:ss150:12Time in minutes (>60), seconds150:12Oct-2025Dec-20251/8/2010 0:00:001900-01-18 00:00:0015.000000000000E+015
20
u-L-d E F
u-L-26 E Fyear, month, day, day name (abbrev.), day # in weeku-L-26 E F
Plain text000-555-12345 ABC@STRING@Appears to be a special format, not compatible with the TEXT function123,456,789.12'€' 1,237.571238.56712.3%26:41.23'CHF' 1,234,567,890,123.231/9/2010 0:00:001900-01-19 00:00:00150.000000000000E+015
21
FFday # in weekF45957Set hyperlinkhttp://www.google.comAppears NOT to be set by a format-1234567.00-12345661/10/2010 0:00:001900-01-20 00:00:001.500000000000E+018
22
MMMMOctoberMonth nameOctoberMore formats0,00,000.00123,456,789.12Indian Lakh and Crore formats123,456,789.12'+'1234567.001234568$100$3$33.33333333333331/11/2010 0:00:001900-01-21 00:00:0015.000000000000E+018
23
EEEESundayDay name#VALUE!2008-09-26 Date2008-09-26yyyy-MM-dd10%30.31/12/2010 0:00:001900-01-22 00:00:00150.000000000000E+018
24
m10Minutes1009-26-2008 Date09-26-2008MM-dd-yyyy1/13/2010 0:00:001900-01-23 00:00:001.500000000000E+021
25
M10Month109/26/08 Date9/26/08M/d/yy-101231/14/2010 0:00:001900-01-24 00:00:0015.000000000000E+021
26
s29Seconds299-26-08 Date09-26-08MM-dd-yy0www.google.com1/15/2010 0:00:001900-01-25 00:00:00150.000000000000E+021
27
SSS45957Milliseconds0299/26 Date9/26M/d01/16/2010 0:00:001900-01-26 00:00:001.500000000000E+024
28
[S]10/26/2025 18:06:29Date in milliseconds39706635899-26 Date09-26MM-ddSun 10/26/2025Sun 10/26/20251900-01-27 00:00:0015.000000000000E+024
29
ggJulian dayg4595726 Sep Date26-Sepd-MMMMon 10/27/2025Mon 10/27/20251900-01-28 00:00:00150.000000000000E+024
30
APMMilliseconds in dayA4595726-Sep-2008 Date26-Sep-2008d-MMM-yyyy1900-01-29 00:00:001.500000000000E+027
31
zzTimezone (INCORRECT or server?)z45957September 26, 2008 DateSeptember 26, 2008MMMM d, yyyy1900-01-30 00:00:0015.000000000000E+027
32
ZZTimezone RFC 822 (INCORRECT or server?)Z45957September 26 DateSeptember 26MMMM d1900-01-31 00:00:00150.000000000000E+027
33
vvTimezone generic (INCORRECT or server?)v45957Sep-26 DateSep-26MMM-d1900-02-01 00:00:001.500000000000E+030
34
VVTimezone local (INCORRECT or server?)V459573:59:00 PM Time3:59:00 PMh:mm:ss a1900-02-02 00:00:0015.000000000000E+030
35
D26Day in year263:59 PM Time3:59 PMh:mm a123456789.0003001900-02-03 00:00:00150.000000000000E+030
36
Y11Year - week in year (see: w )201115:59 Time15:59H:mm1900-02-04 00:00:001.500000000000E+033
37
wwWeek - week in year (see: Y )w405441900-02-05 00:00:0015.000000000000E+033
38
WWWeek in monthW459571900-02-06 00:00:00150.000000000000E+033
39
QQQuarter in yearQ45957TRUE'$'12,345.67891900-02-07 00:00:001.500000000000E+036
40
qqStand alone quarterq45957FALSE1900-02-08 00:00:00
41
@@@123456Significant digits123456#N/A1900-02-09 00:00:00
42
#,##,##0.00123,456,789.12Indian Lakh and Crore format123,456,789.121900-02-10 00:00:00
43
#,##0.0000.123Number format, financial, 3 decimal digits0.1231900-02-11 00:00:00D2000
44
#,##0.###0.123Number format, financial, 3 decimal digits0.1230:00:00.9990.00001157407407kk:00:00.0011900-02-12 00:00:00D2,000
45
#,##0.###############0.12345678901235Number format, financial, 15 decimal digits0.1234567890:00:00.001kk:34:56.0001900-02-13 00:00:00D2,000
46
#,##0.##############0.12345678901235Number format, financial, 14 decimal digits0.123456789012351900-02-14 00:00:00
47
#,##0.#############0.1234567890124Number format, financial, 13 decimal digits0.12345678901241900-02-15 00:00:00
48
#,##0.############0.123456789012Number format, financial, 12 decimal digits0.123456789012351900-02-16 00:00:00
49
#,##0.###########0.12345678901Number format, financial, 11 decimal digits0.123456789011900-02-17 00:00:00
50
#,##0.##########0.123456789Number format, financial, 10 decimal digits0.12345678910000000'$' 10,000,000.00'$'* #,###,###,###0.001900-02-18 00:00:00
51
#,##0.#########0.123456789Number format, financial, 9 decimal digits0.1234567891250000'$' 1,250,000.001900-02-19 00:00:00
52
#,##0.########0.12345679Number format, financial, 8 decimal digits0.12345679156250'$' 156,250.001900-02-20 00:00:00
53
#,##0.#######0.1234568Number format, financial, 7 decimal digits0.123456819531.25'$' 19,531.251900-02-21 00:00:00
54
#,##0.######0.123457Number format, financial, 6 decimal digits0.1234572441.40625'$' 2,441.411900-02-22 00:00:00
55
#,##0.#####0.12346Number format, financial, 5 decimal digits0.12346305.1757813'$' 305.181900-02-23 00:00:00
56
#,##0.####0.1235Number format, financial, 4 decimal digits0.123538.14697266'$' 38.151900-02-24 00:00:00
57
#,##0.###0.123Number format, financial, 3 decimal digits0.1234.768371582'$' 4.771900-02-25 00:00:00
58
#,##0.##0.12Number format, financial, 2 decimal digits 0.121900-02-26 00:00:00
59
#,##0.#0.1Number format, financial, 1 decimal digit0.199,999,999.991900-02-27 00:00:00
60
0.00.1Number format, 1 decimal digit0.11900-02-28 00:00:00
61
0.000.12Number format, 2 decimal digits0.121900-03-01 00:00:00
62
0.0000.123Number format, 3 decimal digits0.1231900-03-02 00:00:00
63
0.00000.1235Number format, 4 decimal digits0.12351900-03-03 00:00:00
64
0.000000.12346Number format, 5 decimal digits0.123461900-03-04 00:00:00
65
0.0000000.123457Number format, 6 decimal digits0.1234571900-03-05 00:00:00
66
0.00000000.1234568Number format, 7 decimal digits0.12345681900-03-06 00:00:00
67
0.000000000.12345679Number format, 8 decimal digits0.123456791900-03-07 00:00:00
68
0.0000000000.123456789Number format, 9 decimal digits0.1234567891900-03-08 00:00:00
69
0.00000000000.1234567890Number format, 10 decimal digits0.12345678901900-03-09 00:00:00
70
0.000000000000.12345678901Number format, 11 decimal digits0.123456789011900-03-10 00:00:00
71
0.0000000000000.123456789012Number format, 12 decimal digits0.1234567890121900-03-11 00:00:00
72
0.00000000000000.1234567890124Number format, 13 decimal digits0.12345678901241900-03-12 00:00:00
73
0.000000000000000.12345678901235Number format, 14 decimal digits0.123456789012351900-03-13 00:00:00
74
0.0000000000000000.123456789012350Number format, 15 decimal digits0.1234567890123501900-03-14 00:00:00
75
0.000%12.346%Percentage with custom number of decimals12.346%1900-03-15 00:00:00
76
#,##,##0.000;(#,##,##0.000)123,456,789.123Positive and negative formats, customizable123,456,789.1231900-03-16 00:00:00
77
#,##,##0.000;(#,##,##0.000)(123,456,789.123)Positive and negative formats, customizable(123,456,789.123)1900-03-17 00:00:00
78
'+'0.##############;-0.##############'+'12345678Positive and negative formats, customizable+123456781900-03-18 00:00:00
79
'+'0.##############;-0.##############-12345678Positive and negative formats, customizable-123456781900-03-19 00:00:00
80
0.000‰0.123‰Promilla ‰ accepted!0.123‰1900-03-20 00:00:00
81
0.000‱0.123‱Per 10000 NOT accepted0.123‱1900-03-21 00:00:00
82
¤0.00¤123.45Currency symbol ¤ accepted! Uses locale setting¤123.451900-03-22 00:00:00
83
¤¤0.00¤¤123.45Currency symbol ¤ accepted! Uses locale setting¤¤123.451900-03-23 00:00:00
84
¤¤¤0.00¤¤¤123.45Currency symbol ¤ accepted! Uses locale setting¤¤¤123.451900-03-24 00:00:00
85
'¥€$' 0.000'¥€$' 123456.789Fictional currencies (e.g. Yen-Euro-Dollar, 'YES')¥€$ 123456.7891900-03-25 00:00:00
86
##0.##E+000 'l'30E-003 'l'Scientific format for ..., kiloliters, liters, ml, µl, ...30E-003 l1900-03-26 00:00:00
87
##0.##E+000 'l'329E-006 'l'Scientific format for ..., kiloliters, liters, ml, µl, ...329E-006 l1900-03-27 00:00:00
88
##0.##E+000 'l'3E+003 'l'Scientific format for ..., kiloliters, liters, ml, µl, ...3E+003 l1900-03-28 00:00:00
89
1900-03-29 00:00:00
90
1900-03-30 00:00:00
91
1900-03-31 00:00:00
92
1900-04-01 00:00:00
93
1900-04-02 00:00:00
94
1900-04-03 00:00:00
95
1900-04-04 00:00:00
96
1900-04-05 00:00:00
97
1900-04-06 00:00:00
98
1900-04-07 00:00:00
99
1900-04-08 00:00:00
100
1900-04-09 00:00:00