How to Use the TEXT Function in Google Sheets
=TEXT(number, format). For example, =TEXT(12.3, "000.00") returns 012.30.
What TEXT does
TEXT turns a value into text with a specific look. Use it to keep leading zeros, fix the number of decimals, or put a formatted date inside a longer sentence built with CONCATENATE or TEXTJOIN. The result is text, so it no longer behaves like a number.
TEXT syntax
=TEXT(number, format)
The number can be a number, a date or a time. The format is a pattern in quotation marks.
Number patterns
0shows a zero when a number has fewer digits than the pattern. Extra decimals are rounded.#works like0but shows no leading or trailing zeros.
Google's help page gives these results:
| Formula | Result |
|---|---|
=TEXT(12.3, "000.00") | 012.30 |
=TEXT(12.305, "00.00") | 12.31 |
=TEXT(12.3, "###.##") | 12.3 |
Date and time codes
The help page lists these codes:
| Code | Meaning |
|---|---|
d, dd | Day of the month, one or two digits |
ddd, dddd | Short or full weekday name |
m, mm | Month number, or minutes next to hours or seconds |
mmm, mmmm | Short or full month name |
yy, yyyy | Two-digit or four-digit year |
HH, hh | Hour on a 24-hour or 12-hour clock |
A step-by-step example
Cell A2 holds the number 12.3 and cell B2 holds the date July 20, 1969.
| A | B | |
|---|---|---|
| 1 | Number | Date |
| 2 | 12.3 | 7/20/1969 |
Dates in the tables are shown in month/day/year order. The results do not depend on your display format.
=TEXT(A2, "000.00")
This returns 012.30. The pattern needs three digits before the decimal point, so Sheets adds a zero. For the date, a year-and-month pattern works:
=TEXT(B2, "yyyy-MM")
This returns 1969-07.
Limits
Google's help page says the format cannot contain an asterisk, that fractional patterns are not supported, and that date and time patterns cannot be mixed with # or 0. The page also lists the ? pattern as unsupported, even though one of its sample formulas uses it, so avoid ?.
Common mistakes
- Forgetting the quotation marks around the format.
- Doing maths on the result. TEXT returns text. Keep the original number for calculations.
- Mixing date and number patterns in one format.
Try it yourself
Use the cells above (A2 and B2). Type your formula into the box, starting with =, then press Check.
Exercise 1: Leading zeros
Write a formula that formats the number in A2 with the pattern 000.00.
Exercise 2: Year and month
Write a formula that formats the date in B2 as year-month using the pattern yyyy-MM.
What to learn next
- Day name from a date: show Monday, Tuesday and so on.
- CONCATENATE: join text from several cells.
- WEEKDAY: turn a date into a day-of-week number.
Syntax and rules in this guide were checked against Google's TEXT help page. See all guides.