TEXT function
TEXT converts a number or date into text in the layout you choose. It is essential when combining numbers or dates with words in a sentence, but the result is text and cannot be summed.
Example
Read it as a sentence: format 1234.5 with a thousands comma and 2 decimal places, which gives 1,234.50.
A
B
C
D
1
1,234.50
2
3
Syntax
TEXT(value, format_text)
-
value(required) - The number or date to format.
-
format_text(required) - A number-format code in quotes, such as "0.00" or "mmm yyyy".
Returns: The value as formatted text.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=TEXT(1234.567,"#,##0.00") | 1,234.57 | Thousands |
=TEXT(0.256,"0.0%") | 25.6% | Percent |
=TEXT(45351,"mmm d, yyyy") | Feb 29, 2024 | Month name |
=TEXT(1.5,"[h]:mm") | 36:00 | Elapsed hours |
=TEXT(0.5,"h:mm AM/PM") | 12:00 PM | 12-hour |
=TEXT("abc","0.00") | abc | Text input |
=TEXT("12","0.00") | 12.00 | Text number input |
=TEXT(Data!F5,"0") | #DIV/0! | Error |
The Data sheet these formulas read
A cell that starts with = holds that formula.
F
1
2
'123
3
2024-02-29
4
TRUE
5
=1/0
Data
Watch out for
- Result is text and cannot be summed.
- Format codes such as date letters depend on your language settings.
Where it works
- Excel
- TEXT is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Text functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .