How to Use the TEXT Function in Excel: Format Dates, Numbers and Percentages

shape
shape
shape
shape
shape
shape
shape
shape
How to Use the TEXT Function in Excel: Format Dates, Numbers and Percentages - featured image

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

  1. What the TEXT function does
  2. Why you need it
  3. Common format codes
  4. Format codes for dates and times
  5. Example 1: format a number as currency
  6. Example 2: format a percentage
  7. Example 3: add leading zeros
  8. Example 4: format a phone number
  9. Example 5: show a time or a duration
  10. Combine TEXT with other functions
  11. Convert a date to text for matching
  12. TEXT versus cell formatting
  13. Locale and language issues
  14. Real-world examples
  15. Tips for faster work
  16. Common mistakes
  17. Practice problems
  18. 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 codeValueResult
03.74
0.003.73.70
#,##012345671,234,567
#,##0.001234.51,234.50
$#,##0.001234.5$1,234.50
0%0.25626%
0.0%0.25625.6%
000000427000427

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 codeResult for 18 Nov 2026
dd/mm/yyyy18/11/2026
mm-dd-yy11-18-26
d mmm yyyy18 Nov 2026
ddddWednesday
mmmm yyyyNovember 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.

Related guides and free tools

Leave a Reply

Your email address will not be published. Required fields are marked *