TO_TEXT in Google Sheets: Preserve Formatted Numeric Text

• Introduced: first introduction date is not established by the reviewed official history; no desktop release number is inferred.
• Availability: documented in the rolling Google Sheets service; these examples use the web worksheet workflow.
• Checked: October 14, 2026; official TO_TEXT reference. Documentation and independent models, with no native Sheets execution or wider client certification.

=TO_TEXT(value)

The source cell’s formatting is part of the conversion. This differs from using TEXT with an explicit new pattern. Record the source format and spreadsheet locale when reproducing an exact output string.

The worked controls use explicit source displays in an English/U.S. convention. Google describes the conversion as equivalent to storing a number as text with an apostrophe, but that does not imply that the entry apostrophe appears in the resulting stored characters.

Set up A2:A5 with the following numeric values and source formats. Confirm the source cells display the middle column before applying the formula.

Use a three-decimal numeric format, a two-decimal percent format, a two-decimal currency format and a five-digit custom zero format respectively. In B2 enter =TO_TEXT(A2) and fill through B5.

The expected results are strings retaining those visible formats. The final output has five characters even though its numeric source is 42. It is useful for a label, but formatting the number did not establish that the original identifier was supplied with three leading zeros.

Enter actual text 00042 in D2 using an apostrophe on entry, and numeric 42 in D3 with ordinary number display. =TO_TEXT(D2) preserves 00042; =TO_TEXT(D3) produces 42 as text under that display.

=ISTEXT(D2) is TRUE before conversion, while =ISNUMBER(D3) is TRUE. After converting D3, the resulting text can be used in a label, but the numeric source should remain available for calculations.

Leading zeros cannot be recovered merely by converting a previously normalized number to text. They must come from preserved raw text, an explicit display width or an external identifier specification.

Put numeric 0.375 in G2 and give it a two-decimal percentage display of 37.50%. In H2 use:

="Completion: "&TO_TEXT(G2)

The expected string is Completion: 37.50%. If the source format is deliberately changed to one decimal place, its display becomes 37.5% and the conversion rule yields a label using that representation instead.

If the label must always use two decimal places independently of source formatting, use ="Completion: "&TEXT(G2,"0.00%"). This makes the required pattern explicit in the formula rather than depending on a source cell’s display configuration.

Place numeric 8.126 in J2 and format it to display 8.13. =TO_TEXT(J2) returns the representation 8.13, while =TO_PURE_NUMBER(J2) exposes the original number 8.126.

If the text 8.13 is later parsed back as a number, the result is 8.13. The difference from the original is 0.004. This illustrates why serializing a rounded display is not a lossless numerical backup.

Preserve numeric values for calculations and use display strings at the reporting boundary. A later parser cannot infer hidden digits from a rounded text export.

For an ordinary label such as pending, TO_TEXT returns the label unchanged. For Boolean TRUE, Google’s general nonnumeric pass-through rule means TRUE remains a Boolean rather than becoming guaranteed text. If a workflow requires every output to be textual, specify that conversion and verify the relevant types rather than relying on the function name alone.

The short reference does not establish every error, blank or complex data-type case. Those boundaries remain untested. Exact currency symbols, date strings and decimal punctuation also depend on the documented source display and locale.

Use TEXT for a fixed explicit pattern, TO_PURE_NUMBER to reveal numeric storage and ISTEXT for a type check. Neither changing alignment nor applying a visual cell style proves that a value has become text.

• Google TO_TEXT reference.
• Editorial testing standards.

Examples use fictional data, English function names and comma separators. Expected outputs are independent mathematical calculations or explicitly documented rules, not native Sheets captures. Check the spreadsheet locale when reproducing punctuation-sensitive formulas.