Function guides · Text
TEXT function
Converts a number or date to text in a number format you specify.
Syntax
=TEXT(value, format_text)
| Argument | What it is |
|---|---|
value | The number or date to format. |
format_text | A number format code in quotes, such as "0.00", "#,##0" or "mm/dd/yyyy". |
What TEXT does
TEXT turns a number or a date into text, using the number format you specify. It exists because joining a date to a sentence with the ampersand gives digits, not a date.
The second argument is a format code in quotes, the same codes you meet in the Format Cells dialog under Custom. “0.00” for two decimals, “mm/dd/yyyy” for a date, “0000” for a padded number, “mmmm” for a month name.
Since the result is text, TEXT is for presentation: messages, labels, codes. For calculations, keep the number.
Format codes are the same ones you see under Custom in the Format Cells dialog. If you are unsure of a code, set a cell to the format you want, open Format Cells, and copy the code from the Custom list. Pasting it between the quotes in TEXT gives the same display.
TEXT examples
1. A date inside a sentence
| A | B | |
|---|---|---|
| 1 | Invoice date | |
| 2 | 3/14/2026 | Due on March 14, 2026 |
Joining a date to text with the ampersand gives the serial number. TEXT applies a format first. Here the invoice date becomes "March 14, 2026" and the sentence reads normally.
2. Pad a number with leading zeros
| A | B | |
|---|---|---|
| 1 | Number | |
| 2 | 932 | 0932 |
A format of 0000 shows at least four digits, adding zeros at the left. Part number 932 becomes 0932. The result is text, so it stays 0932 when joined into a code.
3. Thousands and decimals
| A | B | |
|---|---|---|
| 1 | Total | |
| 2 | 1234.5 | 1,234.50 |
The format "#,##0.00" adds a thousands separator and always shows two decimals, so 1234.5 becomes 1,234.50. Use it when the number is part of a message rather than a cell that stays numeric.
Common TEXT mistakes
- Forgetting that the result is text. A cell with TEXT cannot be summed or compared as a number. Keep the original number in its own cell.
- Format codes in the wrong locale. Codes such as "dd/mm/yyyy" and the decimal separator depend on regional settings in some Excel versions. Test on the target setup.
- Missing quotes around the format. The format is a string.
TEXT(A2,0.00)does not raise an error: Excel reads 0.00 as the number 0, uses the format "0", and quietly returns a whole number. - Using m for minutes and months. In a date format, mm after h is minutes and otherwise months. "mmm" gives Mar, "mmmm" gives March.
TEXT on Excel job tests
TEXT fixes two classic problems: a date or number that turns into digits when joined with text, and an ID that loses its leading zeros. Tasks read "build a message that includes the date" or "show the product number as four digits".
Six format codes cover most of it: "0.00" for decimals, "#,##0" for thousands, "0%" for percent, "mm/dd/yyyy" for dates, "mmmm" for the month name, "dddd" for the day name.
Check whether the task wants a real number. If it does, use ROUND or a number format instead, because TEXT hands back text.
Related functions
TEXT questions
- What does the TEXT function do?
- It converts a number or date to text using a format code you supply.
- How do I show a date as the month name?
=TEXT(A2,"mmmm")gives March, and"mmm"gives Mar.- How do I keep leading zeros?
=TEXT(A2,"0000")pads to four digits. The result is text.- Why can I not add cells that use TEXT?
- TEXT returns text. Convert back with VALUE, or add the original numbers.
Practice TEXT
Try it in a working grid: the free lesson DATE, YEAR, MONTH and DAY (module 10 of Everyday Excel) has checked TEXT exercises.
Practice this function