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.

  1. A float number is stored in A2 cell. Write the following TEXT() formula to change it into percentage.
    =TEXT(A2, “0.0%”)
    Excel text function
  2. It will convert the number value into percentile. See the result here.
    Excel text function
    But the number type is also changed to text, and it is not a number anymore.
  3. See one more number conversion using the following TEXT formula –
    =TEXT(A5, “$#,##0.00”)
    Excel text function
  4. It will separator the number by thousand comma separator and round two decimal numbers from the right.
    Excel text function

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.

  1. Enter the following TEXT formula in an adjacent cell.
    =TEXT(A2, “YYYY/MM/DD”)
    Excel text function
  2. Hit the Enter key and see the formatted date result.
    Excel text function

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.

  1. 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”)
    Excel text function
  2. Hit the Enter key and get the formatted date result.
    Excel text function
    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()

Excel text function

The result will be something like without date formatting:

Excel text function

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.

  1. Write the following TEXT() formula in a cell by providing parameters like this:
    =”Today’s date is: ” & TEXT(TODAY(), “DD/MM/YYYY”)
    Excel text function
  2. See the combination of text and date along with formatting by pressing the Enter key.
    Excel text function
    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.


Previous articleExcel Rules Manager
Next articleHow to add or remove Hyperlink in Excel