Converts a number into text according to a specified format.
Examples
Sample Usage
TEXT(1.23,"$0.00")
TEXT(A2,"#.###")
TEXT(24,"#.0?")
TEXT(DATE(1969,7,20),"yyyy-MM")
Syntax
TEXT(number, format)
-
number
- The number, date, or time to format. -
format
- The pattern by which to format the number, enclosed in quotation marks.-
0
forces display of zeros if a number has fewer digits than the format specifies. For example,TEXT(12.3,"000.00")
produces012.30
. Numbers which have more digits to the right of the decimal point than the pattern are rounded to the indicated number of places. For example,TEXT(12.305,"00.00")
results in12.31
. -
#
is similar to0
but does not force the display of zeros on either side of the decimal point. For example,TEXT(12.3,"###.##")
produces12.3
.
-
Notes
-
The
format
argument toTEXT
cannot contain an asterisk (*
). -
TEXT
does not support the?
pattern in Google Sheets. -
TEXT
does not support fractional format patterns. -
TEXT
supports the following date and time patterns:-
d
for the day of the month as one or two digits. -
dd
for the day of the month as two digits. -
ddd
for the short name of the day of the week. -
dddd
for the full name of the day of the week. -
m
for the month of the year as one or two digits or the number of minutes in a time. Month will be used unless this code is provided with hours or seconds as part of a time. -
mm
for the month of the year as two digits or the number of minutes in a time. Month will be used unless this code is provided with hours or seconds as part of a time. -
mmm
for the short name of the month of the year. -
mmmm
for the full name of the month of the year. -
mmmmm
for the first letter in the month of the year. -
yy
for the year as two digits. -
yyyy
for the year as four digits. -
HH
for the hour on a 24-hour clock. -
hh
for the hour on a 12-hour clock. -
ss
for the seconds in a time. -
ss.000
for milliseconds in a time. -
AM/PM
for displaying hours based on a 12-hour clock and showing AM or PM depending on the time of day.
-
-
Note that the date/time patterns and
#
or0
cannot be mixed.
See Also
T
: Returns string arguments as text.
FIXED
: Formats a number with a fixed number of decimal places.
DOLLAR
: Formats a number into the locale-specific currency format.