Description
Use this function to convert a number to text in a specific number format. Supported in Chains.
TEXT is useful for converting numbers to text in various formats such as dates, times, currency, and more.
Syntax
TEXT(value, format_text)
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
value |
Yes | The numeric value that you want to convert to text. | A number, a date, or a cell reference that contains a number or date. |
format_text |
Yes | The format in which you want to display the value. | A text string that defines the number format (e.g., "0.00", "$#,##0.00", "mm/dd/yyyy"). |
Example
Sample data
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 1000.567 | 0.89 | 44800 | 0.256 | 2022-03-11 |
Sample Formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Display a number as text with a fixed number of decimal places. | =TEXT(A1, "0.00") |
Converts 1000.567 to a text string rounded to 2 decimal places. Result: 1000.57 |
| Display a decimal value as a percentage. | =TEXT(B1, "0%") |
Converts 0.89 to a text string formatted as a whole-number percentage. Result: 89% |
| Display a number as text formatted like currency. | =TEXT(C1, "$#,##0.00") |
Converts 44800 to a text string with a dollar sign, thousands separator, and 2 decimal places. Result: $44,800.00 |
| Display a small decimal value as text in scientific notation. | =TEXT(D1, "0.00E+00") |
Converts 0.256 to a text string in scientific notation. Result: 2.56E-01 |
| Display a date value as text in a specific date format. | =TEXT(E1, "mm/dd/yyyy") |
Converts the date 2022-03-11 to a text string formatted as month/day/year. Result: 03/11/2022 |
Notes
- TEXT doesn't change the value of the original cell; it only changes how the value is displayed.
- The
format_textargument must be enclosed in quotation marks. - Be careful when using TEXT in calculations, as the result is a text string and not a numeric value.
- Common number formats include "0.00" for decimal places, "$#,##0.00" for currency, "mm/dd/yyyy" for dates, and "0%" for percentages.
- TEXT is useful for creating custom number formats that are not available in the standard Workiva formatting options.
Tips
- Use TEXT when you need to display numbers in a specific format within text strings.
- Combine TEXT with other functions such as CONCATENATE or '&' to create more complex text strings.
- Ensure the
format_textstring accurately represents the desired format to avoid unexpected results. - For dates, be aware of regional date formats and adjust the
format_textstring accordingly.