TEXT Function in Excel: Format Codes, Dates, and Examples

Last updated July 2026. The TEXT function converts a number into text in a format you choose: =TEXT(A2,"mm/dd/yyyy") turns a serial date into 07/22/2026, and =TEXT(A2,"$#,##0.00") turns 1234.5 into $1,234.50. The syntax is =TEXT(value, format_text), where the second argument is a format code in quotation marks, the same code you would type in the Custom category of the Format Cells dialog. The result is text, not a number, which is the one thing that trips everybody up.

The function earns its keep whenever a number has to appear inside a sentence, a label, or a file name, and it earns it again after a PDF conversion, when you need account numbers with leading zeros preserved or a date column written in one consistent format before it goes into another system. This guide covers the syntax, the format codes worth memorizing, joining text and numbers, and the caveats Microsoft lists in its own documentation.

How do I use the TEXT function in Excel?

Type =TEXT(value, format_text) where value is the cell holding the number and format_text is a format code wrapped in quotation marks. So =TEXT(B2,"0.00%") shows 0.285 as 28.50%, and =TEXT(B2,"#,##0") shows 1234567 as 1,234,567. Excel applies the formatting and returns the result as a text string, which you can then join to other text or send to a system that expects a specific layout.

The fastest way to get a format code right is to steal it. Select a cell that already looks the way you want, press Ctrl+1, choose Custom, and copy the code out of the Type box. Paste that code between quotation marks in your TEXT formula and the output matches exactly. The one thing that does not carry over is color: Microsoft notes that TEXT ignores color codes even when you copy them from the dialog.

What are the most useful TEXT format codes?

A handful of codes cover nearly all business use. Here are the ones worth keeping close, with what each one does to a real value.

Two rules explain most of the codes. A 0 is a digit placeholder that shows a zero when there is nothing there, which is what preserves leading zeros. A # is a digit placeholder that shows nothing when there is nothing there, which is why #,##0 gives you 1,234,567 rather than 0,001,234,567. Semicolons split the code into sections for positive, negative, and zero values, in that order.

How do I combine text and a number in one cell?

Join them with the ampersand and wrap the number in TEXT so it keeps its formatting: ="Total due: "&TEXT(B2,"$#,##0.00")&" by "&TEXT(C2,"mmmm d, yyyy") produces "Total due: $4,120.00 by August 15, 2026". Without TEXT, Excel drops the formatting and prints the raw underlying value, so a date turns into 46226 and a dollar amount loses its comma and cents.

That is the single most common reason to reach for TEXT. Any time a number is going into a sentence, a header, a chart title, or an email merge, the formatting you see on screen does not travel with it. TEXT is what makes it travel.

Why does my TEXT result stop adding up?

Because the output is text, and Excel will not add text. Microsoft flags this directly: TEXT converts numbers to text, which makes them difficult to reference in later calculations. A column of =TEXT(...) results left-aligns in the cell, SUM over it returns 0, and a lookup that expects a number returns #N/A. Nothing is broken, the values simply are not numbers anymore.

The fix depends on what you actually wanted. If you only need the figures to look a certain way, do not use TEXT at all: select the cells, press Ctrl+1, and apply a number or custom format. Formatting changes the display and leaves the underlying value numeric, so the column still sums. Save TEXT for cases where the string itself is the deliverable, and keep the original numeric column alongside it, which is exactly what Microsoft recommends.

If you have inherited a sheet of text numbers and need them back, that is a different repair job, covered in the guide to converting text to numbers in Excel.

How do I keep leading zeros in Excel?

Use =TEXT(A2,"000000") with one zero per digit you want, so a six digit code stored as 4021 becomes 004021. This is the standard fix for account numbers, cost centers, store codes, and ZIP codes that lost their front zeros the moment Excel read them as numbers.

Be deliberate about it. If the code is an identifier you will never do math on, the cleaner long term answer is to format the column as Text before the data goes in, or to import it as a text column, so the zeros never disappear in the first place. TEXT is the repair; a text column is the prevention. Anything that looks like a number but behaves like a label, such as a policy number or an invoice reference, belongs in a text column from the start.

Why does mm return the month instead of minutes?

Excel reads mm as months unless it sits directly next to an hour or a second code. That is why =TEXT(A2,"mm") gives you 07 for a July date rather than the minutes, and why =TEXT(A2,"hh:mm") correctly gives you 14:35. When you want minutes, always pair the code with h or s so Excel knows which one you mean.

The same context rule shows up with date codes generally: d is the day number, dd pads it to two digits, ddd gives Wed, and dddd gives Wednesday. Months follow the same pattern with m, mm, mmm, and mmmm.

Where TEXT fits after a PDF conversion

Converted documents come with formatting problems that TEXT solves neatly. Statement dates land in a mix of layouts, so a helper column of =TEXT(A2,"yyyy-mm-dd") gives you one consistent string to sort or to hand to an import routine that demands ISO dates. Reference numbers arrive stripped of their leading zeros, and a TEXT column rebuilds them. Building a description for an import file usually means gluing a date, a vendor, and an amount together, which is the ampersand pattern above.

One ordering rule saves rework: fix the value types first, format second. If a converted amount is still sitting as text, wrapping it in TEXT just gives you differently formatted text. Get the column numeric, confirm it sums, then decide whether you need a formatted string on top. When the source is a scanned document rather than a digital one, the cleanest starting point is a converter that reads the tables directly, so the figures come in as numbers and dates from the first pass instead of needing rescue formulas. If the documents you are working from are supplier bills, reading them into a spreadsheet automatically saves the whole retyping step.

Once your date column is genuinely a date column, most presentation work is faster with number formatting than with formulas. The walkthrough on changing date formats in Excel covers when to reach for Format Cells instead, and the post conversion cleanup guide puts the whole sequence in order.

Is TEXT the same as changing the number format?

No, and the difference matters. Number formatting changes how a value is displayed while the cell still holds a number, so it keeps working in SUM, VLOOKUP, and every other calculation. The TEXT function creates a new value that is a string, so it displays exactly as specified but no longer participates in math.

Use number formatting for anything that stays in the spreadsheet and gets calculated. Use TEXT when the formatted result is the output itself: a label, a concatenated sentence, a key you are building for a VLOOKUP, or a field going into a file another system will read. Get that choice right and most TEXT headaches disappear.

Can I use TEXT to build a lookup key?

Yes, and it is a good use for it. When two sheets store the same identifier differently, one numeric and one text with leading zeros, a lookup fails even though the values look identical. Building both sides into the same string with =TEXT(A2,"000000") forces a match on identical text. Do the same when you need to join a date and an account into one key, using a fixed date code so both sides format the same way.

Just remember to keep the underlying numeric columns intact so your totals still work. Add TEXT in a helper column, match on it, and pull the real numbers through. That way the key is text and the money stays numeric, which is the arrangement that survives the next round of edits.