How to use TEXT function in Excel

Within an Excel spreadsheet, text may be created from numbers using the TEXT Function. The function essentially changes a real number into a string of text. Each edition of Excel has TEXT.

Formula:

=Text(Value, format_text)

Where,

The numerical number that has to be converted to text is called value.

The formatting we wish to use is format text.

When should you use the Excel TEXT Function?

In these situations, we employ the TEXT function:

If we want to show dates in a particular way

Sometimes we want numbers to be presented in a certain style or in a more readable way.

When we want to mix text or characters with numbers.

Simple Excel Text Function:

I need to change the following data into d mmmm, yyyy format. The outcome of inserting the text function would be as follows:

Including additional operations in Excel TEXT:

In cells A5 and B5, the old price and the discount are used. The amount is shown in C5. In addition to the computations, we also want to display some text. We want to provide the data as follows:

The whole cost is $yyy.

where yyy is the cost in dollars.

The equation: can be applied to this.

$yyy,yyy.00 is the final price,&TEXT(A5*B5*C5))

Before integers with varying lengths, add a zero:

Everyone is aware that Excel will automatically erase any zeros inserted before numbers. However, the TEXT method is useful if we need to maintain those zeros. To further understand how to utilise this method, let's look at an example.

A 9-digit product code was provided to us, but Excel erased the zeros preceding it. The following example uses TEXT to change the product code into a 9-digit number:

The format code in the formula above has a total of nine zeros, where the number of zeros equals the number of digits we want to show.

Transforming phone numbers into a certain format using TEXT function:

The use of dashes and parentheses in format codes would be necessary if we wanted to achieve the same result for phone numbers.

I want to make sure that the country code is enclosed in brackets here (). As a result, the formula is (##) ### ### ###. The # indicates how many digits we want to utilise.

TIP: The TEXT function occasionally returns the error #NAME?This happens when we don't include the quotation marks around the format code.

To further clarify this, let's use an example:

If the formula =TEXT is entered (A2, mm-dd-yy). Since the formula is wrong and ought to be stated as =TEXT(A2,mm-dd-yy), it would result in an error.

Did you learn about how to use TEXT function in an Excel file? 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 the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience