Text Function in Excel
Text function in excel is categorized under string or text function in excel and this is a very useful function which is used to change a given input to the text provided a specified number format, this formula is used when we large data sets from multiple users and the formats are different for each other.
Below is the TEXT Formula in excel:
value: that we want to format
format_text: format code that we want to apply
How to Use TEXT Function in Excel? (with Examples)
TEXT function in Excel is very simple and easy to use. Let understand the working of TEXT in excel by some examples.
For example, we have time and dates in column A and B and we want both the values to concatenate with space as separator.
So, we want to display both time and date together, in column C to get values together as
7:00:00 AM 6/19/2018, 7:15:00 AM 6/19/2018, and so on.
Now, when we try to concatenate both the values, what we get are the values shown below:
You can see the Excel displayed the values of date and time but this format is not clear and readable for the user because whenever we enter a date into a cell, Excel formats the date using the system short date format and when we concatenate both the Excel display the system value for both date and time.
To make it more clear, readable and in the desired format we will be using the TEXT function.
For the time we want to display it as hours:minutes:seconds AM/PM and 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 crtl+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 C2 we use the TEXT function in Excel, which takes two arguments the value and the format code that we want to apply to that value. So, the TEXT formula in Excel becomes
=TEXT(A2,”h:MM:SS AM/PM”)&” “&TEXT(B2, “m/d/yyyy”)
Dragging the text formula excel to other cells, we get the desired output in the format that we want.
Note: The format code has to be double quotes else the TEXT function in Excel will generate an error #NAME?
If we don’t surround the date format with double quotes it will result in an error shown below.
Excel Text function can also be used for the 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.
Suppose we have a list of customers with their mobile number in column A and B. The mobile numbers are given with their country code.
As you can see, below we converted these values in scientific notation format in excel
Now, we want it in the format such that the country code and the mobile number could be easily readable. We can make it readable using the Excel TEXT function.
A mobile number country code contains 12 digits, starting two signifies the country code and the remaining is the contact mobile number. So, the format code that we use here is “############”
So, TEXT formula in Excel becomes,
Now, if we want to make it more readable by separating the country code, then we will change the format code by simply placing a hyphen just after two hashes.
Text Function Format Code While Handing With Date
While dealing with dates there a list of format code given below
Concatenation with String
Suppose we have a list of children and their date of birth
Now, we want to display name and date of birth in column C, like John was born on 8 December 2015 and similar for other kids.
If we directly concatenate the name and the date of birth, we get value in a format like this
But we want it another format, so using the Excel TEXT function we will be able to get the desired output.
So, the format for the date specified, we will use the format code “d mmmm, yyyy”
If we want to use the abbreviation of the month instead of a full name we will change mmmm to ‘mmm’.
If we want to display day with a leading zero, if it is a single value day like (1-9) we will replace ‘d’ with ‘dd’.
So, depending upon the requirement and the format in which we want to display a value we can alter the format code. We can also customize the built-in format codes, depending upon the requirement
Suppose, we have Gross and expense amount and we want to display in cell A13, “The net profit is” &B11
When we apply the TEXT formula in Excel,
=”The net profit is “&B11 to B11, we get
The formula in A13 doesn’t display the formatted number which is in dollars. So, the revised TEXT formula in Excel that uses the TEXT function to apply to format to the value in A13:
=”The net profit is ” &TEXT(B11,”$#,##0.00″)
This TEXT formula in Excel displays the text along with a nicely formatted value: The net profit is $52,291.00
TEXT Function in Excel Video
This has been a guide to TEXT Function in Excel. Here we discuss the TEXT Formula in excel and how to use TEXT in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –