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.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
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.