Text Function in Excel
Text function in excel is a string function used to change a given input to the text provided a specified number format. It is used when we large data sets from multiple users and the formats are different for each other.
Below is the TEXT function 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 us 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 a 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 formats 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 the 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 function in Excel becomes
=TEXT(A2,”h:MM:SS AM/PM”)&” “&TEXT(B2, “m/d/yyyy”)
Dragging the formula 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 numbers in columns A and B. The mobile numbers are given with their country code.
As you can see below,w we converted these values in scientific notation format in excelScientific Notation Format In ExcelIn Excel, scientific notation is a specific style of writing numbers in scientific and exponential forms. Scientific notation compactly helps display values, allowing us to compare and use the same in calculations..
Now, we want it in a 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 function 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
|Name of Kid||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 in 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 a day with a leading zeroLeading ZeroLeading zeros are zeros that are added to figures without altering their mathematical values. This is done to keep a specific format in a sheet or to prevent errors. We may add Leading Zeros in Excel by utilizing the Right, TEXT, and Concatenate Functions., 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 formula that uses the TEXT function to apply to format to the value in A13:
=”The net profit is ” &TEXT(B11,”$#,##0.00″)
This formula 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 how to use TEXT Function in Excel along with step by step example. You may also look at these useful functions in excel –