Excel Functions and Formulas, TEXT()

Jun 20, 2015 by azurous in Excel

The TEXT() function converts a number value to a text string, with the format specified

Jump To:

Syntax
Example 1, Converting Numeric Value to a Text String Using Currency Formatting
Example 2, Adding Converted Value to a Text String
Example 3, Converting Numeric Value to a Text String Using Decimal Formatting
How to Get the Formatting String

Contents

Syntax:

=TEXT(Value, Format)

Value: The number value to convert to a text string.

Format: The formatting to apply to the converted value.

–

Example 1, Converting a Numeric Value to a Text String Using Currency Formatting:

In this example the value in cell A1 is converted to a text string with the currency formatting. Write the following formula in cell B1:

=TEXT(A1, "$#,##0.00")

Result:

–

Example 2, Adding Converted Value to a Text String:

In the previous example you might be wondering what was the use of converting the numeric value to a text string? We could have easily just used the formula:

=A1

in cell B2, and just formatted the cell to have the currency formatting and the same result would have been obtained?

Answer: The reason for converting numeric values to a text strings is to be able to add (concatenate) them to other text string values while preserving their number format. For more information about adding (concatenating) text strings please see this article. For example lets say we want to create the text string:

“John has spent ____ on buying a new house”

and replace the underline with the money value in cell B1. This can only be done if the value in cell B1 is a text string. Add the formula below to cell C1:

="John has spent " & B1 & " on buying a new house"

Result:

By changing the value in cell A1, The text in cell C1 will update automatically:

Lets say instead of converting the value in cell A1 to a text string we just used the numeric value and applied the currency formatting to cell B2. This is what would have happened:

As you can see the currency formatting has been lost:

–

Example 3, Converting a Numeric Value to a Text String Using Decimal Formatting:

Write the following formula in cell B1:

=TEXT(A1, "0.00")

This formula will convert the numeric value in cell A1 to a text string with the decimal formatting in cell B1:

Result:

–

How to Get the Formatting String:

In the first example I used the formatting string below to apply the currency formatting to the converted numeric text string:

“$#,##0.00”

In the second example I used the formatting string below to apply the decimal formatting to the converted numeric text string:

“0.00”

So where did I get these from? How can you figure out what formatting string to use?

Answer: Right click any cell and click on Format Cells …:

From the number tab select your desired formatting from the list of available formatting options:

Without closing the dialog click on the Custom Category. The formatting you have chosen will appear in the text box marked below: