Excel TEXT Function — Format a Number as Text (and the Trap That Stops It Summing)

TL;DR — TEXT takes a number (or date, which is a number) and a format code, and returns a text string formatted that way. Syntax: =TEXT(value, format_text). =TEXT(1234.5, "$#,##0.00") returns the string "$1,234.50"; =TEXT(TODAY(), "yyyy-mm-dd") returns "2026-07-02". The format code is the same language as a custom cell format — but with one enormous difference: a cell format only changes how a number looks while it stays a number, whereas TEXT bakes the formatting into a real string. That string is beautiful inside a sentence and useless in a SUM. Reach for TEXT when a number has to become part of text; never use it on data you still need to calculate.

="Invoice total: " & TEXT(A1, "$#,##0.00")   ' -> "Invoice total: $1,234.50"
=TEXT(A2, "0.0%")                             ' 0.734 -> "73.4%"
=TEXT(B1, "00000")                            ' 42 -> "00042"  (leading zeros)

Almost everyone meets TEXT for the same reason: they glued a number onto some words with &, and the number came out raw and ugly — Total: 1234.5 instead of Total: $1,234.50. TEXT is the fix. But it's also the function people reach for when a cell format would have been the right answer, and then wonder why their column of "numbers" won't add up. This guide gives you the one mental model, the trap that follows from it, and enough of the format-code language to be dangerous.

What you'll learn

• The mental model: TEXT bakes a format into a string; a cell format doesn't
• Why concatenation needs it — & throws formatting away
• The #1 trap: TEXT output is text, so SUM quietly ignores it
• The format-code crash course: 0 vs #, thousands, %, and date codes
• Leading zeros, and the locale gotcha with month names and separators
• The judgment call: TEXT vs a cell number format vs CONCAT/ROUND

The mental model: formatting in two very different places

A number in Excel has a value and a display. Type 1234.5, apply the Currency format, and the cell shows $1,234.50 while the value stored is still 1234.5 — you can add it, average it, chart it. The format is a mask over a number that never stops being a number.

TEXT does something different. It reads the value, applies a format code, and hands back the result as text — the mask and the number fused into a string:

' cell A1 holds 1234.5
A1 with Currency format   -> looks like $1,234.50, VALUE is still 1234.5 (a number)
=TEXT(A1, "$#,##0.00")    -> the string "$1,234.50" (text; no number underneath)

Hold that picture: a cell format changes the costume; TEXT turns the actor into a cardboard cut-out. Everything below follows from it.

Why concatenation needs TEXT

The reason TEXT exists in daily work is that the & operator (and CONCAT, and TEXTJOIN) strips number formatting. Joining text to a number gives you the number's raw value, not what the cell was showing:

="Total: " & A1                        ' -> "Total: 1234.5"     (raw, ugly)
="Total: " & TEXT(A1, "$#,##0.00")     ' -> "Total: $1,234.50"  (formatted)
="Due " & TEXT(B1, "mmmm d")           ' -> "Due July 2"        (B1 is a date)

This is the single most common legitimate use of TEXT: you're building a sentence, a label, a filename, or a chart title, and you need the number to look the way it looks in its cell. & can't carry the format across, so you carry it yourself with TEXT. See CONCAT and TEXTJOIN for the joining side of this pattern.

The trap that bites first: the output won't do math

Because TEXT returns a string, the result is no longer a number — and Excel will not add up text. This is the classic "my formula returns 0" surprise:

' D2:D10 are all =TEXT(C2, "0.00")   -> they look like numbers
=SUM(D2:D10)                          ' -> 0   (SUM ignores text)

The rule that follows is blunt: never wrap a column in TEXT if you still need to calculate with it. If you want the numbers to display with two decimals and a currency symbol, that's a job for the cell's number format, which leaves the values numeric. Use TEXT only at the last step, when the number is on its way into a string and its math days are over. If you already have text-numbers and need them back, that's the inverse function — VALUE.

The format-code crash course

The format_text argument is the same mini-language as Format Cells → Custom. You only need a handful of tokens:

The one distinction to internalise is 0 forces a digit, # is optional. Use 0 where you want padding or a guaranteed decimal place (money: 0.00), and # where you want the digit only if it's significant (#,##0.## shows two decimals only when they exist).

Dates: the most-searched half of TEXT

Dates are numbers, so TEXT formats them too — and this is what most people are actually looking for. The date codes:

=TEXT(A1, "yyyy-mm-dd")            ' -> "2026-07-02"   (ISO, sorts correctly as text)
=TEXT(A1, "dddd, mmmm d, yyyy")    ' -> "Thursday, July 2, 2026"
=TEXT(A1, "mmm-yy")               ' -> "Jul-26"
=TEXT(A1, "h:mm AM/PM")           ' -> "2:30 PM"

• m is minutes or months depending on position — mm after h is minutes, mm on its own is months. mmm = Jul, mmmm = July, mmmmm = J.
• d/dd = day number, ddd = Thu, dddd = Thursday.
• Building an ISO date string (yyyy-mm-dd) is the reliable way to make dates that sort correctly as text — handy for filenames and keys.

Leading zeros and the locale gotcha

Two things that trip people up:

• Leading zeros. IDs, ZIP codes, and part numbers that must keep their zeros are a job for TEXT: =TEXT(42, "00000") → "00042". (Better still, store such codes as text from the start — but TEXT rescues them when they've been stored as numbers.)
• Locale. Month and day names follow the workbook/system language, so "mmmm" yields July on an English system and Juli on a German one. Separators can shift too. If you need a specific language regardless of the reader's machine, prefix the code with a locale tag, e.g. =TEXT(A1, "[$-en-US]mmmm d") forces English month names.

The judgment call: TEXT vs the alternatives

The strong opinion: most of the time you want a cell format, not TEXT. Ask what you're really after:

• You want the number to look right in its cell (currency, %, thousands) and keep calculating → apply a number format. Never TEXT. This is the mistake that breaks totals.
• You're putting the number inside a string — a sentence, label, filename, chart title → this is the real TEXT job. Use it, and only here.
• You want to reduce a value to fewer decimals for actual math (so the stored number changes) → that's ROUND, not TEXT. Formatting isn't rounding, and TEXT isn't either — it changes the display, not the value it hands to the next calculation.
• You need to reverse it — text back into a real number or date → VALUE / NUMBERVALUE or DATEVALUE.

TEXT isn't a formatter for your spreadsheet — it's a formatter for strings. Use it exactly when a number has to stop being a number and become part of text, and never as a shortcut for making a cell look nice.

How ExcelMaster helps

The TEXT format-code language is the kind of thing you re-learn every few months — was it mmm or mon, 0.00 or #.##, and why did the total break? ExcelMaster works from what you're describing — "put the total in this sentence as currency", "show the month as a three-letter abbreviation", "pad these IDs to five digits" — and writes the exact format code, while keeping the underlying data numeric wherever you still need to calculate. You get the string you pictured without discovering three tabs later that SUM now returns zero.

Frequently asked questions

Why does SUM ignore my TEXT cells / return 0?

Because TEXT returns text, not a number, and SUM skips text. If you wrapped a column in =TEXT(...) and now totals come out as 0, you formatted when you should have applied a cell number format (which keeps values numeric). Use TEXT only for numbers that are becoming part of a string.

How do I format a date as text in Excel?

=TEXT(date, "format code"). Common codes: "yyyy-mm-dd" → 2026-07-02, "mmmm d, yyyy" → July 2, 2026, "mmm-yy" → Jul-26. Remember mm means months next to y/d, but minutes next to h.

How do I keep leading zeros with TEXT?

Use 0 tokens for the width you need: =TEXT(42, "00000") → "00042". This is ideal for ZIP codes and IDs — though storing them as text from the start avoids the problem entirely.

What's the difference between TEXT and a cell number format?

A cell number format changes only how a number looks; the value stays numeric and keeps calculating. TEXT converts the number into an actual string with the formatting baked in — great for concatenation, fatal for SUM.

How do I turn TEXT output back into a number?

Use VALUE (or NUMBERVALUE for locale control) for numbers, and DATEVALUE for dates. Or, faster for a quick fix, multiply the text by 1 (=A1*1).

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-02.

Related guides: Excel VALUE & NUMBERVALUE · Excel DATEVALUE & TIMEVALUE · Excel CONCAT · Excel TEXTJOIN · Excel ROUND