The TEXT Function in Excel

The TEXT function in Excel converts a number into a text string using a format code you supply. It is the right tool for building dynamic labels, like a chart title that says Revenue: 1.2M, because it bakes the formatting into the value itself. That is also its main trap. Once a number passes through TEXT it is text, not a number, and can no longer be used in math.

How the TEXT function works

=TEXT(value, format_text) takes a number in value and returns it as a string formatted by the format_text code. The format code uses the same syntax as custom number formats, so =TEXT(0.125, "0.0%") returns the string 12.5%.

Common codes include "#,##0" for thousands separators, "$#,##0.00" for currency, "0.0%" for percentages, and date codes like "mmm-yy". The format code must be in quotes.

The output is always text. =TEXT(1234.5, "#,##0") returns the string 1,235, which left aligns by default and cannot be summed. This is the difference between cell formatting, which changes only the display while the underlying value stays numeric, and TEXT, which produces a genuine string.

Worked example: a dynamic chart label

Say total revenue sits in B2 as 1240000 and you want a label cell that reads Revenue: 1.2M for a dashboard.

1. In B2 enter the raw number 1240000.
2. In B3 write =TEXT(B2/1000000, "0.0") & "M", which returns the string 1.2M.
3. To prefix a caption, write ="Revenue: " & TEXT(B2/1000000, "0.0") & "M", giving Revenue: 1.2M.
4. Reference B3 from a chart title or a text box, and it updates automatically when B2 changes.
5. Note that B3 is now text. Trying =B3*2 returns a #VALUE! error because 1.2M is a string, not a number.

Every TEXT result is a string, so keep these in label cells, not in calculation chains.

How analysts use it in models

TEXT shines anywhere a number and words have to live inside one cell or one string.

• Build dynamic titles and footnotes, such as As of & TEXT(date, "mmm d, yyyy"), that restate as the model changes.
• Concatenate formatted figures into commentary cells for board packs and investor updates.
• Pad or align identifiers with codes like "0000" so 42 becomes 0042 for consistent account or invoice keys.
• Format dates into text keys, like =TEXT(date, "yyyymm"), to build lookup values that join tables.
• Keep TEXT outputs in a separate presentation layer so your calculation cells stay numeric and auditable.

Pitfalls to watch

The defining trap is that TEXT output cannot be used in arithmetic. If you later SUM a column that ran through TEXT, the text values are ignored or throw errors. When you only need a number to look different, change the cell's number format instead of wrapping it in TEXT.

Format codes are locale sensitive. A code written with a comma as the thousands separator may behave differently on a machine with other regional settings. Test on the target environment.

It is easy to double format. Applying a percent cell format to a cell that already holds =TEXT(x, "0.0%") does nothing useful, since the value is already a finished string. Pick one approach per cell.