The TEXT Function: Format Numbers and Dates Inside Text

The Excel TEXT function turns a number or date into text in the exact format you choose, such as $1,250.50 or Feb 3, 2025. You need it whenever you put a number or date inside a sentence or label, because joining them with & shows the raw stored value instead. This guide covers the syntax, the format codes you'll use most, and the traps, including codes that change with your language settings.

What does the TEXT function do?#

TEXT takes a value and a format code, and returns the value as formatted text. The syntax:

=TEXT(value, format_text)

value is the number or date, usually a cell. format_text is the format code, in double quotes, such as "mmm d, yyyy" or "$#,##0.00". TEXT works in every version of Excel.

The examples use this payments table:

Column B holds real dates and column C real numbers; the dates and dollar signs are only cell formatting.

Why do numbers and dates look wrong when you join them to text?#

The & sign joins the value Excel stores, not what the cell shows. Try it:

="Paid on "&B2

The result is Paid on 45691. Excel stores each date as a number of days since January 1, 1900, and Feb 3, 2025 is day 45,691. The same happens with amounts: ="Total: "&C2 gives Total: 1250.5, without the dollar sign or the second decimal. Cell formatting is lost the moment the value becomes part of text. Why Excel dates are numbers explains this in more detail.

TEXT fixes it by applying the format inside the formula.

Excel TEXT function date format codes#

Date codes use d for day, m for month and y for year. The number of letters controls the style. All of these use Feb 3, 2025 in B2:

Mind the m: in a date code, m means month. For minutes in a time, use it next to an hour, as in "h:mm AM/PM".

Number format codes for TEXT#

Number codes are built from a few symbols. 0 is a digit that always shows, # is a digit that shows only when needed, a comma adds a thousands separator, and .00 fixes the number of decimals:

TEXT rounds to the decimals in the code, so 12,400.75 with "#,##0" shows 12,401. Percent codes multiply by 100 for you. Zeros in front, as in "00000", pad short numbers, which is handy for IDs and ZIP codes.

How to put numbers and dates inside a sentence#

Join the pieces with &, and wrap each number or date in TEXT. To build a receipt line in D2:

=A2&" paid "&TEXT(C2,"$#,##0.00")&" on "&TEXT(B2,"mmm d, yyyy")

The result is Acme Corp paid $1,250.50 on Feb 3, 2025. Fill down (Windows: Ctrl+D; Mac: ⌘D) and every row gets the same style. Note the spaces inside " paid " and " on ": without them the words run together. For more on joining, see how to combine text in Excel.

You can combine TEXT with TEXTJOIN too. In Microsoft 365, =TEXTJOIN(", ",TRUE,TEXT(B2:B4,"mmm d")) gives Feb 3, Feb 14, Mar 1. The TEXTJOIN function guide covers its arguments.

TEXT vs Format Cells: which should you use?#

Use Format Cells when you only want a cell to look different, and TEXT when the formatted value must be part of text. Format Cells (Windows: Ctrl+1; Mac: ⌘1) changes the display but keeps the real number, so formulas still work with it. TEXT creates new text, so the result can't be added up or compared as a number.

That means you shouldn't use TEXT on a column you'll calculate with. If you already have, VALUE turns text back into a number: =VALUE("1250.50") returns 1250.5. A tip: the Custom category in Format Cells shows the codes Excel uses, so you can copy one into TEXT. For cell formats, see how to format numbers as currency and percent.

Format codes can vary by language and region#

TEXT's format codes depend on the language of your Excel and your regional settings. Some language versions use different letters for day, month and year; German Excel, for example, uses T for day and J for year. Many countries also swap the comma and the decimal point, so "#,##0.00" may need to be "#.##0,00".

This matters when you share files. A TEXT formula written in one language can show the wrong result for a colleague using another. If the value only needs to look right in a cell, Format Cells is safer, because it adapts to each person's settings.

TEXT and other text functions#

TEXT is the opposite of pulling text apart. If you need part of a formatted result, such as the year from a code like INV-2025-0042, use LEFT, RIGHT and MID, or FIND and SEARCH to locate a marker first. And when you build labels from names, such as "Sara joined on Feb 3, 2025", you may first need to split first and last names.

Practise it#

Practise TEXT and VALUE in a live spreadsheet. You'll see the raw date number appear, format dates and dollar amounts with TEXT, build a receipt sentence, and turn imported text digits back into real numbers with VALUE.