Google Sheets TO_TEXT Function: Syntax, Examples & Tips

What Is TO_TEXT?

TO_TEXT converts a number into text. The result looks the same, but Sheets now treats it as a string: it left-aligns by default, it is ignored by SUM and other numeric aggregates, and it can be compared with other text values exactly.

When you point TO_TEXT at a cell, it uses the cell's displayed formatting. If A2 holds 0.25 formatted as a percentage, =TO_TEXT(A2) returns the text "25%"; if A2 holds a date, you get the date as it appears in the cell rather than its serial number.

Why Convert Numbers to Text?

• Matching IDs: Lookups fail when one table stores an ID like 10045 as a number and the other as text. Converting both sides to text makes them match.
• Preserving how a value looks when joining it into labels.
• Keys and concatenated codes: Building composite keys such as =TO_TEXT(A2)&"-"&B2.
• Exporting: Some downstream systems need text fields.

Basic Examples

Convert a number

=TO_TEXT(42)

Returns the text "42". =ISTEXT(TO_TEXT(42)) returns TRUE.

Fix a lookup type mismatch

=VLOOKUP(TO_TEXT(A2), Products!A:C, 3, FALSE)

Looks up a numeric ID in a table where IDs are stored as text.

Keep a cell's formatting in a label

="Completion: "&TO_TEXT(B2)

If B2 is formatted as 87.5%, the label reads "Completion: 87.5%". Without TO_TEXT, concatenation uses the raw value 0.875.

Convert a whole column

=ARRAYFORMULA(TO_TEXT(A2:A500))

TO_TEXT vs. TEXT

If you need a specific look regardless of the source cell's formatting, use =TEXT(A2, "$#,##0.00"). If you just need "the same thing, but as text", TO_TEXT is simpler.

Converting Back

To turn text back into a number, use VALUE (parses text such as "1,200" or "25%"), or TO_PURE_NUMBER to strip formatting from a numeric value. Multiplying by 1 or adding 0 also coerces numeric-looking text in most formulas.

Common Use Cases

• Joining data from different sources where one exports numbers and another exports text.
• Building labels and descriptions that keep currency, percent or date formatting.
• Creating unique keys for deduplication or VLOOKUP/XLOOKUP joins.
• Preserving leading zeros is not something TO_TEXT does on its own — a number like 00123 is already stored as 123. Use TEXT(A2, "00000") for fixed-width codes.

Common Errors

• SUM returns 0: The column was converted to text. Aggregate the original numbers, or convert back with VALUE.
• Unexpected format in the result: TO_TEXT copies the source cell's display. If you get "1.2E+10", the source is formatted in scientific notation; change the cell format or use TEXT with an explicit pattern.
• Lookup still fails: Check for stray spaces with TRIM, and make sure both sides are the same type.

Tips and Best Practices

• Convert at the point of comparison (inside the lookup) rather than converting entire data columns.
• Use TEXT when the output format must be guaranteed.
• After converting, test with ISTEXT if you're unsure what type you have.

Related Functions

TEXT, VALUE, TO_PURE_NUMBER, TO_DOLLARS, TO_PERCENT, TO_DATE, and ISTEXT.