The TEXT function in Excel
Last updated
TEXT turns a number or a date into text with the format you choose. For example, =TEXT(B2,"$#,##0.00") turns 1250 into $1,250.00, and =TEXT(A2,"mmmm") turns a date into its month name, such as January. Use it to build labels, because a number joined to text loses its cell format.
TEXT syntax
=TEXT(value, format_text)
- value
- The number or date to format: a cell reference such as
B2, a number or a formula that returns one. Dates work because Excel stores them as numbers. - format_text
- The format code, in double quotes, such as
"0000","#,##0.00"or"mmmm yyyy". It uses the same codes as Format Cells, Custom.
Practice TEXT here
Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.
Bring back the leading zero in ZIP codes
The ZIP codes in column C were stored as numbers, so Boston’s 02108 shows as 2108. In D2, use TEXT to turn the number in C2 into the five-digit code 02108.
| Customer | City | ZIP (number) | ZIP code |
| Northwind Foods | Boston | 2108 | |
| Harbor Logistics | Newark | 7102 | |
| Cedar Books | Chicago | 60601 | |
| Maple Dental | Hartford | 6103 |
How to use TEXT, step by step
- Click the cell for the result and type
=TEXT(. - Click the cell with the number or date, then type a comma.
- Type the format code in double quotes, such as
"$#,##0.00"for a price or"dddd"for a day name. - Close the bracket and press Enter. To build a label, join the result with &, as in
="Total: "&TEXT(B2,"$#,##0.00").
TEXT examples
Keep the leading zero of a ZIP code
=TEXT(C2,"00000")
Returns 02108 for 2108. Each 0 is a digit that is always shown, so missing digits become zeros.
A price label with thousands and cents
="Total: "&TEXT(B2,"$#,##0.00")
Returns Total: $1,250.00. The comma groups thousands, .00 keeps two decimals and $ is shown as is.
The month or day name of a date
=TEXT(A2,"mmmm")
Returns January for 1/15/2026. Use "dddd" for the day name (Thursday), "mmm" or "ddd" for the short forms.
A monthly report label
=TEXT(A2,"mmmm yyyy")&": "&TEXT(B2,"$#,##0.00")
Returns January 2026: $12,480.75. Each TEXT formats one value and & joins the pieces.
Common TEXT problems and how to fix them
| Result | Cause | Fix |
|---|---|---|
| A label shows 46023 or 1250 | A date or number joined with & keeps its raw value: dates are serial numbers and the cell format is ignored. | Wrap the value in TEXT with the format you want: ="Due "&TEXT(A2,"mm/dd/yyyy"). |
| The result won’t add up | TEXT returns text. SUM skips text and some formulas return #VALUE! with it. | Keep calculating with the original number, and use TEXT only for the label. To format a number you still need to calculate with, use Format Cells instead. |
| Letters appear in the result, like dd/01/yyyy | Format codes depend on the language of Excel. In French Excel the day is j and the year a, so "dd/mm/yyyy" isn’t understood there. | Use the codes of your Excel language: "jj/mm/aaaa" in French, "TT.MM.JJJJ" in German, "dd/mm/aaaa" in Spanish and Portuguese. |
| #NAME? or a formula error | The format code is missing its double quotes, so Excel reads it as a name or as part of the formula. | Always put the format in quotes: TEXT(A2,"0000"), not TEXT(A2,0000). |
TEXT vs Format Cells, and format codes by language
Format Cells and TEXT use the same codes but do different things. Format Cells only changes how a cell looks: the value stays a number you can add up. TEXT creates a new value that is text, which is what you need inside a label built with & or CONCATENATE. Format codes also follow the language and region of Excel: a workbook written with "dddd" in English Excel shows the right day name, but the same TEXT formula typed in French Excel needs "jjjj". This practice grid checks number codes (0, #, thousands, decimals, %) and date codes (day, month, year) in each Excel language; time codes and multi-part formats are not graded here.
Frequently asked questions
How do I keep leading zeros in Excel?
Use as many zeros as digits you need: =TEXT(A2,"00000") shows 2108 as 02108. The result is text, which suits codes and IDs.
How do I get the month name from a date?
=TEXT(A2,"mmmm") gives the full name (January) and =TEXT(A2,"mmm") the short one (Jan). Use "dddd" for the day of the week.
Why does my TEXT formula show the code instead of the date?
The codes are probably from another Excel language. English uses d, m and y; French uses j, m and a; German uses T, M and J.
Can I add up the results of TEXT?
No, TEXT returns text. Keep the number in its own cell for calculations, and use TEXT only where the value is shown in a label.
Keep practising
All 5 TEXT exercises, with explanations and progress tracking.
Open the TEXT practice set