- TEXT syntax: a value and a format code in quotes
- Microsoft's TEXT examples and what they return
- The mm catch: month or minutes?
- Leading zeros and dates: one example each
- How to find any format code without memorising it
- Turning the text back into a number
- Writing a TEXT formula, step by step
- My take on TEXT
- Frequently asked questions
The TEXT function in Excel changes the way a number looks by applying a format code to it, so =TEXT(0.285,"0.0%") shows 28.5% and =TEXT(TODAY(),"DDDD") shows the day of the week. The catch is in the name: the result is text, so Microsoft says to keep the original number in its own cell and build any other formulas from that, never from the TEXT result.
Microsoft describes TEXT as useful when you want numbers in a more readable format, or when you want to combine numbers with words or symbols. That second job is where it earns its place. For plain formatting, there's an easier way, which I'll come to.
TEXT syntax: a value and a format code in quotes
The syntax is TEXT(value, format_text). Both arguments are required:
| Argument | What it holds |
|---|---|
| value | The number you want to convert to text, or the cell that holds it |
| format_text | A format code, inside straight double quotes, that defines how the number should look |
Leave the quotes off the format code and Excel shows an error. It's an easy one to miss when you're typing quickly.
Microsoft's TEXT examples and what they return
| Formula | What it returns |
|---|---|
=TEXT(0.285,"0.0%") | 28.5% |
=TEXT(TODAY(),"MM/DD/YY") | Today's date, such as 03/14/12 |
=TEXT(TODAY(),"DDDD") | The day of the week, such as Monday |
=TEXT(NOW(),"H:MM AM/PM") | The current time, such as 1:29 PM |
=TEXT(4.34,"# ?/?") | The fraction 4 1/3 |
=TRIM(TEXT(0.34,"# ?/?")) | 1/3, with TRIM removing the leading space |
=TEXT(12200000,"0.00E+00") | 1.22E+07 in scientific notation |
=TEXT(12200000,"#,###") | 12,200,000 with thousands separators |
=TEXT(12200000,"#,###.0,") | 12,200.0, scaled down by 1,000 |
The last two rows are worth a second look. A comma enclosed by number signs or zeros adds a separator, but a comma at the end, after the last # or 0, scales the number down by 1,000. Microsoft also points out that the separator itself depends on your regional settings: a comma in the US, a period in some other locales.
Microsoft's currency example puts a dollar sign in front of the code #,##0.00, so 1234.567 comes back as 1,234.57 with the symbol attached; Excel rounds to two decimal places. For any other currency, don't guess the code. Format Cells will show it to you, as below.
The mm catch: month or minutes?
This is the one that trips people up. Microsoft says "MM" or "mm" on its own returns the month. Put it next to hours or seconds and it returns minutes instead. Date codes in TEXT aren't case sensitive, so M and m, D and d, and Y and y all work the same.
| Format code | What it gives you |
|---|---|
"mm" on its own | The month |
"HH:MM" | Hours and minutes |
"MM:SS" | Minutes and seconds |
"H:MM AM/PM" | Time on a 12-hour clock, such as 1:29 PM |
"H:MM" with no AM/PM | Time on a 24-hour clock |
The rule behind it: M is for month, D for day and Y for year; H is for hours, M for minutes and S for seconds. If you leave out AM/PM or A/P, Excel shows the time on a 24-hour clock.
Leading zeros and dates: one example each
Excel looks for numbers, so it drops the leading zeros on part numbers and SKUs. =TEXT(1234,"0000000") puts them back and returns 0001234; the number of zeros in the code is the total number of characters you want. Better still, Microsoft suggests formatting the input range as Text before you paste or type the values. My guide to adding leading zeros in Excel covers every other method.
For dates, =TEXT(B2,"mm/dd/yy") turns the date in B2 into text in that pattern. If you only want a date column to look different, you don't need a formula at all; see how to change the date format in Excel.
How to find any format code without memorising it
- Select a cell and press Ctrl+1 (Cmd+1 on a Mac) to open Format Cells.
- On the Number tab, choose the format you want.
- Select Custom and copy the code shown in the Type box, leaving out any semicolon and @ symbol.
- Paste it into your TEXT formula, inside straight double quotes.
Two limits to know. TEXT doesn't support colour formatting, so a code you copy that includes a colour is accepted, but the colour won't display. And if all you want is a different look, Format Cells does it without a formula: currency format puts the symbol next to the value, while accounting format aligns the symbol left and the value right.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
Turning the text back into a number
If you've already built on a TEXT result and the maths won't work, Microsoft gives two quick conversions: multiply it by 1, as in =D4*1, or use the double-unary operator, =--D4. Either one hands Excel a number again.
Writing a TEXT formula, step by step
- Keep the original number in its own cell.
- In another cell, type
=TEXT( - Point to the value, then type a comma.
- Type the format code in straight double quotes.
- Close the bracket and press Enter.
Where TEXT shines is joining a number to words, for example a label that reads Sales up 28.5% this month instead of 0.285. My guide to concatenating in Excel shows how to join the pieces together.
My take on TEXT
Reach for TEXT when a number has to sit inside a sentence, a label or a file name. For everything else, use Format Cells and leave the value alone. And never build a calculation on a TEXT result: it might look like 28.5%, but to Excel it's just words. Keep the original, point your formulas at it, and you won't get caught out.
Frequently asked questions
What does the TEXT function do in Excel?
It applies a format code to a number so it displays the way you want, and returns the result as text.
Why won't my TEXT result add up?
TEXT converts numbers to text. Keep the original value in another cell and build your calculations from that.
Why does mm show the month instead of minutes?
On its own mm means month. Next to hh or ss, as in HH:MM or MM:SS, it means minutes.
How do I find the right format code?
Press Ctrl+1, pick the format on the Number tab, choose Custom and copy the code from the Type box.
Can TEXT show a number in red?
No. Microsoft says TEXT doesn't support colour formatting, so a colour in the code won't display.
