The TEXT function is categorized as a String/Text Function, which converts a number value into a text string in a user-specified format. For example, if you want to convert a date in ’15/03/2020′ format into March 15, 2020 format, you use the TEXT function to do that.
This guide explains how to use the TEXT function in Excel with the help of some formulas and examples.
Syntax
The general syntax of the TEXT function:
=TEXT(value,format_text)The TEXT function requires two arguments/parameters:
value– The number value that you want to convert into a text string. This value could be a numeric value, a date, or a cell reference of a number value.format_text– The format code that you want to apply to the specific value. It should always be enclosed in double quotation marks.
TEXT Function Format Codes
The Text function has only two arguments. The first argument requires only the value you want to convert, it’s easy. But you should insert the correct format code that will give you the output number in the format you want. The following table contains the most common and often used formats.
| Format Code | Description | Example |
|---|---|---|
0 | Zero is a digit placeholder that shows only digits without any decimal places. | #.0 – always displays 1 decimal places. If you type 5.50 in the referenced cell, it will display as 5.5. |
# | Displays digit without any extra zeros. | #.## – shows up to two decimal places. when you enter 3.777, it returns 3.78. |
? | Displays only digit with no decimal places. It is usually used for aligning numeric values in a column at a decimal place. | #.? – it will display one decimal place and will align the decimal point. |
. | Decimal point | |
, | Thousands separator. | ###,### – It will display a thousands separator. If you type 195200, it returns 195,200 |
0% | Displays numbers as a percentage. | If you type 0.285, it returns 28.5 |
In addition to the above format code, you can also add any of the following symbols in the format code of the formula, and they will be shown exactly as entered.
| Symbol | Description |
|---|---|
+ and - | Plus and minus signs |
( ) | Left and right parenthesis |
: | Colon |
^ | Caret |
' | Apostrophe |
{ } | Curly brackets |
< > | Less-than and greater than signs |
= | Equal sign |
/ | Forward slash |
! | Exclamation point |
& | Ampersand |
~ | Tilde |
| Space character |
Text Function Format Codes for Dates and Time
When you want to convert dates and times using the TEXT function, use any of the below format codes.
| Format Code | Description and Examples |
|---|---|
d | Specifies Day of the month in one or two-digit number without a leading zero (e.g. 2 to 25) |
dd | Specifies Day of the month in a two-digit representation with a leading zero (e.g. 02 to 25) |
ddd | Specifies Day of the week in three-letter abbreviation (e.g. Mon to Sun) |
dddd | Specifies full name of the Day of the week. (e.g. Monday, Wednesday) |
m | Specifies the Month of the Year, in one or two-digit number without a leading zero (e.g. 02 to 12) |
mm | Specifies Month in a two-digit representation with a leading zero. (e.g. 01, 12) |
mmm | Specifies Month in three-letter abbreviation (e.g. Jan, Nov) |
mmmm | Specifies full name of the Month. (e.g. January, November) |
yy | Specifies the Year in a two-digit number (e.g. 08 meaning 2008, 19 meaning 2019) |
yyyy | Specifies the Year in a four-digit number (e.g. 2008, 2019) |
h | Specifies the hour in one or two-digit representation without a leading zero (e.g. 6, 12) |
hh | Specifies the hour in two-digit representation with a leading zero (06 to 12) |
m | Specifies Minutes in one or two-digit number without a leading zero (e.g. 5, 45) |
mm | Specifies Minutes in one or two-digit number a leading zero (e.g. 05, 45) |
s | Specifies Seconds in one or two-digit number without a leading zero (e.g. 5, 45) |
ss | Specifies Seconds in one or two-digit number a leading zero (e.g. 05, 45) |
AM / PM | Specifies that time should be displayed as a 12-hour clock, followed by “AM” or “PM” |
How to Use TEXT Function in Excel
You learned the syntax and format codes of the TEXT function, now, let us explore how to use the function in Excel with the help of few examples.
Use text formula to display the whole number to the number in cell A1.
To do this we can use the text formula as:
=TEXT(A1,"0")
Use this formula to display single decimal place:
=TEXT(A1,"0.0")
The below table shows the Text formulas with different format codes to apply different formatting types to different numeric values. You can copy these formulas directly into your spreadsheet to try with on your own.
| Value | Formula | Formatted Value |
|---|---|---|
| 4963.34 | =TEXT(A2,"0.000") | 4963.340 |
| 5300.52 | =TEXT(A3,"#,##0") | 5,301 |
| 5.12 | =TEXT(A4,"# ?/?") | 5 1/8 |
| 0.4963 | =TEXT(A5,"#%") 50% | 50% |
| 9600.60 | =TEXT(A6,"$#,##0.0") | $9,600.6 |
| 20 | =TEXT(A7,"~#!") ~20! | ~20! |
| 5656 | =TEXT(A8,"00000000") | 00005656 |
The above formulas are applied in column C of a spreadsheet as shown in the below screenshot:
TEXT Function with Formulas
You can also use other formulas and functions both within and outside of the TEXT function.
Let us assume you have Gross and Expense amount, and you want to calculate the net profit and display the profit in cell A9 with the string “Your Net Profit is”. You can use the formula below for that:
="Your Net Profit is "&TEXT(C6-C7,"$#,###.00")The formula first calculates the profit by a formula (C6-C7) within the TEXT function and it joins the formatted value with the string “Your Net Profit is” using concatenate formula (&), and it finally displays the result in cell A9.
Format A Mobile Number Using the TEXT Function
Usually, when you type any number that is longer than 11 digits in the spreadsheet, for example, mobile numbers, Excel will automatically turn it to scientific notation. And you can use these scientific notations properly, so you may want to convert them to normal numeric values. You can use the TEXT function to convert those annoying scientific notations into mobile numbers.
As shown in the below screenshot, when you enter mobile numbers (12-digit long including their country code) in Excel, it automatically converts these mobile numbers into scientific notation format.
With the TEXT function, you can format this scientific notation format into readable mobile numbers.
Usually, a mobile number is 12 digits long (this may vary for some countries). The first two digits are country code and the remaining 10 digits are the mobile numbers.
So to convert those above scientific notations into mobile numbers, use this formula:
=TEXT(A1,"+############")We are using ‘############’ as the format code for this example to convert the notation to mobile number:
Now, let’s make it more readable by separating the country code from the mobile number. To do that, put a hyphen (-) after two hashes.
=TEXT(A1,"+##-##########")
Format Date using TEXT Function
By default, Excel stores Date as serial numbers. The serial number for January 1, 1900, is 1, and January 1, 2001, is 36892 because it is 36891 days since January 1, 1900.
Since most functions automatically convert date values to serial numbers, it’s tricky to display them in a readable format. But with the Excel TEXT function can easily convert them to text values and display them in your desired format.
For example, if you want to take the date from cell A1 (05-03-2015) and show it in a standard date format like ‘Mar 5, 2015’ in cell B1, you use the following formula:
=TEXT(A1,"mmm d,yyyy")mmmspecifies to 3 letters abbreviated monthdspecifies the day of the month in one or two digitsyyyyspecifies to display year four-digit numbers.
The screenshot below shows how you can apply different formatting types to the same date using the Text formula:
Concatenation Date and Text
Let’s say we want to join name (column A) and date of birth (column B) and display it in column C. You’ll get result like this:
If we directly concatenate text in cell A1 and date in cell B1, Excel joins the text and serial number for the date, not the actual date.
In order to concatenate text and date and properly display the Date in the desired format, use the TEXT function with the CONCAT function.
The Formula:
=CONCAT(A2,"-",TEXT(B2,"dd/m/yy"))The result:
Now, we’ll join a text string ‘was born on’ to the output and format the date in different format.
Then, the formula is copied to cell A2:A5 using the fill handle.
In another example, we are using the TODAY() function to get the current date, and join the date with some related text.
Now, you can easily convert any value (e.g., numbers, dates, etc.) into text in your desired format.
Member discussion