Text
Showing a number as formatted text
Definition
Showing a number as text with a set digit pattern means giving TEXT the number and the pattern.
Syntax
=TEXT(value, format_text)
Arguments
| Argument | Meaning |
|---|---|
value |
The cell that holds the number to show as text. |
format_text |
The digit pattern in double quotes. "00" pads to two digits; "000" pads to three. |
Worked example
A badge list stores numbers in B2:B4 that must print as two digits.
| A: Staff | B: Number |
|---|---|
| Nia | 7 |
| Omar | 18 |
| Pia | 3 |
Formula: =TEXT(B2,"00")
Result: 07. The pattern keeps two digits, so 7 is shown as 07.
Practice
Cell A2 holds 12. In B2, write a formula that shows that number as three digits, with a leading zero.
- In B2, write a TEXT formula that formats A2 as 000.
- B2 should show A2 formatted as 000.
Step 1 of 2
Enter =TEXT(A2,"000") in B2.
A2 is 12, so three digits is 012.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
B2 uses TEXT.
B2 now shows 012.
The next lesson is Checking whether a cell is a number.
Related functions
TEXT is the function that writes the number as text in that digit pattern.
Common mistake
Leaving the quotes off the pattern, as in =TEXT(B2,00): Excel treats 00 as the number 0, so the result is not 07.