Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
TEXT vs VALUE: converting between numbers and text
Excel & Google Sheets
=TEXT(A2,"#,##0.00")✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Number | Formatted |
| 1234.5 | 1,234.50 |
| 9876 | 9,876.00 |
| 450.75 | 450.75 |
| 10000 | 10,000.00 |
| 33.3 | 33.30 |
=TEXT(A2,"#,##0.00")→1235,##0.00
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1To format a number as text, use =TEXT(number, format_code) — for example =TEXT(A2,"#,##0.00") formats 1234.5 as '1,234.50'.
- 2Common format codes: "0.00" for two decimals, "#,##0" for thousands separator, "yyyy-mm-dd" for dates, "$#,##0.00" for currency.
- 3To convert a text number back to a real number, wrap it in VALUE: =VALUE("1,234") returns 1234 as a number you can sum.
- 4A quicker alternative to VALUE is to multiply by 1 or add 0: =A2*1 coerces a numeric text string to a number.
Tips
- TEXT results look like numbers but are strings — SUM will ignore them. Always use VALUE or arithmetic coercion before summing text-formatted numbers.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Why does my SUM return 0 after using TEXT?
TEXT returns a text string, not a number. SUM cannot add text strings. Convert the values back with VALUE before summing, or avoid TEXT if you still need arithmetic.
How do I format a number as a percentage with TEXT?
Use the format code "0.00%" — but note you must pass the decimal value (e.g., 0.125), not the percentage (12.5), since TEXT will multiply by 100 for you.