TEXT Function in Excel: Format Numbers and Dates as Text

You have a date in one cell and you want a sentence that says “Report for January 2026”. Concatenating the date gives you “Report for 46023” — the raw serial number behind the date. TEXT fixes that: it converts a number or date into text formatted exactly the way you choose, so it fits inside labels, file names, and sentences.

What TEXT does

TEXT takes a value and a format code and returns the value as text in that format. The code uses the same patterns as custom number formats: “$#,##0.00” for money, “mmmm d, yyyy” for a long date, “00000” for leading zeros. The result is text, so it can be joined with & into sentences — but it can no longer be used in arithmetic.

The syntax

=TEXT(value, "format_text")

  • value — the number or date to format, for example A2.
  • format_text — the format code in quotes, for example “$#,##0.00” or “mmmm d, yyyy”.

Step-by-step example

Build a report label from a date and an amount.

  1. Click the cell where the label should go.
  2. Type ="Report for "&TEXT(A2,"mmmm yyyy") where A2 holds the date — the result reads “Report for January 2026”.
  3. For money, type ="Total: "&TEXT(B2,"$#,##0.00") — B2 shows as $1,234.50 instead of 1234.5.
  4. For a leading-zero code, type =TEXT(C2,"00000") — the number 42 becomes the text “00042”.
  5. To show a percentage, type =TEXT(D2,"0.0%") — 0.857 becomes “85.7%”.

Worked example

Value Format code Result
2026-01-05 “mmmm d, yyyy” January 5, 2026
1234.5 “$#,##0.00” $1,234.50
42 “00000” 00042
0.857 “0.0%” 85.7%

Each row shows the same idea: the value on the left, the code in the middle, and readable text on the right. Change the code and the text changes — the underlying value never does.

Common errors

The result shows the code, not the value. The format code must be in quotes. Without quotes Excel reads mmmm as a name and returns #NAME? instead.

Months show as numbers. A single m gives the month number; mm gives two digits; mmm gives Jan; mmmm gives January. Pick the length you actually want.

The text cannot be added up. TEXT returns text, so =TEXT(A2,"0")+1 fails. If you need the number later, keep the original value in its own cell and use TEXT only for display.

Dates show as 46023. That is the serial number Excel stores for the date, which means TEXT never ran — usually because the formula was typed without the equals sign or the quotes are missing.

Tip

TEXT is the fix for the most common concatenation complaint: joining a date or a price into a sentence. Whenever a joined value looks like a serial number or loses its decimals, wrap it in TEXT with the format you want to see.

More format codes worth knowing

A few codes cover most real needs. "ddd" gives the short weekday (Mon), "dddd" the full weekday (Monday), and "mmm-yy" gives Jan-26 for compact month labels on charts. For phone-style numbers, TEXT(A2,"000-0000") turns 5551234 into 555-1234, and for IDs the pattern "000000" pads any number to six digits. One warning: format codes follow the workbook locale, so a code that spells out “January” in an English workbook spells out the local month name in a Korean or German one — fine for personal files, something to check before sharing a template internationally.

Practice file

Download the TEXT practice file (.xlsx) — it contains values, format codes, and live TEXT formulas side by side, plus a blank sheet for you to try it yourself.

Google Sheets

Google Sheets supports TEXT with the same format codes. One difference: Sheets uses the spreadsheet locale for month names, so “mmmm” follows the sheet’s language setting rather than your computer’s.

Example prices and figures are illustrative, not market data.