TEXT Function

Excel Guides / Text

Text

Turn numbers, percentages and dates into formatted text for labels, chart titles and sentences built with formulas.

Updated 12 Oct 2026 · 5 min read · By Sky Dashboards

Cell formatting changes how a value looks without changing the value. That stops helping the moment you join a number to some words: ="Revenue: "&B2 shows the raw 18450, not $18,450. The TEXT function fixes this by applying a number format inside the formula. It is how you build labels, chart titles and sentences that read properly.

TEXT syntax

The result is always text. That is the point of the function, and also its catch, as explained below.

Format codes for numbers and percentages

The codes are the ones you would type under Format Cells > Custom. 0 always shows a digit, even a zero. # shows a digit only when needed. A comma adds thousands separators, and % multiplies by 100 and adds the sign.

TEXT rounds to the decimals in the code, just as cell formatting does. The last row has two sections separated by a semicolon, the first for positive numbers and the second for negative, which is handy for growth figures that should always show a sign.

Format codes for dates and times

With 15-Jan-26, a Thursday, in A2:

d and dd give the day number, ddd and dddd the weekday name. The m codes give the month, except that m or mm straight after h, or straight before ss, means minutes. For half past six in the evening, "h:mm AM/PM" gives 6:30 PM and "hh:mm" gives 18:30.

Dynamic chart titles and labels

This is where TEXT earns its place. A creator's monthly report keeps the numbers in column B and builds readable labels next to them:

Without TEXT, the same formulas would show Revenue: 18450, Growth: 0.125 and Report for 46082, because joining with & uses the stored value, not what the cell displays.

Link a chart title to a cell

1. Build the title in a cell, such as the label in C2.
2. Click the chart title once to select it.
3. Click in the formula bar, type =, then click cell C2.
4. Press Enter. The title now follows the cell and updates whenever the data changes.

The result is text, so you cannot sum it

TEXT returns a string that looks like a number. Excel treats it like any other word:

• SUM ignores it. A total over a column of TEXT results returns 0.
• It aligns left by default, a quick visual clue that a cell holds text.
• Charts cannot use it as values, and sorting puts "10" before "9".
• Lookups miss. An XLOOKUP for the number 42 will not find the text "00042".

Locale caveat for date codes

The format code inside TEXT is plain text, so it is read according to the language settings of whoever opens the file. Codes like yyyy and dddd work in English versions of Excel, but some languages use other letters. German Excel, for example, uses J for the year, so TEXT(A2,"mmmm yyyy") may not show the year correctly for a colleague in Germany. Month and day names also come out in the reader's language.

Cell number formatting does not have this problem. Excel stores it in a neutral form and translates it when the file opens on another system.

When to use cell formatting instead

TEXT is for building strings. When a value stands on its own in a cell, format the cell and keep the number:

• Totals, KPIs and table values should stay numbers so they can be summed, charted and looked up.
• Dates should stay dates so sorting, filtering and functions like EOMONTH keep working.
• Leading zeros for display only can come from a custom format such as 00000 on the cell, which shows 00042 while storing 42.

Press Ctrl + 1, choose Custom on the Number tab and type the same code you would give TEXT. The custom number formats guide covers the codes in detail. A good rule: if you are about to wrap a cell in TEXT and nothing else, you probably want cell formatting.

Frequently asked questions

Why can I not add up the results of TEXT?

TEXT always returns text, and SUM skips text. Keep the original number for calculations and use the TEXT result only for labels.

How do I show the day of the week from a date?

Use =TEXT(A2,"dddd") for the full name, such as Thursday, or "ddd" for the short form, Thu.

Can I use TEXT in a chart title?

Not directly. Build the title with TEXT in a cell, then select the chart title, type = in the formula bar and click that cell.

Why does my TEXT date look wrong on another computer?

Format codes inside TEXT follow the language settings of the Excel that opens the file. Use cell formatting where you can, or add a locale tag such as [$-409] to the code.

Related guides

All 32 Excel guides