The TEXT function in excel is quite a lot used formula for converting a number or a numeric value in a specific text format. This function belongs to the text formula group and therefore the result of this function is a text string.
In this tutorial, we would go through how to use the TEXT excel function and what is its use. This blog contains lots of examples of TEXT formula in excel to make you understand about how to use the TEXT excel function.
Here we go 😎
Using TEXT Formula in Excel
The TEXT function is generally required and used in following scenarios:
- For converting date into a specific date format (like dd-mm-yyyy or dd mmmm, yyyy)
- For converting a number into specific format.
- To combine date/number with text to make sentence.
When To Use TEXT Function in Excel
The excel TEXT formula is a useful text function that is used to convert a number or date in a particular text format.
For example, it helps you to convert a date in DD-MM-YYYY format or any other such date format (like dd/mm/yyyy, etc.).
Syntax and Arguments
=TEXT(value, text_format)
Below points explains the arguments of the excel TEXT formula-
- value – In this argument, specify the numeric value or number that you wish to convert into specific format.
- text_format – In this argument, specify the format to apply on the value argument.
Examples of TEXT Formula in Excel
In this section of the blog, we would unlock how does TEXT function work in excel by having glance at some of the practical examples.
Ex. 1 # Convert date in DD/MM/YYYY format
Suppose you have the dates in excel in format DD-MM-YYYY format.
Now your task is to convert this date into some different format, lets say in DD/MM/YYYY format.
To achieve this, simply use the following formula in cell B2 and copy-paste it to cell B3.
=TEXT(A2,"DD/MM/YYYY")
So simple and quick !
Similarly, the date format “dd mmmm, yyyy” will convert and display the date 15-03-2021 as 15 March, 2021.
Below image shows more date formats that you can use in excel.
Learn everything about dates and date formats in excel.
It is important to note that the result of the TEXT function is a text formatted value. Therefore, the dates in column C are not an actual date format, but a text format (simply visible as a date).
Ex. 2 # Convert number into specific format
In addition to converting date format in excel, the TEXT function is also very useful to change the format of numbers.
Very wide usage of this function is to add a specific count of zeros (0) before a number with variable length in excel.
Your task is to to add zeros before number to make all the numbers of same length (lets say of eight characters).
To achieve this, simply use the following formula:
=TEXT(A2, "00000000")
As a result, excel adds leading zeros before the numbers to make it of equal length (eight characters long).
It is important to note that, the result in column C is not an actual number. It is simply a text formatted value.
Learn more about multiple ways for adding leading zeros before a number in excel here.
Ex. 3 # Combine Text and Date in Excel
Many a time, you may wish to concatenate some text string with date.
You may have used the CONCATENATE function to join text and date in excel. However, it may result in an incorrect date value, like shown in the below image:
In the above example, excel converted the date in DD-MM-YYYY in a number value. This is so because, excel stores and understands a date and time as a numeric value (numbers).
To show actual date instead of a date number, simply use the TEXT function to convert the date into text format, like below:
=CONCATENATE(A2," ",TEXT(B2,"dd-mm-yyyy"))
Similarly, you can use the TEXT function along with CONCATENATE function to combine numbers and text. See below example –
In the above example, I have used the following formula:
=CONCATENATE(A2," ",TEXT(B2,"$0.##"))
Learn more about number formatting in excel by Excelunlocked in this blog.
Bonus – Using TEXT Formula to Generate Mobile Numbers
Each and every country has its own mobile number coding rules and format. For example, in India the mobile numbers starts with +91 followed by 10 digit number. Similarly, in Canada and other North American states, the cell number starts with +1.
We can use TEXT function to display the mobile numbers in specific format.
Let’s say we have 10 digit mobile numbers as shown in the image below:
Suppose, we want to add the text (+91) before each of the mobile numbers and also give one space character after the 5th digit of the 10 digits mobile number.
To achieve this, simply use the following formula.
=TEXT(A2,"(+91) 00000 00000")
Similarly, people use this trick to format the area specific PIN and ZIP Codes.
Error #NAME? while using TEXT Formula in Excel
The TEXT function in excel will return the #NAME? error code when you have missed to put the text_format argument (the second argument) within double quotes.
With this, we have completed this blog on using TEXT excel formula.
Thank You 🙂