TEXT in Excel: how to use it
With TEXT you decide what a number or date looks like inside a text, such as =TEXT(A1,"dd/mm/yyyy").
Try the first exercises for freeIn the lesson The TEXT function
What does TEXT do?
TEXT turns a number or a date into text, in the form you choose. You give the number and a format code, such as "#,##0.00" for an amount with two decimal places, or "dddd" for the name of the day.
You mainly need TEXT when you put a number in a sentence. If you stick a number to a text with &, it loses its formatting. With TEXT, you decide how it appears in the sentence. The result is text, though: you can't calculate with it afterwards.
When do you use TEXT?
- An amount in a sentence: ="Amount due: "&TEXT(B2,"#,##0.00").
- The day of the week for a date: =TEXT(A2,"dddd").
- The name of the month: =TEXT(A2,"mmmm").
- A number with zeros in front, such as 42 as 0042: =TEXT(A2,"0000").
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
Filled in: =TEXT(A1,"#,##0.00")
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
TEXT | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | TEXT |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
value | The number or date you want to show as text. | A1 |
format_text | How it should look, in quotation marks, such as "#,##0.00" or "dddd". | "#,##0.00" |
, | A comma separates the arguments from each other. | , |
" " | You type text in quotation marks, such as "Leeds". A number or a cell, such as 500 or A2, you type without them. |
TEXT step by step
C1 contains =TEXT(A1,"#,##0.00"). A comma for thousands, a point for decimals. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click C1 and type TEXT with a bracket, followed by the cell with the number, A1, and a comma.
=TEXT(A1, - Type the format code in quotation marks. #,##0.00 means: a comma for the thousands and always two digits after the decimal point.
=TEXT(A1,"#,##0.00" - Close the bracket and press Enter: the number becomes the text 1,234.50.
=TEXT(A1,"#,##0.00")
More examples
The day of the week
C1 contains =TEXT(A2,"dddd"). With dddd you get the full name of the day. With ddd you get the short form: Sat.
Zeros in front
C1 contains ="F-"&TEXT(A2,"0000"). Every 0 in the code is a place that's always filled in, with a zero if need be.
An amount in a sentence
C1 contains ="Amount due: "&TEXT(A2,"#,##0.00"). Without TEXT, the sentence would say 89.9.
Codes depend on the language
In an English Excel you use d, m and y: "yyyy" for the year. A colleague with Excel in another language uses other letters, such as "jjjj" in Dutch. That's a well-known trap when you share files.
Where it often goes wrong
- The result is text, so you can no longer calculate with it.
- The codes depend on the regional settings of the computer.
Practise this in the lesson The TEXT function. The first exercises are free.
Frequently asked questions
How do I get Monday, Tuesday...?
With =TEXT(A1,"dddd").
Read next
LEFT, RIGHT and MIDTRIMLENUPPER, LOWER and PROPERSEARCH and FIND