| Function Category | Text |
| Volatility | Non-volatile |
| Related Functions |
What does the TEXT function do?
The TEXT function converts a number to text in a specified number format.
This can include dates, times and their fragments, financial units, percentages.
Conversion to custom user formats is also possible, which can be created based on existing ones.
The complete list of formats can be found using the Format Cells procedure. This procedure can be accessed through the context menu, which can be opened by right-clicking any cell.
And then select the desired type from the list of number formats.
TEXT function syntax
=TEXT(number, format)| A | B | C | |
|---|---|---|---|
| 1 | Value | Format | Result |
| 2 | 46317 | "mm/dd/yyyy" | 10/22/2026=TEXT(A2,"mm/dd/yyyy") |
| 3 | 46317 | "dddd" | Thursday=TEXT(A3,"dddd") |
| 4 | 1234.5 | "$#,##0.00" | $1,234.50=TEXT(A4,"$#,##0.00") |
| 5 | 0.257 | "0.0%" | 25.7%=TEXT(A5,"0.0%") |
| 6 | 0.6041667 | "h:mm AM/PM" | 2:30 PM=TEXT(A6,"h:mm AM/PM") |
| 7 | 42 | "00000" | 00042=TEXT(A7,"00000") |
The format is a text string in quotes, the same codes as in Format Cells → Custom. The result is text: it’s left-aligned and can’t be summed directly. Date codes depend on the Excel language — in a non-English Excel, "yyyy" may need to be written differently.
TEXT: result and input format
No special formatting required.
Logical values are not converted to their numerical equivalents but become text.
Numbers formatted as text are treated as numbers.
TEXT formula examples
Example 1
To ensure correct sorting of numbers in text format, it may be necessary to add leading zeros to numbers to equalize their length.
The specialist used the TEXT formula with a custom format.
| A | B | C | |
|---|---|---|---|
| 1 | Number | 5 digits | As text: sorts right |
| 2 | 7 | 00007=TEXT(A2,"00000") | ID-00007="ID-"&TEXT(A2,"00000") |
| 3 | 45 | 00045=TEXT(A3,"00000") | ID-00045 |
| 4 | 123 | 00123=TEXT(A4,"00000") | ID-00123 |
| 5 | 1024 | 01024=TEXT(A5,"00000") | ID-01024 |
Each 0 in the format is a required digit, so short numbers are padded with zeros. As text, ID-00007 sorts before ID-00045; without padding, ID-7 would come after ID-45.
Example 2
Customer phone numbers are stored in the database in normalized form, without any separators or symbols. For convenient manual calling from the database, the sales manager used a custom phone number format in the TEXT function.
| A | B | |
|---|---|---|
| 1 | Phone (number) | Readable |
| 2 | 15551234567 | +1 (555) 123-4567=TEXT(A2,"+0 (000) 000-0000") |
| 3 | 12025550143 | +1 (202) 555-0143=TEXT(A3,"+0 (000) 000-0000") |
| 4 | 447700900123 | +44 (770) 090-0123=TEXT(A4,"+0 (000) 000-0000") |
Format codes fill digits from the right: the last 10 go into the (000) 000-0000 mask, and everything left over goes to the leading 0, so a 2-digit country code (44) works too.
Like the article? Help its author! Buy !SEMTools, it has lots of useful instruments to process text data.