How to Use TEXT Function in Excel (10 Examples)

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:

• 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:

• 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:

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:

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:

• 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:

• 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:

• 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:

• 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:

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:

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

• Use the following formula in the output Cell C5:

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