The Excel TEXT function takes a date or number and returns it in the format you choose, such as dd-mmm-yyyy for dates or 0.00 for two decimal places. Here is how to write the formula, how to apply it to a column, and the one behaviour that causes most problems.
This guide is deliberately short. It is based on Trump Excel's TEXT function guide. Microsoft's own documentation is on its TEXT function support page.
The TEXT formula has two parts
According to Trump Excel, the syntax is:
=TEXT(value, format_text)
- value is the date or number you want to format, usually a cell reference.
- format_text is a format code written inside double quotes, such as
"0.00"or"dd-mmm-yyyy".
Forgetting the quotes around the format code is an easy slip. The code must go inside them.
Format a column of dates in one step
Say your dates are in A2:A11 and you want them shown as day, short month name and year in column B.
- Click cell B2.
- Type this formula:
=TEXT(A2,"dd-mmm-yyyy")
- Press Enter.
- Copy the formula down the column to B11.
To format a number to two decimal places instead, swap the format code:
=TEXT(A2,"0.00")
One formula for the whole range in newer Excel
If you have Microsoft 365, Excel 2021 or Excel 2024, you can skip copying down. Trump Excel says a single formula pointed at the whole range will spill results into the cells below:
=TEXT(A2:A11,"dd-mmm-yyyy")
In older versions, this does not work. Put the single-cell formula in B2 and copy it down, as in the steps above.
If a spilling formula shows a #SPILL! error instead of results, see how to fix the #SPILL! error in Excel.
The result is text, not a number
This is where most people get caught. Trump Excel notes that the output of TEXT is always text. The cell may look like a date or a number, but Excel treats it as a text string.
That matters if you plan to sort, add up or compare the results later. Two practical options:
- Keep the original values in column A for any calculations, and use the TEXT column only for display, labels or joining with other text.
- If you have already replaced your values with TEXT output and need numbers back, follow how to convert text to numbers in Excel.
If you only want a cell to look different while staying a number, TEXT is the wrong tool. Microsoft covers number formatting options on its format numbers as text and combine text and numbers pages.
