Here’s an overview of a few applications of the **TEXT** function in Excel.

**Introduction to the TEXT Function**

**Function Objective:**

**Converts a value to text in a specific format.**

**Syntax:**

**=TEXT(value, format_text)**

**Arguments Explanation:**

Argument | Required/Optional | Explanation |
---|---|---|

value |
Required |
Value in a numeric form that has to be formatted. |

format_text |
Required |
Specified number format. |

**Return Parameter:**

**The value in a specified format.**

**How to Use the TEXT Function in Excel: 10 Suitable Examples**

**Example 1 – Using the TEXT Function to Modify the Date Format**

In the following dataset, a fixed date has been shown in different formats in column** B**.

- We can present the date in a textual format with the following formula:

`=TEXT(B5,"d mmmm, yyy")`

- Display the dates in other formats by modifying the date codes like in the picture below.

**Method 2 – TEXT Function to Connect Numerical Data to a Statement**

We’ll combine the string,** ”You Have to Pay…“ **and the total sum of the food prices along with 4% VAT=.

- In the output cell
**C9**, the related formula with the TEXT function should be:

`="You Have to Pay "&TEXT(SUM(C5:C7)+SUM(C5:C7)*D5,"$ ###,###.00")`

**Method 3 – Joining a Date with a Statement by Combining the TEXT and DATE Functions**

We’ll display today’s date in the following format: **“Today is…”** followed by the date in textual format.

- For our dataset, the formula in the output cell
**B9**will be:

`="Today is "&TEXT(TODAY(),"d mmmm, yyy")`

**Read More:** How to Use TEXT Function to Format Codes in Excel

**Method 4 – Adding Leading Zeros with the TEXT Function in Excel**

Let’s put the numbers from column B in five digits.

- The required formula in
**Cell C5**will be:

`=TEXT(B5, "00000")`

- Hit Enter and AutoFill the formula in needed.

**Method 5 – Formatting a Telephone Number with the TEXT Function**

While defining the format codes for telephone numbers, we have to replace the number characters with **Hash (#) **symbols.

- The required formula for the first telephone number will be:

`=TEXT(B5,"(###)-###-####")`

- Press
**Enter**and you’ll get the output with the defined format for a telephone number. - Use AutoFill if needed.

**Method 6 – Use the TEXT Function to Format Timestamps**

- The first formula to format a timestamp in a 12-hour clock system is:

`=TEXT(B6,"HH:MM AM/PM")`

- You can copy the formula and use it in your own Excel sheet with proper modifications for a timestamp.

**Read More:** How to Add Dashes to SSN in Excel

**Method 7 – Converting a Decimal to a Percentage with TEXT Function**

- The first output in the following table is the result of:

`=TEXT(B5,"0.00 %")`

- You can remove the decimals for the percentage by typing
**“0 %”**only in the second argument. If you want to see the output with only one decimal place, use**“0.0 %”**instead.

**Method 8 – Converting a Decimal to a Fraction with the TEXT Function**

- Use the following formula for the decimal value in
**Cell B5**:

`=TEXT(B5,"# ???/???")`

This returns a mixed fraction (such as 1 1/2 for 1.5).

**Method 9 – Converting a Number to the Scientific Notation with the TEXT Function**

- The required formula with the TEXT function is:

`=TEXT(B5,"0.00E+00")`

**Method 10 – Converting a Number to Geographic Coordinates with the TEXT Function**

- Use the following formula in the output
**Cell C5**:

`=TEXT(B5,"##0° #0' #0''")`

You can input the degree symbol in the second argument of the function by holding the **Alt** key and typing **0176** on the numeric keyboard.

** Things to Keep in Mind**

If you use a **hash (#)** in the format_text argument, it’ll ignore all insignificant zeros.

If you use **zero (0)** in the format_text argument, it’ll show all insignificant zeros.

Input **quotation marks (“ “)** around the specified format code.

Since the TEXT function converts a number to a text format, the output might be difficult to use later for calculations. Keep the original values elsewhere for further calculations if needed.

If you don’t want to use the TEXT function, you can also click on the **Number** command from the Number group of commands, and then type the format codes by selecting the **Custom** format option.

The TEXT function is really useful when you have to join a statement with a text in a specified format.

**Download the Practice Workbook**

**<< Go Back to Excel Functions | Learn Excel**