TEXT function

TEXT syntax

Argument	Required	What it means
value	Yes	The number or date to show: usually a cell such as A2, or a calculation.
format_text	Yes	The format code, in double quotes, such as "0.00", "$#,##0" or "d mmm yyyy". The same codes Format Cells uses under Custom.

Examples

A money amount inside a sentence

Joined straight on, B2 would show as 18420.5. TEXT gives it a currency sign, a thousands separator and two decimals first.

D2fx="Sales in "&A2&": "&TEXT(B2,"$#,##0.00")

Row	A	B	C	D
1	Month	Sales		Label
2	March	$18,420.50		Sales in March: $18,420.50

="Sales in "&A2&": "&TEXT(B2,"$#,##0.00") → Sales in March: $18,420.50Copy

A date inside a label

A date is a number underneath, so ="Due "&A2 shows Due 46090. TEXT turns the number back into a date you can read.

B2fx="Due "&TEXT(A2,"d mmmm yyyy")

Row	A	B
1	Due date	Label
2	9 Mar 2026	Due 9 March 2026

="Due "&TEXT(A2,"d mmmm yyyy") → Due 9 March 2026Copy

The day of the week

"dddd" is the day's full name and "ddd" its short name, Mon.

B2fx=TEXT(A2,"dddd")

Row	A	B
1	Date	Day
2	9 Mar 2026	Monday

=TEXT(A2,"dddd") → MondayCopy

A percentage with one decimal

The % in the format multiplies by 100 and adds the sign; "0.0" keeps one decimal.

D2fx=TEXT(A2/B2,"0.0%")&" of target"

Row	A	B	C	D
1	Sales	Target		Progress
2	41,200	50,000		82.4% of target

=TEXT(A2/B2,"0.0%")&" of target" → 82.4% of targetCopy

Practise it

New data, so you can't copy the answer above. We check the number your formula returns, then change the data to make sure it still holds.

Your task: In B2, build the invoice code: INV- followed by the number in A2, padded with zeros to five digits.

Graded live

B2fx

Row	A	B
1	Number	Code
2	42

Write the formula in the bar above, then press Check or Enter.

Hint

Join "INV-" to TEXT(A2,"00000"). Each 0 in the format is a digit, filled with a zero when the number is shorter.

Solution

="INV-"&TEXT(A2,"00000") Any formula that returns the same result passes.

Check

Common mistakes

1. Calculating with the result. TEXT returns text. SUM skips text, so a column of TEXT results adds up to zero. Keep the original numbers for maths and use TEXT only where the result is read.
2. Using mm for minutes. On its own, mm is the month. It means minutes only straight after h or straight before s, as in "h:mm" or "mm:ss".
3. Copying a format code to a computer set to another language. The letters are read in the language of the computer's region settings. On a computer set to German the day is T and the year J, so "dd.mm.yyyy" doesn't give that user the date you meant.
4. Expecting TEXT to change the cell's own value. TEXT returns a new value in another cell. To make a number only look different and stay a number, format the cell instead (Format Cells > Number).

The format codes you’ll use most

The examples show how each code displays 1234.5, 0.285, 42, or the date 9 March 2026 (a Monday) at 14:05, as English Excel shows them.

Code	What it shows
0	A whole number, rounded: 1235
0.00	Exactly two decimals: 1234.50
#,##0	A thousands separator: 1,235
$#,##0.00	Currency: $1,234.50
0% and 0.0%	A percentage: 29% and 28.5%
00000	Leading zeros, five digits in all: 00042
d and dd	The day: 9 and 09
ddd and dddd	The day’s name: Mon and Monday
m and mm	The month: 3 and 03
mmm and mmmm	The month’s name: Mar and March
yy and yyyy	The year: 26 and 2026
h:mm and h:mm AM/PM	The time: 14:05 and 2:05 PM

A code mixes these with your own characters: "d mmm yyyy" shows 9 Mar 2026. Codes aren’t case-sensitive, so "DD/MM/YYYY" works too.

Codes that change with your language and region

TEXT reads its codes in the language of the computer’s region settings, which can differ from the language of Excel’s menus. The codes above are English. On a computer set to German, the day is T and the year J, so the same date there is "TT.MM.JJJJ". A TEXT formula written on one computer can show the wrong thing when it’s opened on another.

The separators follow your regional settings too: the comma between thousands is a full stop in many countries, and the decimal point a comma. If a file is shared across countries, keep dates and numbers as real values and format the cells with Excel’s built-in date and number formats, which follow each reader’s regional settings. Use TEXT only where you need the result as text.