Updated August 21, 2023
Excel Text Formula (Table of Contents)
Introduction to Text Formula in Excel
While working with Excel, we handle various 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 the text function and different ways to use the TEXT() function with examples.
The function that converts the numbers or values into text format 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 of using text formulas 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.
If we want to add zeros at the beginning of the number like below.
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 000. The number of zeros depends on the requirement.
Observe the below screenshot.
One leading zero is added when we give three zeros for a two-digit number. When we gave four zeros, it created two leading zeros.
Example #2
The date format is not similar in all the countries; a few countries use date first, and a few countries will use month first. We may find it difficult to understand the correct date in those cases. Using the Text() formula, we can also convert the date format into text format.
Observe the above date, 2/3/2019. Here it may be March second or February third.
We can find the exact date if we apply the TEXT() formula to this.
Observe the above screenshot. The text format is “DD-MMM-YYYY”, and the result is 03-Feb-2019. First, we assume the date is 02 – Mar -2019, but after applying the formula, we learned it is 03 – Feb – 2019.
DD represents a day with a number without leading to zero; again, this DD can be used differently.
DD – represents the date in number format; if it is a single number, it will come with a leading zero.
DDD represents the date in string formats like MON or TUE.
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 month’s first three letters.
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, the TEXT () Formula can be achieved.
We will see a few examples of 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 wish to change 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. Giving “#.##” for 500.5 will result in 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 them. Find the below screenshot for reference.
Concatenation of Texts
We can concatenate texts with numbers or perform some calculations, 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 the fee for each student is 1000 rupees.
We can now calculate in the text function 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 the calculation.
So the result will be as given below.
There is a function to determine whether the data in a cell is text. So we can check whether we converted to text or not.
Observe the above screenshot; we checked the data we converted using the 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 a #value.
- Before converting to text using the Text() Formula, we can check whether the data is in text format using IS TEXT() Formula.
- Use & symbol between string and text Formula to concatenate string and number operations.
Recommended Articles
This has been a guide to Text Formulas in Excel. Here we discussed How to use Text Formula in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
