Skip to main content

TEXT function

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:

  1. Select the cells
  2. Press Ctrl+1 to launch the Format Cells dialog
  3. Select any of the build-in formatting code
  4. Click the Custom category
  5. Copy the format that's displayed in the Type box into your TEXT formula

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 .5 or 1/2 results in 1/2 when 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:

=--C2

Which 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.

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>