Excel Functions and Formulas, TEXT()

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

Jump To:

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")

Excel, Formulas, Functions, TEXT, Example 1
Result:

Excel, Formulas, Functions, TEXT, Example 1 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"

Excel, Formulas, Functions, Text, Example 2
Result:

Excel, Formulas, Functions, Text, Example 2 Result

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

Excel, Formulas, Functions, Text, Example 2 Change
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:

Excel, Formulas, Functions, Text, Example 2 Error. png
As you can see the currency formatting has been lost:

Excel, Formulas, Functions, Text, Example 2 Error Restul. png


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:

Excel, Formulas, Functions, Text, Example 3
Result:

Excel, Formulas, Functions, Text, Example 3 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 …:

Excel, Formulas, Functions, Text, Format String

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

Excel, Formulas, Functions, Text, Format String Number Tab
Without closing the dialog click on the Custom Category. The formatting you have chosen will appear in the text box marked below:

Excel, Formulas, Functions, Text, Format String Number Tab, Custom Category

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *