How to use TEXT in Excel
Formats a number or date as text.
Syntax
valueA number, date or cellformat_textA format in quotes, like "$#,##0" or "mmm d"Example
Here’s TEXT on a small table of orders. The highlighted cells are the ones the formula reads.
=TEXT(F6,"$#,##0")Edit this formula live Change the ranges and watch the answer update. Free, no sign-up.
Common mistakes with TEXT
The result is text
TEXT(F6,"$#,##0") looks like a number but can’t be summed. Keep the number in one cell and format it with TEXT only for display or labels.
Format codes go in quotes
Write "mmmm d, yyyy" or "0.0%" inside quotes.
What the error messages mean
Type these slips into Gridwise and this is the feedback you get:
Leaving off the closing bracket
=TEXT(F6,"$#,##0"
Parentheses don’t match. You have 1 opening and 0 closing parentheses. Every ( needs a ).
Questions
What does the TEXT function do in Excel?
Formats a number or date as text. The syntax is TEXT(value, format_text).
Why is my TEXT formula giving the wrong answer?
The result is text: TEXT(F6,"$#,##0") looks like a number but can’t be summed. Keep the number in one cell and format it with TEXT only for display or labels. Format codes go in quotes: Write "mmmm d, yyyy" or "0.0%" inside quotes.
Does TEXT work in Google Sheets?
Most Excel functions, including the core ones on this page, work the same way in Google Sheets. A few newer functions arrived in Sheets later or behave slightly differently, so test it on your own data.