TEXT
Converts a value to text with a specified format. Essential for displaying numbers and dates as formatted strings.
TEXT(value, format_text)Arguments
valueThe number or date to formatformat_textThe format code (e.g., "0.00", "dd/mm/yyyy")
=TEXT(A1,"$#,##0.00")Formats number as currency
=TEXT(TODAY(),"dddd, mmmm d, yyyy")Formats date as full text
=TEXT(0.75,"0%")Converts decimal to percentage text
- •Result is text, not a number - can't be used in calculations
- •Use locale-aware formats for international compatibility
- •Common formats: 0.00, #,##0, dd/mm/yyyy, hh:mm:ss
- •Formatting a number with TEXT and then feeding the result into SUM, which fails because you've turned it into text along the way
- •Hard-coding a format like "mm/dd/yyyy" and being surprised when a colleague abroad opens the file and everything looks off - regional settings affect how dates read
- •Reaching for TEXT just to change how a number looks on screen, when a custom cell number format would do the same job and keep the value numeric
VALUEDoes the reverse - turns a text string that looks like a number back into one you can calculate withCONCATENATEPairs naturally with TEXT when you need to drop a formatted number or date into a longer sentenceTEXTJOINHandy once you're joining several TEXT-formatted values together with a delimiterWhy won't SUM work on the result of my TEXT formula?
Because TEXT always hands back a text string, even when it looks exactly like a number. If you need it to stay usable in calculations, format the cell directly instead, or convert it back with VALUE().
How do I add a thousands separator with TEXT?
TEXT(A1,"#,##0") gets you the separator alone. Add currency and two decimals with TEXT(A1,"$#,##0.00").
Do TEXT format codes change depending on the spreadsheet's language?
Yes, and this catches people off guard. Month and day placeholders can differ by locale, so a formula written in one language sometimes needs tweaking when the file is opened in Excel set to another language.
Need to translate a formula using TEXT?
Use our translator to convert your complete formula