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 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 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 general format.
We can convert that to number by selecting the number format from the drop-down, then it will convert to 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.9 (2,433 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 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 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 the 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 can be achieved using TEXT() Formula.
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 result as 500.50 in first line formula.
# – Represents to display numbers. When we give “#.##” for 500.5 it will give result as 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 with the combination of #.
. – Represents for a decimal point.
Special Characters
If we want to add any special characters, we can add it 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.
Now we can perform the calculation in the text function itself and can 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 which we converted using text formula completely in text format or not. 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 to keep in double quotes, the formula will 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 downloadable excel template. You can also go through our other suggested articles –
