TEXT Function in Excel: Format Numbers and Dates in Formulas
Use the TEXT function to format numbers, currency, percentages and dates inside formulas, plus UPPER, LOWER and PROPER for changing text case.
By Ayoub Farid · · 1 min read
When you join a number to text, Excel drops its formatting. ="Total: "&B2 shows "Total: 12900.5" instead of "Total: $12,900.50". The TEXT function fixes that by formatting a value as text, exactly how you specify.
Syntax
=TEXT(value, format_text)Common format codes
| You want | Formula | Result |
|---|---|---|
| Thousands separator | =TEXT(B2,"#,##0") | 12,901 |
| Two decimals | =TEXT(B2,"#,##0.00") | 12,900.50 |
| Currency | =TEXT(B2,"$#,##0.00") | $12,900.50 |
| Percentage | =TEXT(C2,"0.0%") | 18.3% |
| Leading zeros | =TEXT(D2,"00000") | 00042 |
| Date | =TEXT(E2,"dd mmm yyyy") | 15 Mar 2026 |
| Day name | =TEXT(E2,"dddd") | Sunday |
| Month name | =TEXT(E2,"mmmm") | March |
Build readable sentences
="Sales were "&TEXT(B2,"$#,##0")&" in "&TEXT(E2,"mmmm")→ "Sales were $12,901 in March"
Important: the result is text
TEXT returns text, not a number. You can't SUM it, and it may sort oddly. Use TEXT for labels and display. For values you'll calculate with, use cell formatting or ROUND instead.
Date codes and your language
Date codes depend on your Excel language settings. "yyyy" works in English versions; some other language versions use local letters (for example "jjjj" in French or German Excel). If a date format gives odd results, check the codes for your language.
Changing case: UPPER, LOWER, PROPER
=UPPER(A2) SAM LEE
=LOWER(A2) sam lee
=PROPER(A2) Sam LeePROPER is great for cleaning names typed in all caps. Combine with TRIM: =PROPER(TRIM(A2)).
Frequently asked questions
How do I format a number inside a formula in Excel?
Use =TEXT(value,"format"), e.g. =TEXT(B2,"$#,##0.00").
Why can't I sum the results of TEXT?
TEXT returns text. Keep the original numbers for calculations.
How do I get the month name from a date?
=TEXT(A2,"mmmm") for the full name, "mmm" for the short name.
How do I capitalise the first letter of each word?
Use =PROPER(A2).