TEXT Function in Excel
Text function is used to convert the value into text. But it, in reality, Text function is used to convert any value in text format and we can even change the format of converted value as well. For this, we just have to select the cell which we want to convert and the format in which we want to convert. However, there are times when you need to convert numbers into text, and that is where the TEXT function comes into the picture.
TEXT Formula in Excel
The TEXT function is used to convert a numeric value into a text string in a specified format. The formula for the TEXT Function is as follows:
value (required)– the numeric value which needs to be converted to text. The value can be a number, date, reference to a cell containing a numeric value or any other function that returns a number or date.
format_text (required)– a format which you want to apply. It is supplied in the form of a format code enclosed in the quotation marks, e.g. “mm/dd/yy”.
The TEXT function is available in all versions of Microsoft Excel.
TEXT Function Format Codes
As we saw earlier, the formula of Text function is very easy however there is a tricky part to it which is supplying a format code which will output your number a format of your choice. Typically, the Text function accepts most of the format codes used in Excel number formats. Below is a table containing the most common and frequently used formats.
In addition to this, you can include any of the below characters in the format code, and they will be displayed exactly as entered.
4.8 (4,485 ratings)
View Course
How to Use the TEXT Function in Excel?
This TEXT function is very simple easy to use. Let us now see how to use TEXT Function with the help of some examples.
- For displaying numbers in a format that makes more sense for users or in a more readable way.
- To display dates in a defined format.
- To concatenate numbers or dates with certain characters or texts.
Example #1
For an example, if you want to fetch the date from cell A2 and show it into another cell in a standard date format like “February 1, 2019”, you use the Text formula as shown below:
=TEXT(A2, “mmmm d, yyyy”)
The result will look like as shown below:
Example #2
For example, we have a table with time and dates in columns B and C and we want both the values to concatenate with space as a separator in a single cell.
Here, we want the Time and Date column values to display into column C together as shown below.
9:00:00 PM 10/18/2019, 10:00:00 AM 10/18/2019……..
While trying to concatenate both the values, we get values as shown below-
We observe that EXCEL displays the time and date in a format which is not readable to a user and this is because when we enter a data in a cell, the excel formats the date using the system short date and when we try to concatenate the columns the Excel displays in a format which is not readable.
To make it easier and more clear, readable and in the proper format, we use the TEXT function.
For a time, we want the format to show up as hours:minutes: seconds AM/PM and for a date as Month/Date/Year
Excel provides a list of custom format and the format that we want, we can check, opening format cells window. Press CTRL+1 in windows and +1 in Mac to open up the Format Cells window, in Number tab go to Custom.
Scroll down and check for the formats required.
Under Type: copy the format for date(m/d/yyyy) and time(h:mm:ss AM/PM)
Now, in D4 we use the TEXT function, which takes two arguments the value and the format code that we want to apply to that value. So, the TEXT formula becomes
=TEXT(B4,”h:MM:SS AM/PM”)&” “&TEXT(C4,”m/d/yyyy”)
Dragging the Text formula to other cells, we get the desired output in the format that we want.
Example #3
Text function can also be used for numbers with large values for example telephone numbers. When a numeric value in excel exceeds value 99999999999, then excel always represents that value in scientific notation.
Example #4
Suppose we have mobile numbers of a list of customers in column B and C. The mobile numbers are with their country code.
As you can see below, we observe that Excel converts these mobile numbers into scientific notation format.
Now, as the mobile numbers are in scientific notation format we want these numbers to show up in a format where the country code and the mobile number can be easily readable. We do this with the use of TEXT function.
Any mobile number with its country code contains 12 digits, starting two digits implies country code and the rest 10 digits are the mobile number. The format we use here is “############”
TEXT formula is as shown below-
=TEXT(C4,”############”)
We can also make the mobile number more readable by putting “-” after two hashes, by doing this the country code and the mobile number will be separated by a hyphen.
Below is the list of format code used in Excel while dealing with Dates-
Example #5
Combining Date and Text in the desired format.
While using TODAY() or NOW() function for getting the current date, we have a feature to display it in combination with some of the text so that we can relate the date with some occurrence or happen as a reference.
However, there is a catch, if you try to concatenate date and text in the normal way:
=CONCATENATE(“Today is “, TODAY())
Excel will return a very strange result, like “Today is 101819”.
The reason behind this is that in the internal Excel system, dates are loaded as numbers, and that number appears in a sequence of a text string. In order to fix this, we use the TEXT function to display the date in the correct format. For example, inserting the TODAY function in the Text formula with the format code “dddd d mmm, yyyy” will return a string similar to this: “Today is Thursday 18 Oct 2019”.
Formula for same is as shown below:
=CONCATENATE(“Today is “, TEXT(TODAY(), “dddd d mmm, yyyy”))
or
=”Today is ” & TEXT(TODAY(), “dddd d mmm, yyyy”)
Below are the lists of all the built-in Excel Text Functions.
The built-in functions have been categorized, to make it easy for you to find the function you require. Each of the function listed below provides a full explanation of the function, with the use of examples and common errors.
Note: Some of the Text Functions were not available in earlier Excel versions and are newly added to Excel 2013 or Excel 2016.
Things to Remember
- For converting numbers to text like 123 to One Two Three, unfortunately, that feature is not available with Text function. For doing so, you will have to use Visual Basics (VBA).
- The Text function converts a numeric value to some formatted text hence its result cannot be used for calculation purpose.
- ‘format_text’ argument in text function formula cannot contain asterisk character (*).
Recommended Articles
This has been a guide to TEXT function. Here we discuss the TEXT Formula and how to use TEXT along with practical examples and downloadable excel templates. You can also go through our other suggested articles –
