Text formula in excel is a text function which is used to convert a given number as input into a relevant text based on the number provided, this function is used to make the data in a range with the same format to each other, this is an inbuilt formula and takes a single input as an argument.
Text formula in excel (Table of Contents)
Excel TEXT Formula
TEXT Formula in excel is a function that converts a number to text in a number format. TEXT Formula in excel returns a number as text in the given format. The requirement to convert the number into text can be due to the following reasons:
- We may not want the numbers in the cells getting involved in calculating.
- We may want to display leading zeros in numbers in cells.
- We may want to embed the numeric output of formula or function and present it in a particular format inside other text. For example, “Sales last quarter increased by over $68,500”, where the number 68500 has been formatted with a currency symbol and thousands separator.
To use the Excel TEXT function, the formula is as follows:
As we can see above in the picture, the TEXT formula in Excel takes two mandatory arguments.
- Value: Value is the number or reference to the cell containing the value or number, which we want to convert.
- Format_text: For this argument, we need to specify the format in which we want the value to be converted.
The characters, which we can use in format code, are:
There are many formats, which we can use. Some of them are as follows:
How to Use TEXT Formula in Excel (with Examples)
Let us understand this TEXT formula in excel with some examples.
Suppose, we have the following format for entering the price of the item, quantity, and discount %. We want to generate a statement where we get the calculated final price but in currency format.
The following steps are here.
Step 1– As we want to write a statement too. That is why we will be using ‘CONCATENATE’ function for concatenating statement with the calculated value.
Step 2– To enter the function in the cell, we need to use an equal (=) sign.
Step 3– As ‘CONCATENATE‘ function takes texts as an argument, we will provide text1 as a static statement in double quotes.
Step 4– For text2, we will use the asterisk sign (*) (multiplication operator) to multiply the values.
Step 5- As we press enter after closing the parenthesis, we get the statement as below.
Step 6- However, we want to present the amount in currency format that is why we will use Excel TEXT function wrapping calculated value.
Step 7- After pressing the Enter button, the result would be like this:
Sometimes, while using various reports in an organization like sales report, manufacturing report, we select a date range to see the report for those particular days. We want the heading to be changed automatically whenever the user selects a different date than before.
In a normal way, the report would look like as follows:
However, we want heading to be dynamic. To do the same, the steps are:
Step 1- We need to use CONCATENATE For the text1, we have used the static statement “Sales Report for (“.
Step 2 -Now we need to give reference to B1 and D1 cell for the heading.
Step 3 – As we press the Enter button after closing the parenthesis, the result is as follows:
Step 4- As we can, the dates are being shown as numbers, but we want to display them as dates. To do the same, we need to use the TEXT
The format would be “dd-mmm-yyyy”.
Now the result is as follows:
There are situations when we want to extract only month name from date. To do the same, we can use the Excel text function as follows:
Now we apply formula text(reference of the cell containing the date,” mmmm”)
Now the result is as follows:
Drag the formula to other cells
Suppose, we have item codes of 5 digits. We want to get the Sr. No. if we just enter partial item code.
Step 1- First we will create a format as follows:
Step 2 – As we enter the partial number, we want to get Sr. No. We will use the MATCH function to get the Serial Number.
Step 3 – To give the lookup_value to the MATCH function, we need to use wildcard character asterisk (*) which represents zero or more characters in a string of characters. The reason to use asterisk is that we want to find out the partial match.
Step 4 – For lookup_array, we need to give the reference of the list but as we have used asterisk character while providing lookup_value to the MATCH function, which converts the value into a text, we need to convert lookup_array also to text values. To do the same, we will use TEXT
Step 6- For match_type, we will use 0. As we want to convert all values into text, we need to convert this formula into ARRAY Formula that is why we will press Ctrl+Shift+Enter while submitting the function.
Step 7 – Now we have entered 835 as the partial number and consequently formula has returned 7 as the serial number for the item code.
Suppose we have the following details about start time and end time for performed tasks, we want to find out the time taken. To do the same, we can use Excel TEXT formula.
To find out the duration in days, hours and minutes, we can use a combination of INT and excel TEXT function.
Steps 1- To find out the days, we have used INT function
Step 2 – To find out the hours and minutes, we will use Excel TEXT function with ampersand operator (&) in the following way:
The result is as follows:
Drag the formula to other cells.
Things to Remember about Excel TEXT Formula
- A format_text argument must appear in double quotation marks.
- To get the Rupee symbol, we can use Alt+Ctrl+4 key combination.TEXT Function in Excel
This has been a guide to Excel TEXT Formula. Here we discuss how to use TEXT Formula in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –