Excel TEXT Function (9 Practical Examples)

Excel TEXT Function

The Excel TEXT Function is used to change the way a number appears by applying formatting to it with format codes. It’s useful in situations where users want to display numbers in a more readable format, or users want to combine numbers with text or symbols. The Excel TEXT function will convert numbers to text, with which users cannot apply mathematical operations. It is suggested that users keep the original values in a separate cell and then use the text function in another cell.

In this section:

---

1. Syntax of TEXT Function
2. Convert numbers to currency with thousand separator and decimal places
3. Convert today’s date to a text format like 06/22/22
4. Modify Date format
5. Text Function with leading zeroes
6. Format telephone number with TEXT Function
7. Use TEXT Function to format time
8. Convert decimal to percentage by TEXT Function
9. Convert decimal to fraction using TEXT Function
10. Convert number to scientific notation with TEXT Function

---

1. Syntax of TEXT Function:

=TEXT (value, format_text)

where:

• value: The number or the cell reference containing number to convert.
• format_text: The format users want to use.

value – The number to convert.

format_text – The number format to use.

2. Convert numbers to currency with thousand separator and decimal places:

To convert numbers to currency with thousand separator and decimal, the formula is =TEXT(C6, “$#, ##0.00”), which returns currency with dollar sign and thousand separator.

3. Convert today’s date to a text format like 06/22/22:

To convert today’s date like 06/22/22, formula is =TEXT(TODAY(), “mm/dd/yy”), which returns today’s date in the format 06/22/22.

4. Modify Date format:

Users should follow the examples below to learn to modify dates in different ways:

5. Text Function to add leading zeroes:

To add leading zeroes, the formula is =TEXT(C6, “000000”). Users can add number of zeroes as they require.

6. Format telephone number with TEXT Function:

To format telephone numbers, the formula with TEXT Function is =TEXT(C6,”(###)-###-####”), which format the telephone numbers. Users can also use different format as their requirements.

7. Use TEXT Function to format time:

To format time, the formula ( in cell D6) is =TEXT(C6, “hh : mm”). Users can modify the formula as they need.

8. Convert decimal to percentage by TEXT Function:

To convert decimal to percentage the formula (in cell D6) is =TEXT(C6, “0.00”). Users can modify the formula as they need.

9. Convert decimal to fraction using TEXT Function:

To convert decimal to fraction, the formula (in cell D6) is =TEXT(C6, “# ?/?”).

10. Convert number to scientific notation with TEXT Function:

To convert numbers to scientific notation with TEXT Function, the formula (in D6) is =TEXT(C6, “0.00E+00”).

More related readings:

1. MS Office post on TEXT Function
2. Extract first Name in excel (3 practical examples)
3. Extract Middle names (3 practical examples)
4. Excel ARRAYTOTEXT Function
5. Excel CLEAN Function with practical examples
6. Excel CONCAT Function to join text
7. Excel REPLACE Function to replace text
8. Excel TRIM Function with Practical Examples
9. Excel RIGHT Function