Excel text function
Although Excel is mostly all about numeric data, but it also contains text and string data. To manage and perform operations with text data, we require text functions to simplify things.
Excel offers various text functions, such as Len(), Replace(), left(), right(), concatenate(), text(), trim(), and many more. These functions are available Text dropdown list inside the Formula tab. They help to manipulate the text data in Excel.
Besides these text functions, a function named TEXT() is also offered by Excel that is used for number to text conversion. In this chapter, we will discuss the TEXT() function of Excel rather than all the text functions.
TEXT() function
Excel TEXT() function is a function that allows the users to convert the number to text in Excel spreadsheets. It is an Excel built-in function. You can use this function within the Excel spreadsheet for the number to text conversion.
“Data converted using TEXT() function cannot be used for calculation as they changed into the text format. So, if you want to use them in calculation, also keep the original data in another cell.”
This function is categorized into the text/string functions category. With the help of this function, a numeric value can be converted into a text string.
Syntax
The TEXT() function accepts two parameters: value and formating_text. Following is the formula of TEXT() function –
Parameters
Value – It takes the value to which you would like to format.
Formating_text – It is a format in which way you want to format the value. Or you can understand it as – it contains the formatting code that you want to apply on value.
Return Value
The TEXT() function returns the values after converting to a specific text format.
Why this function is required?
The TEXT() function is used in the following circumstances:
- When you want to display a date in a specific format, like DD/MM/YYYY, MM/DD/YYYY or d mmmm, yyyy format.
- When you like to show the numeric values in different ways. For example, 10000 as 10,000 or change into percentage.
- When you want to display the combination of numbers and characters, i.e., alphanumeric string. For example, combine text string with numbers to form a sentence.
Basic Example: Format numbers
We have taken a simple example to format the numbers using the TEXT() function.
- A float number is stored in A2 cell. Write the following TEXT() formula to change it into percentage.
=TEXT(A2, “0.0%”)
- It will convert the number value into percentile. See the result here.
But the number type is also changed to text, and it is not a number anymore. - See one more number conversion using the following TEXT formula –
=TEXT(A5, “$#,##0.00”)
- It will separator the number by thousand comma separator and round two decimal numbers from the right.
Note: When the TEXT() function applies to any type of value, it converts to the text format. Thus, you cannot use these values further for numeric calculation.
Similarly, you can do more number formatting using this function.
Formatting codes
Excel offers some formatting codes to be used with the TEXT() function. These codes are used while formatting numbers into text.
| Code | Description | Example |
|---|---|---|
| # (hash) | It hides the extra zero/numbers from numeric values. | It helps to display such numbers like #.# to display a single decimal point. For example, 5.374 as 5.3 |
| 0 (zero) | It displays irrelevant zero from numbers if you add. | #.00 – It would display two zeros after the decimal point in a numeric value. For example, A number 2.4 will be 2.40 |
| , (comma) | It is used as a thousand separator in numbers. | For example, ##,### it will put a thousand separator. A number 23548 will be displayed as 23,548. |
You can use them within the TEXT() function to format your Excel worksheet data however you want.
Basic Example: Format date
This is a simple example to format a date in a specific format using the TEXT() function. We will show you to format the date into YYYY/MM/DD text format in this example. We have a date stored in an A2 cell.
- Enter the following TEXT formula in an adjacent cell.
=TEXT(A2, “YYYY/MM/DD”)
- Hit the Enter key and see the formatted date result.
Example 2: date in date, month name, year format
In this example, we will format the date in date, month, year format. But the month will be the month name instead of month number. It is a special date format. We have to provide formatting_text value d mmmm, yyyy.
- We have a date stored in A2 cell. Write the following TEXT() formula in a cell and provide the parameter values.
=TEXT(A2, “d mmmm, yyyy”)
- Hit the Enter key and get the formatted date result.
In the same way, you can also use any other date format you want.
These are the examples to format the dates in a special format using the TEXT() function. Now, we will combine the text with a date.
Example: Combining text with date
As we told you that the TEXT() function allows to combine the numbers with text/string. Similarly, we can also combine text/ string with a date. In this example, we will format the date with a text string with the help of TEXT() function. We will do with today’s date.
If you try to do it by simply combining the string using the formula like this –
=”Today’s date is: ” & TODAY()
The result will be something like without date formatting:
Without apply any formatting to the date, the returned resultant date is unreadable. Although it has been combined with the text string. Hence, add the formatting with date using the TEXT() function.
Add the formatting with date
Firstly, we will get today’s date using the TODAY() function that will be nested inside the TEXT() function with a date format. That date will be then concatenated with text string using & operator. Now, see how it will take place to get today’s formatted date with text data.
- Write the following TEXT() formula in a cell by providing parameters like this:
=”Today’s date is: ” & TEXT(TODAY(), “DD/MM/YYYY”)
- See the combination of text and date along with formatting by pressing the Enter key.
In this way, you can combine the text with formatted numbers.
Popular Examples
The TEXT() functions can be used alone and with other functions. This means it can also be used with other functions by nesting them inside it.
Here, we have some popular examples with their description to see that how the TEXT() function works with different data.
| Function | Description |
|---|---|
| =TEXT(1394.567,”$#,#.00″) | Currency with a thousand separator and two decimals. |
| =TEXT(TODAY(),”MM/DD/YYYY”) | Today’s date in month, date, and year format. E.g., 07/26/2021 |
| =TEXT(TODAY(),”DDDD”) | Day of todays date. E.g., Monday |
| =TEXT(“12/05/2019”, “d mmmm, yyyy”) | It will return the date with month name. For example, 12 May, 2019 |
| =TEXT(NOW(),”H:MM AM/PM”) | It will return the current time of your computer system. For example, 2:49 PM. |
In the same way, TEXT() function helps to achieve different results. You can use it anywhere you need and do more tasks as you want.
#NAME? Error
You may get this #NAME? Error while using TEXT() function. This error usually occurs when you skip the quotation mark (” “) around the formatting code in second parameter value.
For example,
You have provided a date format without quotation marks like this dd-mm-yyyy. It is incorrect that is why the TEXT() function will return #NAME? Error.
Let’s understand with a complete example –
If you write a TEXT() formula =TEXT(A2, dd-mm-yyyy) to format a date, it would give #NAME? Error because the formula is incorrect. You need to write the formula this way: =TEXT(A2, “dd-mm-yyyy”)
Remember that the formatting text always be passed inside double-quotes.
