What does it do?
Converts a numeric value to text and lets you specify the display formatting by using special format strings
Formula breakdown:
=TEXT(value, format text)
What it means:
=TEXT(a numeric value or a formula, a text string enclosed in quotation marks)
The TEXT function in Excel allows you to convert a numeric value to a specific format by using special format strings.
If you have a date and want to show just the month or if you have a large number and want to show it in a thousands format , then the TEXT function is your savior.
An example usage of the TEXT would be:
Following is a demonstration and a table of the different ways you can display a value in Excel by using the TEXT function:
How to Use the Text Formula in Excel
| To display | As | Use this format |
| 1234.59 | 1234.6 | “####.#” |
| 8.9 | 8.900 | “#.000” |
| 0.631 | 0.6 | “0.#” |
| 1234.568 | 1234.6 | “#.0#” |
| 5.25 | 5 1/4 | “# ???/???” |
| 12000 | 12000 | “#,###” |
| 12000 | 12 | “#,” |
| 12200000 | 12.2 | “0.0,,” |
| Months | 1–12 | “m” |
| Months | 01–12 | “mm” |
| Months | Jan–Dec | “mmm” |
| Months | January–December | “mmmm” |
| Months | J–D | “mmmmm” |
| Days | 1–31 | “d” |
| Days | 01–31 | “dd” |
| Days | Sun–Sat | “ddd” |
| Days | Sunday–Saturday | “dddd” |
| Years | 00–99 | “yy” |
| Years | 1900–9999 | “yyyy” |
| Hours | 0–23 | “h” |
| Hours | 00–23 | “hh” |
| Minutes | 0–59 | “m” |
| Minutes | 00–59 | “mm” |
| Seconds | 0–59 | “s” |
| Seconds | 00–59 | “ss” |
| Time | 4:00 AM | “h AM/PM” |
| Time | 4:36 PM | “h:mm AM/PM” |
| Time | 4:36:03 PM | “h:mm:ss A/P” |
| Time | 36:03.8 | “h:mm:ss.00” |
