Converts a number into text according to a specified format.
number- The number, date, or time to format.
format- The pattern by which to format the number, enclosed in quotation marks.
0forces display of zeros if a number has fewer digits than the format specifies. For example,
012.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,
#is similar to
0but does not force the display of zeros on either side of the decimal point. For example,
TEXTcannot contain an asterisk (
TEXTdoes not support the
?pattern in Excellentable.
TEXTdoes not support fractional format patterns.
TEXTsupports the following date and time patterns:
dfor the day of the month as one or two digits.
ddfor the day of the month as two digits.
dddfor the short name of the day of the week.
ddddfor the full name of the day of the week.
mfor 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.
mmfor 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.
mmmfor the short name of the month of the year.
mmmmfor the full name of the month of the year.
mmmmmfor the first letter in the month of the year.
yyfor the year as two digits.
yyyyfor the year as four digits.
HHfor the hour on a 24-hour clock.
hhfor the hour on a 12-hour clock.
ssfor the seconds in a time.
ss.000for milliseconds in a time.
AM/PMfor 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
0 cannot be mixed.
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.
In order to use the TEXT formula, begin typing the formula in the area you would like to display the outcome:
Strengthening support for internal date format
The TEXT function will soon support localisation of dateformats via language codes. This is handy if a product user wishes to convert the cell text into an non-American date format for example Portuguese.
=TEXT(A1,"[$-pt-PT] dd mmmm yyyy")
This will possible when we release a future Excellentable version that includes more global settings.