How to Use TEXT in Google Sheets: Comprehensive Guide

TEXT converts a number or date into text formatted the way you specify. It is how you show 0.25 as 25%, turn a date into March 15, 2026 or Mon, pad 42 to 00042, or put a nicely formatted amount into a sentence.

Syntax

=TEXT(number, format)

number: The number, date or time to format.
format: A format pattern in quotes, such as "0.00", "#,##0", "0%" or "yyyy-mm-dd".

The result is text, not a number. That is what you want for display and for joining with other text, but it can't be used in further calculations without converting it back with VALUE.

Number formats

"0": a digit, padding with zeros. "00000" turns 42 into 00042.
"#": a digit, with no padding. "#,##0" adds thousands separators.
"0.00": always two decimals. "0.0": one decimal.
"#,##0.00": thousands separators with two decimals.
"0%": multiplies by 100 and adds a percent sign. "0.0%" shows one decimal.
"$#,##0.00": currency. Any symbol can be added as literal text.
"0.00E+00": scientific notation.

=TEXT(1234.5, "#,##0.00")

Returns 1,234.50.

=TEXT(0.256, "0.0%")

Returns 25.6%.

=TEXT(42, "00000")

Returns 00042. A common way to build IDs and ZIP codes with leading zeros.

Date and time formats

"yyyy" year (2026), "yy" (26)
"mmmm" full month (March), "mmm" (Mar), "mm" two-digit month (03), "m" (3)
"dddd" full weekday (Sunday), "ddd" (Sun), "dd" two-digit day (15), "d" (15)
"hh" hours, "mm" minutes, "ss" seconds, "am/pm" for a 12-hour clock

=TEXT(A2, "yyyy-mm-dd")

Returns 2026-03-15.

=TEXT(A2, "dddd, mmmm d, yyyy")

Returns Sunday, March 15, 2026.

=TEXT(A2, "mmm yyyy")

Returns Mar 2026, a useful label for grouping by month.

=TEXT(B2, "hh:mm")

Returns a time such as 14:30.

Months versus minutes: mm means months when it appears with date parts and minutes when it follows hours (hh:mm). Sheets decides from the context, so keep date and time codes in their natural order.

Practical patterns

Put formatted numbers in a sentence

="Revenue was " & TEXT(B2, "$#,##0") & " (" & TEXT(C2, "0.0%") & " growth)"

Without TEXT, joining would show the raw number, such as 1234567.891, rather than $1,234,568.

Weekday or month name from a date

=TEXT(A2, "dddd")

Group by month or year

=TEXT(A2, "yyyy-mm")

Produces 2026-03, which sorts correctly as text and works well as a key for QUERY or SUMIF.

Pad numbers to a fixed width

=TEXT(A2, "000")

Elapsed time

=TEXT(B2 - A2, "[h]:mm")

The brackets let hours go past 24, so 30 hours shows as 30:00.

Show a number with a unit

=TEXT(A2, "0.0") & " kg"

Phone number layout

=TEXT(5551234567, "(000) 000-0000")

Returns (555) 123-4567.

Whole column

=ARRAYFORMULA(TEXT(A2:A, "yyyy-mm-dd"))

Combine with IF(A2:A = "", "", ...) to keep blank rows blank.

Using TEXT in lookups

Because TEXT returns text, TEXT(A2, "00000") is a way to make a number match a text-formatted ID with leading zeros:

=VLOOKUP(TEXT(A2, "00000"), IDs!A:B, 2, FALSE)

Common errors

#VALUE!. The format string is invalid, or the first argument is text that Sheets can't read as a number. Text such as "abc" can't be formatted as a number.
The result won't add up. It is text. Calculate with the original number and use TEXT only for display.
Dates come out as numbers. Add a date format: TEXT(A2, "yyyy-mm-dd").
mm gives the wrong thing. Months versus minutes confusion. Use mmm for month names or place mm after hh:.
Separators don't match what you expect. Thousands and decimal separators follow the spreadsheet's locale (File > Settings), so #,##0.00 may appear as 1.234,50 in some locales.
Format string without quotes. TEXT(A2, yyyy) fails. Write TEXT(A2, "yyyy").

TEXT vs. number formatting vs. TO_TEXT

Format > Number changes how a cell is displayed but leaves the value a number. TEXT creates a new text value, so use it when the formatted result must be part of a string or must travel to another system as text. TO_TEXT converts without any formatting.

Frequently asked questions

Can TEXT be used in a calculation? Not directly. Convert back with VALUE, or calculate from the original number.

How do I show a negative number in parentheses? =TEXT(A2, "#,##0;(#,##0)"). Sections are separated by semicolons: positive; negative; zero.

How do I add a literal word? Quote it inside the format: TEXT(A2, "0 ""units""").

Does it work on a range? Wrap in ARRAYFORMULA to apply it to every cell.

Related Functions

VALUE: Convert text to a number.
CONCATENATE: Join text pieces.
TEXTJOIN: Join text with a delimiter.
TODAY: Return the current date.
ARRAYFORMULA: Apply a formula to a whole range.

TEXT