Formatting Numbers as Text With TEXT

Cell formatting changes how a number looks in its own cell. But what if you want to drop a formatted number inside a sentence, like "Your total is $1,250.00"? For that you need the value turned into formatted text.

The TEXT function converts a number into a text string using a format pattern you choose. The result is text, ready to be combined with words.

TEXT takes two arguments:

• value the number to format
• format_text a format code in quotes

The format code uses the same symbols as custom cell formats. For example =TEXT(0.25, "0%") returns the text 25%. The output is a string, not a number.

Two symbols control digits:

• 0 forces a digit, padding with a zero if needed.
• # shows a digit only if present, with no padding.

So =TEXT(5, "00") returns 05, while =TEXT(5, "##") returns 5. Use 0 when you want a fixed width, like invoice numbers.

Put a decimal point in the pattern to fix decimal places, and a comma to add thousands separators.

=TEXT(1250.5, "#,##0.00") returns 1,250.50. The #,##0 part groups thousands, and .00 forces exactly two decimals. This is the classic accounting style.

Add a currency symbol directly into the format code. Most symbols can be typed literally inside the quotes.

=TEXT(1250.5, "$#,##0.00") returns $1,250.50. The dollar sign is just part of the pattern. You can place it before or after the number depending on the locale you want.

The percent sign in a format code multiplies the value by 100 and appends a percent sign.

So =TEXT(0.1234, "0.0%") returns 12.3%. The 0.0 keeps one decimal place. Remember the stored value is a fraction like 0.1234; TEXT handles the conversion to a readable percentage.

TEXT also formats dates using date codes: yyyy year, mm month, dd day, and mmmm for the full month name.

If A2 holds a date, =TEXT(A2, "mmmm d, yyyy") returns something like June 18, 2026. This is the easiest way to show a date exactly how you want inside text.

The real power of TEXT appears when you join it with labels using & or CONCAT.

="Total: " & TEXT(B2, "$#,##0.00") produces a clean sentence like Total: $1,250.50. Without TEXT, B2 would dump its raw, unformatted value into the string.

Remember: TEXT returns text, not a number. The result looks like 1,250.50 but you can no longer add, sum, or do math on it directly.

So use TEXT only for display and labels. If you need the value in later calculations, keep the real number and apply rounding or cell formatting instead.

TEXT is great for building fixed-width IDs. Suppose order numbers should always be five digits with leading zeros.

=TEXT(42, "00000") returns 00042. You can prefix a label too: ="ORD-" & TEXT(42, "00000") gives ORD-00042. The 0 symbol guarantees the padding.

Format codes can hold up to four sections separated by semicolons: positive;negative;zero;text. This lets accountants show negatives in parentheses.

=TEXT(-1250.5, "#,##0.00;(#,##0.00)") returns (1,250.50). The first section formats positives, the second formats negatives. It is a clean way to flag a loss without a minus sign.

Test your understanding of the TEXT function.

You can now turn numbers into precisely formatted text:

• TEXT(value, format_text) applies a format code and returns a string.
• 0 forces digits and pads; # shows optional digits; , groups thousands; . sets decimals.
• Use % for percentages, currency symbols for money, and date codes like yyyy mm dd for dates.
• The result is text, so use it for labels and display, not further math.