Excel Text Formula (Table of Contents)
Introduction to Text Formula in Excel
While working with Excel, we handle various kind of data types like Integers, Float, Boolean, Text, etc. Out of them, Text is one of the data types which we use widely. In this, we will cover the definition of text function and different ways to use the TEXT() function with examples.
The function that converts the numbers or values into text format as specified by the user in the format_text and returns the text is called Text() Formula.
Syntax of TEXT() Formula
Below is arguments of the Text Formula:
- Value: The data in a non-text format that we want to convert into text format.
- Format_Text: The format in which the user expects the text should be Format text which should be in double-quotes.
How to Use Text Formula in Excel?
Below are the different examples to use text formula in excel.
Example #1
Input a number in excel and see how it looks; normally, it will be considered in a general format.
We can convert that to a number by selecting the number format from the drop-down, then it will convert to a number format with zeros after the decimal point as below.
In case if we want to add zeros at the beginning of the number like below.
4.8 (8,029 ratings)
View Course
Now the movement, we move the cursor to the next cell; Excel will automatically clear the leading zeros. Then what is the solution if we want to add the leading zeros? The answer is TEXT() Formula.
For adding leading zeros, we need to give the format code as 000. The number of zeros depends on the requirement.
Observe the below screenshot.
When we gave three zeros for a two-digit number, one leading zero is added. When we gave four zeros, it created two leading zeros.
Example #2
Using the Text() formula, we can convert the date format into text format also. Date format is not similar in all the countries; few countries use date first, and few countries will use month first. In those cases, we may find it difficult to understand the correct date.
Observe the above date 2/3/2019. Here it may be March second or February third.
If we apply the TEXT() formula to this, we can find the exact date.
Observe the above screenshot. Text format is “DD-MMM-YYYY”, and the result is 03-Feb-2019. First, we assume that the date is 02 – Mar -2019, but after applying the formula, we came to know that it is 03 – Feb – 2019.
DD represents a day with a number without leading zero; again, this DD can be used in different ways.
DD – represents the date in number format; if the date is a single number, it will come with leading zero.
DDD – represents the date in string formats like MON or TUE, etc.
DDDD – represents the full form of the day like MONDAY or FRIDAY, etc.
M – represents the month with a number without leading zero.
MM – represents the month with a leading zero.
MMM – represents the month in string format with the first three letters of the month.
MMMM – represents the full name of the month.
Similarly, for a year also.
We can set the format for a time and also with the below format text codes.
H – represents hours without leading zero.
HH – represents hours with leading zero and similarly, for minutes and seconds. Below is the example screenshot.
Example #3
When we want to display the percentage or currency or numbers after decimal points, TEXT () Formula can be achieved.
We will see a few examples for that text format codes.
When we want two zeros after a decimal point, use “0.00” in the text format.
If we want the number to represent in dollars, give “$###” in the text format.
If we want the percentage, give “#%” in text format.
If we want the number separated by commas, then give “###,###” in text format.
Below are the Different Text Format Codes
0 – Represents the insignificant zeros. In the above example, if the number is 500.5, it will give the result as 500.50 in the first line formula.
# – Represents to display numbers. When we give “#.##” for 500.5, it will give a result of 500.5 as it does not display insignificant zeros.
– Represents for separation of the numbers. If we want to separate the numbers by comma, we can use the combination of #.
. – Represents a decimal point.
Special Characters
If we want to add any special characters, we can add them in between the double quotes; it will display in the same way how we insert. Find the below screenshot for reference.
Concatenation of Texts
We can concatenate texts with numbers or can perform some calculation, and the results can be displayed in the required format.
Consider a small calculation like in a class 50 students are available, and there are 4 classes and fee for each student is 1000 rupees.
We can now perform the calculation in the text function itself and display the results in the desired format.
If we want the result in Total cell as “Total fee for all children is ###,###”, we can achieve this using a combination of text and concatenation function.
Here “Total fee for all children is “ a string and the number results from calculation.
So the result will be as given below.
There is a function to find out whether the data in a cell is text or not. So, we can check whether we converted to text or not.
Observe the above screenshot; we checked the data we converted using text formula completely in text format. The result from the formula is True, which means the data in cell I42 is in text format.
Things to Remember About Text Formula in Excel
- TEXT() is a very simple and understandable function to use.
- Do not forget to keep the text format in double-quotes. If we missed keeping in double-quotes, the formula would give the error result as #value.
- Before convert to text using Text() Formula, we can check whether data is in text format or not by using IS TEXT() Formula.
- Use & symbol in between string and text Formula if you want to concatenate string and number operations.
Recommended Articles
This has been a guide to Text Formula in Excel. Here we discussed How to use Text Formula in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
