The Excel TEXT feature is used to transform a variety of a table into textual content. Basically, this feature converts various to a text string. TEXT is available in all versions of Excel.

=Text(Value, format_text)Formula

Where:

Value is a numeric result that we will convert to text

Text_format is the function we want to apply.

When is the Excel TEXT Function required?

We use the TEXT function in the following cases:

1.   When we want to display dates in a specified format;

2.   When we wish to display numbers in a specified format or in a more legible way;

3.   When we wish to combine numbers with text or characters.

Using Excel Text Function

With the following data, you need to convert the data to “d mmmm, yyyy” format. When we insert the text function, the result is as follows:

Using Excel TEXT with other functions

We use the old and discounted prices shown in cells A5 and B5. Quantity is given in C5. We want to display text with calculations.

1.   The final price is $xxx

2.   Where xxx would be the price in $ terms.

3.   For this, we can use the formula:

 =”The final price is “&TEXT(A5*B5*C5, “$###,###.00”)

How to combine the given text with the data using the TEXT function

1.   When we use the date formula, we would get the result below:

2.   Now, if we try to combine today’s date using CONCATENATE, Excel would give a weird result as shown below:

3.   What happened here was that dates that are stored as numbers by Excel were returned as numbers when the CONCATENATE function is used.To fix it we need to use the TEXT function in Excel. The formula to use would be:

Function Text to add zeros before variable length numbers

We all know that all zeros added before numbers will be automatically added by Excel. removal action. However, if we need to keep those zeros, then the TEXT function will be very useful. Let's see an example to understand how to use this function.

1.   We are given a 9-digit product code, but Excel removed the zeros before it. We can use TEXT as shown below and convert the product code to a 9-digit number:

In the above formula, we are given the identifier form that contains 9-digit zeros, where the number of zeros is equal to the number of digits we want to display.

Converting telephone numbers to a specific format

If someone wanted to do the same thing for phone numbers, this would involve using dashes and parentheses in the format code.

1.   Here, you want to ensure the country code comes in brackets (). 

2. Therefore, the formula used is ( ##) #### #####.. The # is the number of digits we wish to use.

Did you learn about how to use excel to text function in excel? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets, and PowerPoint Slides. You can also download WPS Office to edit word documents, excel, and PowerPoint for free of cost. Download now! And get an enjoyable and easy working experience.