TEXT

Using the Excel TEXT Function

Summary

The Excel TEXT function changes the way a number appears by applying formatting.

The TEXT function will convert numbers to text. This may make it difficult to use the number in calculations. Keep your original value, in number format, in one cell and add the TEXT version in another cell. This will allow you to reference the number cell if it is required in future formulas.


Syntax

=TEXT (value, format_text)

Syntax Breakdown

Value
Required. The numeric value you want to convert to text.

Format Text
Required. Text string describing the formatting you want applied to value. This argument must be enclosed in double quotation marks (""). See below for common format codes:

Format Code Description
"$#,##0.00" Currency with a thousands separator. Includes 2 decimal places, such as $5,432.10
"MM/DD/YY/" Date in MM/DD/YY format. For exapmle, 10/25/18
"0.0%" Percentage. For example, 5.4%
"# ?/?" Fraction. For example, 4 1/5
"0.00E+00" Scientific notation. For example, 1.01E+08


Usage Notes

TEXT will convert a number to text with a specified number formatting.

If you do not want to use TEXT, you can also format cells by going to Format Cells > Number dialog box, or pressing Ctrl + 1, and selecting the format you want.

Finding TEXT Formatting Codes
To find formatting codes to use with TEXT follow these steps:

1. Press Ctrl + 1 to access the Format Cells dialog box.

2. Go to the Custom option.

3. Select the formatting style that you want. The formatting code is show in the Type box. Highlight the formatting code and copy it by pressing Ctrl + C. Exit the formatting dialog box.

Excel TEXT Format Cells Example

4. Press Ctrl + V to paste the copied formatting code into the TEXT formula. Be sure the code is within double quotation marks ("") or the function will return an error.

Common TEXT Questions
Q: Will TEXT change the case of text?

A: The TEXT function does not change the case of text. Use UPPER, LOWER, or PROPER to change the case.

Q: Will TEXT convert numbers to text, such as 1 to One.

A: TEXT will not convert a number to text, such as 1 to One. More advanced users can use the SpellNumber function code in VBA to enable this capability.