The TEXT function in Excel converts a number or a date into text in the exact format that you choose. It is the tool to use when you want to join a value to a sentence and keep it looking right, for example showing a sales figure as $1,234.50 or a date as Monday, 18 November 2026 inside a message. Without it, Excel shows the raw underlying number, such as 46345 for a date, or 0.256 for a percentage. This guide explains the syntax, shows the most useful format codes in a table, gives worked examples for numbers, dates, times, percentages and phone numbers, shows how to combine TEXT with other functions, and lists the common mistakes and fixes.
Updated November 2026
In this guide
- What the TEXT function does
- Why you need it
- Common format codes
- Format codes for dates and times
- Example 1: format a number as currency
- Example 2: format a percentage
- Example 3: add leading zeros
- Example 4: format a phone number
- Example 5: show a time or a duration
- Combine TEXT with other functions
- Convert a date to text for matching
- TEXT versus cell formatting
- Locale and language issues
- Real-world examples
- Tips for faster work
- Common mistakes
- Practice problems
- Quick recap
What the TEXT function does
TEXT takes a value and a format code and returns the value as text, formatted as the code describes. The syntax is =TEXT(value, format_text). The value can be a number, a date, a cell reference or the result of another formula. The format text is a code in quotation marks, such as “0.00” or “dd/mm/yyyy”. The result is text, not a number, which matters if you want to calculate with it later. Use TEXT for display and keep the original cell for calculations.
Why you need it
When you join a number to text with the ampersand, Excel uses the general format and drops your cell formatting. If A1 holds the date 18 November 2026 and you write ="Due on "&A1, the result is Due on 46345 or a similar serial number. With ="Due on "&TEXT(A1,"dd mmmm yyyy") the result is Due on 18 November 2026. The same is true for money and percentages, so TEXT is common in report titles, invoices, mail merges and dashboard labels.
Common format codes
These are the codes that you will use most often.
| Format code | Value | Result |
|---|---|---|
| 0 | 3.7 | 4 |
| 0.00 | 3.7 | 3.70 |
| #,##0 | 1234567 | 1,234,567 |
| #,##0.00 | 1234.5 | 1,234.50 |
| $#,##0.00 | 1234.5 | $1,234.50 |
| 0% | 0.256 | 26% |
| 0.0% | 0.256 | 25.6% |
| 000000 | 427 | 000427 |
The zero is a required digit, so it shows a zero when there is no digit. The hash sign is an optional digit that is left out when there is none. The comma adds the thousands separator, and the percent sign multiplies the value by 100.
Format codes for dates and times
Date codes are built from d for day, m for month and y for year. Using more letters gives a longer form. The code dd gives a two digit day, ddd gives the short day name such as Mon, and dddd gives the full day name such as Monday. The code mm gives a two digit month, mmm gives Nov, and mmmm gives November. The code yy gives 26 and yyyy gives 2026. For time, h is the hour, mm after the hour is the minute, ss is the second, and AM/PM switches to a twelve hour clock.
| Format code | Result for 18 Nov 2026 |
|---|---|
| dd/mm/yyyy | 18/11/2026 |
| mm-dd-yy | 11-18-26 |
| d mmm yyyy | 18 Nov 2026 |
| dddd | Wednesday |
| mmmm yyyy | November 2026 |
You can check this with a calendar: 18 November 2026 falls on a Wednesday. If you also need the number of days between two dates, see our DATEDIF guide.
Example 1: format a number as currency
If B2 holds 1234.5, the formula =TEXT(B2,"$#,##0.00") returns $1,234.50. To build a sentence, write ="Total due: "&TEXT(B2,"$#,##0.00"), which returns Total due: $1,234.50. Change the dollar sign to another symbol if you need a different currency. If you want no decimals, use “$#,##0”, which rounds 1234.5 to $1,235 in the display.
Example 2: format a percentage
When C2 holds 0.256, =TEXT(C2,"0.0%") returns 25.6%. For a sentence, use ="Growth was "&TEXT(C2,"0.0%"). If you calculate the change in code, our percent change guide shows the formula, and you can wrap it in TEXT for a clean label. For instance, a rise from 80 to 100 is 25 percent, and =TEXT((100-80)/80,"0%") returns 25%.
Example 3: add leading zeros
IDs, ZIP codes and product codes often need leading zeros. Excel removes them from numbers, so 427 does not stay 000427. Use =TEXT(427,"000000") to return 000427. For a ZIP code stored as 2134, =TEXT(2134,"00000") returns 02134. Because the result is text, it keeps its zeros when you copy it as values.
Example 4: format a phone number
If a cell holds the ten digit number 5551234567, you can use =TEXT(A2,"(000) 000-0000") to return (555) 123-4567. The number must be a real number and not text with spaces or dashes. This is a quick way to clean a list before you export it, though the result is text, so keep the original column.
Example 5: show a time or a duration
For a time value of 0.75, which is 6 PM because a day is 1, =TEXT(0.75,"h:mm AM/PM") returns 6:00 PM. For durations over 24 hours, put the hour code in square brackets. A total of 30.5 hours is stored as 1.2708 days, and =TEXT(1.270833,"[h]:mm") returns 30:30. Without the brackets, Excel would wrap around and show 6:30. Our minutes to hours converter is useful for working with time totals.
Combine TEXT with other functions
TEXT often appears inside other formulas. With TODAY, ="Report date: "&TEXT(TODAY(),"dd mmmm yyyy") shows the current date in the text. With IF, =IF(D2>0,"Profit of "&TEXT(D2,"$#,##0"),"Loss of "&TEXT(-D2,"$#,##0")) writes a different message for positive and negative values. With SUM, ="Total: "&TEXT(SUM(E2:E20),"#,##0.00") adds a formatted total. For more on conditions, see our IF statements guide.
Convert a date to text for matching
TEXT also helps when you need the month or the day name as a label, for example to group sales by month. =TEXT(A2,"mmm") returns Nov for a date in November, and =TEXT(A2,"dddd") returns the weekday. These labels can then be used in COUNTIFS or SUMIFS to build a summary, as in our SUMIFS guide. The text result will not sort in calendar order, though, so keep a month number column for sorting.
TEXT versus cell formatting
Cell formatting changes only how a value looks, and the value remains a number that you can calculate with. TEXT creates a new text value. Use cell formatting for the data itself and TEXT when you need the formatted value inside a sentence, a label or a file that is exported as text. If you try to add two TEXT results, Excel may still convert them, but it is better to calculate first and format last.
Locale and language issues
Format codes depend on the language of Excel. In some regional settings the decimal separator is a comma and the codes for the year or the day use other letters, such as j for jour in French. If a workbook is shared with people who use another language, a TEXT formula may show wrong results or the value error. In that case, test the file on the other setup, or use cell formatting for the shared version.
Real-world examples
In a sales report, a title cell can read ="Sales for "&TEXT(A1,"mmmm yyyy"), which shows Sales for November 2026 and updates when the date changes. In an invoice, a line such as ="Amount due by "&TEXT(B1+30,"dd mmm yyyy") adds thirty days to the invoice date and shows the due date in a clean form. In a dashboard, a card label can be =TEXT(C1,"#,##0")&" orders", which turns 12500 into 12,500 orders. In a mail merge, TEXT makes sure that the amounts and dates in each message look the same as in your sheet. These small touches make a workbook look professional, and they save you from fixing labels by hand each month.
Tips for faster work
Build the format code on a test cell first. Apply a custom number format through Format Cells, test it on a sample value, and then copy the same code into your TEXT formula. Keep the codes in one helper cell if you use them in many formulas, so you can change them in a single place. Wrap long text formulas with line breaks in the formula bar to read them better. Finally, document the codes you use in a note, because other people who open the file may not know what each letter means.
Common mistakes
The first mistake is to forget the quotation marks around the format code. The second is to use mm for the month when you mean the minute, since after h or before ss the same letters mean minutes. The third is to try to calculate with the text result, which can give a value error. The fourth is to use a format code that Excel does not know, which returns the code as plain text. The fifth is to expect TEXT to round the stored value, since it only changes the display text. The sixth is to forget the square brackets for hours that pass 24.
Practice problems
Try these on your own. Problem 1: format 0.075 as a percentage with one decimal, which should give 7.5%. Problem 2: format 9876543 with thousands separators, which gives 9,876,543. Problem 3: show the date 1 January 2027 as Friday, which is the correct weekday for that date, using dddd. Problem 4: write a formula that makes the sentence Total: $2,500.00 from the number 2500. Problem 5: add leading zeros to make 89 appear as 000089.
Quick recap
TEXT converts a value to formatted text with the syntax TEXT(value, format_text). Use 0 and # for numbers, the comma for thousands, the percent sign for percentages, and d, m and y for dates. Use square brackets for hours above 24, and keep the original cell for calculation. Combine TEXT with the ampersand, IF, SUM and TODAY to make clear labels, and remember that the result is text.
Frequently asked questions
What does the TEXT function do in Excel?
It converts a number or date to text in the format that you choose.
How do I show a date as Monday in Excel?
Use =TEXT(A1,”dddd”).
How do I add leading zeros with TEXT?
Use a format code such as “000000”.
Why does TEXT return a number as text?
The function is designed to return text, so keep the original cell for calculations.
What is the difference between TEXT and cell formatting?
Cell formatting changes the display only, while TEXT creates a new text value.


