Text function
Excel TEXT function
TEXT converts a number or date into text using a format code you choose, like "$#,##0.00" or "mmm d, yyyy". Use it whenever you join a number into a sentence and want it to look the same as it does in a formatted cell.
=TEXT(value, format_text)Arguments
value- The number or date to format.
format_text- A format code in quotes, the same codes used in Format Cells, like "0.0%" or "dd/mm/yyyy".
TEXT examples
Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Invoice | Amount | Due | Paid % |
| 2 | INV-301 | 1834.5 | 2025-03-14 | 0.375 |
| 3 | INV-302 | 260 | 2025-04-01 | 1 |
| 4 | INV-303 | 12950 | 2025-04-20 | 0 |
Result: $1,834.50
Formats the amount as currency: "$1,834.50".
Result: Mar 14, 2025
Turns the due date into "Mar 14, 2025".
Result: 38%
Shows the fraction as a percentage: "38%".
Result: Invoice INV-303 for $12,950 is due Sunday
Builds a sentence with nicely formatted numbers and the weekday.
How to use TEXT
- Type =TEXT( and select the number or date.
- Add a comma and a format code in quotes.
- Close the bracket.
- Join it with other text using & if needed.
Common TEXT mistakes
Doing math on the result
TEXT returns text, not a number. Keep the original number for calculations and use TEXT only for display.
Using "mm" for minutes
"mm" next to "h" means minutes, but on its own it means month. Use "hh:mm" for time.
Format codes differ by language
Some non-English versions of Excel use different date letters, so shared workbooks may show errors.
TEXT FAQ
How do I keep number formatting when concatenating in Excel?
Wrap the number in TEXT: ="Total: "&TEXT(B2,"$#,##0.00"). Without it, Excel joins the raw number like 1834.5.
How do I get the day name from a date in Excel?
Use =TEXT(A2,"dddd") for "Monday" or =TEXT(A2,"ddd") for "Mon".
How do I add leading zeros in Excel?
Use TEXT with zeros: =TEXT(A2,"00000") turns 42 into "00042".
Practice TEXT in these lessons
Guides that use TEXT
Related functions
Updated