In Microsoft Excel, the TEXT function is generally used to convert a numeric value to a specified format for various purposes. In this article, you’ll get to learn how you can use this TEXT function effectively in Excel with appropriate illustrations.

The above screenshot is an overview of the article, representing a few applications of the **TEXT** function in Excel. You’ll learn more about the methods and different formats to use the **TEXT** function with ease in the following sections of this article.

**Table of Contents**hide

**Download Practice Workbook**

You can download the Excel workbook that we’ve used to prepare this article.

**Introduction to the TEXT Function**

**Function Objective:**

**TEXT function is used to convert a value to text in a specific number 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:**

**A numeric value in a specified format.**

**10 Suitable Examples of Using TEXT Function in Excel**

**1. Using TEXT Function to Modify Date Format**

TEXT function is basically used to modify a date format in Excel. In the first argument, you have to input the date value or the cell reference of a date. Then you can define a proper date format by your own customization.

For example, in the following picture, a fixed date has been shown in different formats in **Column C**. The first output, what we can obtain by typing the following formula:

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

You can copy the above formula and paste it into your spreadsheet to find the date format in a particular format if your **Cell B5** contains a date value or a date. You can display the dates in some other formats too by modifying the date codes like in the picture below.

**2. TEXT Function to Connect Numerical Data to a Statement**

In the picture below, an example of connecting a statement to numerical data in currency format is shown. The statement here is:** ”You Have to Pay…“ **and then the total sum of the food prices along with 4% VAT will be added after that statement. Technically we have to concatenate the two data by using an **Ampersand (&)** inside.

So 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")`

**3. Joining a Date with a Statement by Combining TEXT and DATE Functions**

Like the method shown in the previous section, we can also concatenate a text and a date by using **Ampersand (&)** as well as modifying the date format. The incomplete statement in the following screenshot is- **“Today is…”** and after this part, the current date has to be inputted in a proper format. So we can use the **TODAY **function here to include the current date and in the second argument of the **TEXT** function, we can modify the date code according to our wishes.

So, for our dataset, the related formula in the output **Cell B9** will be:

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

**4. Adding Leading Zeros with TEXT Function in Excel**

To keep or add leading zeros in a numerical value, the TEXT function can serve the purpose with the most suitable formula. In the following dataset, assuming that we want to resize all numbers to display a similar size by adding leading zeros before the numbers. All the numbers will be shown in five digits.

For the first instance, the required formula in **Cell C5** will be:

`=TEXT(B5, "00000")`

After pressing **Enter** and auto-filling the rest of the cells in **Column C** with **Fill Handle**, you’ll get the desired results at once.

**5. Formatting Telephone Number with TEXT Function**

We can modify the telephone numbers in a particular format with the TEXT function. In the picture below, the relevant examples are being shown. While defining the format codes for telephone numbers, we have to replace the number characters with **Hash (#) **symbols.

So, 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.

**6. Use of TEXT Function to Format Timestamps**

To format a timestamp, we have to use **HH (Hour), MM (Minute), SS (Second), and AM/PM **characters to define the required parameters. Here you have to keep in mind- in a **12-hour clock** system, you have to input the AM/PM exactly in **“AM/PM”** text, not in “PM/AM” format at all, otherwise, the function will return with an unknown text value- “P1/A1” at the defined position in the timestamp.

In the following screenshot, a fixed timestamp has been shown in different but common formats after formatting. You can easily convert a 12-hour clock system to a 24-hour clock system and vice-versa by using this TEXT function.

Here, the first formula to format the timestamp in a 12-hour clock system is:

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

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

**7. Converting Decimal to Percentage with TEXT Function**

By using the TEXT function, you can convert a decimal number to a percentage more easily. You have to simply input **“0.00 %”** in the second argument. It’ll multiply the selected number or decimal defined in the first argument with 100 and add a percentage **(%) **symbol at the end.

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. Or if you want to see the output with only one decimal place then you can use-** “0.0 %”** instead.

**8. Converting Decimal to Fraction with TEXT Function**

To convert a decimal value to a proper or a mixed fraction, you have to use the following formula for the decimal value in **Cell B5**:

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

In the second argument of the **TEXT** function, the format code is primarily assigned to return the output with the mixed fraction. But if the decimal value does not contain an integer value except 0 before the decimal point, then the function will return a proper fraction instead of a mixed fraction.

**9. Converting Number to Scientific Notation with TEXT Function**

Formatting a large string of digits in a number to a scientific notation is way too easy with the appropriate use of the TEXT function. You have to simply input an exponent with the letter** ‘E’ **after the defined characters for coefficients. By adding **‘+00’** after **‘E’**, you have to denote the number of characters for the exponent power.

Based on the first output displayed in the picture below, the required formula with the TEXT function is:

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

**10. Converting Number to Geographic Coordinates with TEXT Function**

To convert a number to a geographic coordinate, the related formula with the TEXT function in the output **Cell C5** is:

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

Here you have to input the degree symbol in the second argument of the function by holding the **ALT** key and then pressing **0,1,7 & 6** one by one.

**💡**** Things to Keep in Mind**

🔺 If you use **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.

🔺 You must not forget to 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. So, it’s better to keep the original values in an additional column or row 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 you have to type the format codes by selecting the **Custom** format option.

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

**Concluding Words**

I hope all of the suitable methods mentioned above to use the** TEXT **function will now provoke you to apply them in your Excel spreadsheets with more productivity. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.