TEXT() vs cell formatting: same codes, different consequences
The TEXT function and custom number formats speak the same format-code language ("0.0%", "yyyy-mm-dd", "+0;-0;0") — but TEXT converts the value into a string, while formatting leaves the number intact and changes only what you see. Choosing wrong either breaks downstream math or breaks your concatenations.
The differences at a glance
| TEXT | VALUE | |
|---|---|---|
| Cell still contains | Text — the rendered string | The real number/date, untouched |
| Downstream SUM/sort/chart | Broken or text-sorted | Works normally |
| Usable inside other text | Yes — its whole purpose ("due "&TEXT(A2,"mmm d")) | No — concatenating a formatted cell grabs the raw value |
| Where it lives | In the formula — visible, copies with it | On the cell — invisible in formulas, lost on some exports |
| Compatibility | Universal (verified; format codes match across apps) | Universal, minor locale differences in codes |
Which should you use?
- TEXT() — Building strings: labels, sentences, concatenated dates ("Report for 7/24/2026"), CSV-style exports — anywhere the number is becoming part of prose.
- Cell formatting — Everything still used as a number: currency columns, percentages, date columns, dashboards. If it will ever be summed, averaged, sorted, or charted, format — don't TEXT.
Compatibility (Excel for the web, Sheets & LibreOffice executed; desktop Excel per docs)
TEXT executes identically across every LibreOffice version we test (desktop Excel behavior per docs — we do not run desktop Excel; Google Sheets is executed separately, see each function page), and the format-code dialect (sections, ?/?, [h]:mm, comma scaling) matches — several of our verified recipes lean on exactly that. The reverse trip is VALUE/DATEVALUE when someone TEXTed a column that needed to stay numeric. Excel for the web is a separate application with its own calculation engine, and that one we do execute: all 10 corpus cases for these functions matched the documented values there (recalculated on OneDrive, 2026-09-01); the per-case values are on the individual function pages.
Example formulas
| Number into a sentence | ="Growth: "&TEXT(B2,"+0.0%;-0.0%") |
| Same look, still a number | custom format: +0.0%;-0.0% |
| Undo an accidental TEXT column | =VALUE(A2) |
Full per-version details on each function page: TEXT · VALUE.