Description
TEXT function converts a numeric value to text and lets you specify the display formatting by using special format strings. This function is useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols.
Syntax
TEXT(value, format_text)Parameters
value Required. A numeric value, a formula that evaluates to a numeric value, or a reference to a cell containing a numeric value.
format_text Required. A numeric format as a text string enclosed in quotation marks, for example "m/d/yyyy" or "#,##0.00". Learn Excel Number Format Guidelines.
Format Code
Use the Custom number format dialog to help build your own custom number formats. It's much easier to modify an existing format than try to build your own from scratch!
You can use the following steps to quickly get the format code:
- Select the cells
- Press Ctrl+1 to launch the Format Cells dialog
- Select any of the build-in formatting code
- Click the Custom category
- Copy the format that's displayed in the Type box into your TEXT formula
The TEXT function doesn't support color formatting.
If the format code already has a quote or double quote, replace all " with "" and then enclose the entire string in double quotes "", learn more about Put Double Quotes in a String. For example:
The default accounting format code:
_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)
If you copy this code to TEXT formula, you'll get an error #VALUE!
The correct code:
_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)
The final full code:
=TEXT(A1,"_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)")
Examples
Thousands separator
| Value | Formula | Result |
|---|---|---|
| 12300000 | =TEXT(A2,"#,###") |
12,300,000 |
| 12300000 | =TEXT(A3,"0,000.00") |
12,300,000.00 |
| 12300000 | =TEXT(A4,"#,") |
12300 |
| 12300000 | =TEXT(A5,"#,###.0,") |
12,300.0 |
| 12300000 | =TEXT(A6,"0.0,,") |
12.3 |
- Excel separates thousands by commas if the format contains a comma (,) that is enclosed by number signs (#) or by zeros.
- A comma that follows a digit placeholder scales the number by 1,000. For example, if the format_text argument is "#,###.0,", Excel displays the number 12,300,000 as 12,300.0.
Number, Currency and Accounting formats
| Value | Description | Formula | Result |
|---|---|---|---|
| 1234.56 | Number - General | =TEXT(A2,"0.00") |
1234.56 |
| 1234.56 | Number - thousands separator, no decimals | =TEXT(A3,"#,##0") |
1,235 |
| 1234.56 | Number - thousands separator, 2 decimals | =TEXT(A4,"#,##0.00") |
1,234.56 |
| 1234.56 | Currency - no decimals | =TEXT(A5,"$#,##0") |
$1,235 |
| 1234.56 | Currency - 2 decimals | =TEXT(A6,"$#,##0.00") |
$1,234.56 |
| -1234.56 | Currency - 2 decimals, negative value | =TEXT(A7,"$#,##0.00_);($#,##0.00)") |
($1,234.56) |
| 1234.56 | Accounting - no decimals | =TEXT(A8,"$ * #,##0") |
$ 1,235 |
| 1234.56 | Accounting - 2 decimals | =TEXT(A9,"$ * #,##0.00") |
$ 1,234.56 |
Currency format with [Red] will color a negative value red when a cell is formatted, but the TEXT function doesn't support text color. For example: =TEXT(-1234.56,"$#,##0_);[Red]($#,##0)") displayed ($1,235).
Date Formats - Months, days and years
| To display | As | Formula | Result |
|---|---|---|---|
| 5/28/2022 | 1–12 | =TEXT(A2,"m") |
5 |
| 5/28/2022 | 01–12 | =TEXT(A3,"mm") |
05 |
| 5/28/2022 | Jan–Dec | =TEXT(A4,"mmm") |
May |
| 5/28/2022 | January–December | =TEXT(A5,"mmmm") |
May |
| 5/28/2022 | J–D | =TEXT(A6,"mmmmm") |
M |
| 5/28/2022 | 1–31 | =TEXT(A7,"d") |
28 |
| 5/28/2022 | 01–31 | =TEXT(A8,"dd") |
28 |
| 5/28/2022 | Sun–Sat | =TEXT(A9,"ddd") |
Sat |
| 5/28/2022 | Sunday–Saturday | =TEXT(A10,"dddd") |
Saturday |
| 5/28/2022 | 00–99 | =TEXT(A11,"yy") |
22 |
| 5/28/2022 | 1900–9999 | =TEXT(A12,"yyyy") |
2022 |
Time formats - Hours, minutes and seconds
| To display | As | Description | Formula | Result |
|---|---|---|---|---|
| 4:57:53 PM | 0-23 | Hours | =TEXT(A2,"h") |
16 |
| 4:57:53 PM | 00-23 | Hours | =TEXT(A3,"hh") |
16 |
| 4:57:53 PM | 0-59 | Minutes, but Excel display Months in this example | =TEXT(A4,"m") |
5 |
| 4:57:53 PM | 00-59 | Minutes, but Excel display Months in this example | =TEXT(A5,"mm") |
05 |
| 4:57:53 PM | 0-59 | Seconds | =TEXT(A6,"s") |
53 |
| 4:57:53 PM | 00-59 | Seconds | =TEXT(A7,"ss") |
53 |
| 4:57:53 PM | 4 PM | Time | =TEXT(A8,"h AM/PM") |
4 PM |
| 4:57:53 PM | 4:57 PM | Time | =TEXT(A9,"h:mm AM/PM") |
4:57 PM |
| 4:57:53 PM | 4:57:53 P | Time | =TEXT(A10,"h:mm:ss A/P") |
4:57:53 P |
| 4:57:53 PM | 16:57:52.72 | Time | =TEXT(A11,"h:mm:ss.00") |
16:57:52.72 |
| 4:57:53 PM | 1:02 | Elapsed Time (hours & minutes) | =TEXT(A12,"[h]:mm") |
1073080:57 |
| 4:57:53 PM | 62:16 | Elapsed Time (minutes & seconds) | =TEXT(A13,"[mm]:ss") |
64384857:53 |
| 4:57:53 PM | 3735.80 | Elapsed Time (seconds & hundredths) | =TEXT(A14,"[ss].00") |
3863091472.72 |
* The m or mm must appear immediately after the h or hh symbol, or Excel displays the month rather than the minute.
12-hour clock
AM/PM, am/pm, A/P, a/p - Displays the hour based on a 12-hour clock. Excel displays AM, am, A, or a for times from midnight until noon and PM, pm, P, or p for times from noon until midnight.
24-hour clock
If you leave off the AM/PM, Excel will display the time based on a 24-hour clock, like 17:30.
Combine Date & Time formats
| Formula | Result |
|---|---|
="Date: "&TEXT(NOW(),"mm/dd/yyyy") |
Date: 05/31/2022 |
="Date-time: " & TEXT(NOW(), "m/d/yyyy h:mm AM/PM") |
Date-time: 5/31/2022 9:32 AM |
Percentage formats
| Value | Description | Formula | Result |
|---|---|---|---|
| 0.254740088392962 | No decimals | =TEXT(A2,"0%") |
25% |
| 0.254740088392962 | 1 decimal | =TEXT(A3,"0.0%") |
25.5% |
| 0.254740088392962 | 2 decimals | =TEXT(A4,"0.00%") |
25.47% |
Fraction formats
| Value | Description | Formula | Result |
|---|---|---|---|
| 4.34 | Up to one digit (1/4) | =TEXT(A2,"# ?/?") |
4 1/3 |
| 0.34 | Up to one digit (1/4) ** | =TRIM(TEXT(A3,"# ?/?")) |
1/3 |
| 4.34 | Up to two digits (21/25) | =TEXT(A4,"# ??/??") |
4 17/50 |
| 4.34 | Up to three digits (312/943) | =TEXT(A5,"# ???/???") |
4 17/50 |
| 4.34 | As halves (1/2) | =TEXT(A6,"# ?/2") |
4 1/2 |
| 4.34 | As quarters (2/4) | =TEXT(A7,"# ?/4") |
4 1/4 |
| 4.34 | As sixteenths (8/16) | =TEXT(A8,"# ??/16") |
4 5/16 |
| 4.34 | As tenths (3/10) | =TEXT(A9,"# ?/10") |
4 3/10 |
| 4.34 | As hundreths (30/100) | =TEXT(A10,"# ??/100") |
4 34/100 |
- ** Note the second example uses the TRIM function to trim the leading space from decimal only values.
- After you apply a fraction format to a cell, decimal numbers as well as actual fractions that you type in that cell will be displayed as a fraction. For example, typing
.5or1/2results in1/2when the cell has been formatted with a fraction type of Up to one digit (1/4). - If no fraction format is applied to a cell, and you type a fraction such as
1/2, it will be formatted as a date. To display it as a fraction, apply a Fraction format, and then retype the fraction.
Scientific notation formats
| Value | Description | Formula | Result |
|---|---|---|---|
| 12,300,000 | Scientific - 7 places | =TEXT(A2,"0.00E+00") |
1.23E+07 |
| 12,300,000 | Scientific - 6 places | =TEXT(A3,"#0.0E+0") |
12.3E+6 |
E (E-, E+, e-, e+) - Displays a number in scientific (exponential) format. Excel displays a number to the right of the "E" or "e" that corresponds to the number of places that the decimal point was moved. For example, if the format_text argument is "0.00E+00", Excel displays the number 12,200,000 as 1.22E+07. If you change the format_text argument to "#0.0E+0", Excel displays 12.2E+6.
Special formats - Zip code, Zip +4, Phone number, Social Security number
| Value | Description | Formula | Result |
|---|---|---|---|
| 12345 | Zip Code | =TEXT(A2,"00000") |
12345 |
| 123456789 | Zip Code + 4 | =TEXT(A3,"00000-0000") |
12345-6789 |
| 1234567899 | Phone Number | =TEXT(A4,"[<=9999999]###-####;(###) ###-####") |
(123) 456-7899 |
| 123456789 | Social Security Number | =TEXT(A5,"000-00-0000") |
123-45-6789 |
| 1111222233330000 | 16-digit credit card number | =TEXT(A6,"####-####-####-####") |
1111-2222-3333-0000 |
Custom formats
| Value | Description | Formula | Result |
|---|---|---|---|
| 123456 | ID # & 9-Digit number | ="ID# "&TEXT(A2,"000000000") |
ID# 000123456 |
| 123456 | Latitude/Longitude | =TEXT(A3,"###° 00' 00''") |
12° 34' 56'' |
Using Symbols to create custom formats
| Symbol | Name |
|---|---|
| $ | Dollar sign |
| + | Plus sign |
| ( | Left parenthesis |
| : | Colon |
| ^ | Circumflex accent (caret) |
| ' | Apostrophe |
| { | Left curly bracket |
| < | Less-than sign |
| = | Equal sign |
| - | Minus sign |
| / | Slash mark |
| ) | Right parenthesis |
| ! | Exclamation point |
| & | Ampersand |
| ~ | Tilde |
| } | Right curly bracket |
| > | Greater-than sign |
| © | Copyright |
| ® | Registered |
| ™ | Trademark |
| Space character |
Symbols are displayed exactly as entered. For example, =TEXT(NOW(),"©yyyy") would display "©2022".
Restore leading 0's and convert back to numbers
| Original Value | Leading 0's removed | TEXT function | Convert back to Numbers |
|---|---|---|---|
| 00001 | 1 | =TEXT(A2,"00000") |
=--C2 |
| 00012 | 12 | =TEXT(A3,"00000") |
=--C3 |
| 00123 | 123 | =TEXT(A4,"00000") |
=--C4 |
| 01234 | 1234 | =TEXT(A5,"00000") |
=--C5 |
| 12345 | 12345 | =TEXT(A6,"00000") |
=--C6 |
The TEXT function converts numeric values to TEXT, so you can't perform mathematical operations on them. You can use the double-unary (--) operator to convert text values back to numbers, like:
=--C2Which will convert 00001 back to 1
If you need to use a TEXT converted number in a formula try to use it before using (--), like =C2+2, which will return 3. If Excel returns an error then you can use =--C2+2.
Use CHAR(10) to add a new line
| Formula | TEXT w/a Line Break |
|---|---|
="Today is: "&CHAR(10)&TEXT(TODAY(),"mm/dd/yy") |
Today is: 05/31/22 |
You can use CHAR(10) with the TEXT function to create a new line, but you need to format the cell to Wrap Text:
Format Cells (Ctrl + 1) > Alignment > Check the Wrap Text check box.