How to Use TEXT Function to Format Codes in Excel

The TEXT function is one of the meaningful functions in Excel that allows you to do different formatting types or numbers. This article shows various ways to use format codes with the TEXT function for different purposes.

What Are Excel TEXT Function Format Codes?

First, we should know the syntax and purpose of the TEXT function. The syntax of this function is like this:

So, using the TEXT function, we can format any values or numbers using different format codes.

This function is needed when we want to modify or want to get the value of a specific format. Then we need the TEXT function. An example of the function is as follows.

It will produce the following output:

Today’s date is in MM/DD/YY format, like 29/06/21. So, using the TEXT function, we can easily customize our final output as per our requirements.

Basically, there are a lot of format codes that can be used with the TEXT function in Excel. But here I will show the most common and most used codes with their purposes.

This section discusses diverse uses of the TEXT function format codes e.g. combining the text with the number or date, adding a leading zero, and converting numbers in a defined format. Let’s dive into the uses!

1. Joining Texts and Numbers with Excel TEXT Function Format Codes

Let’s have a dataset of some fruits and their unit price and quantity. The last column is the total price column.

Now we will calculate the total price with a text and currency symbol, thousands separator, and two decimal places using the TEXT function.

For this, our formula will be like this:

📌 Formula Explanation:

Firstly, we add text at the front using the ampersand (&) symbol. Then in the TEXT function in the first section of the parameters, we will use our formula to count the desired output. After that, put a $ sign at the front for formatting, as the currency symbol here is a dollar, a comma (,) for thousand separators, and a # for representing optional digits.

• So, first, enter the following formula in cell E5 and drag the Fill Handle icon below to copy the formula.

Read More: How to Combine Text and Numbers in Excel and Keep Formatting

2. Applying Phone Number Format Codes with TEXT Function in Excel

Let’s assume we have a dataset containing some phone numbers for this method. But the given numbers are not well-formatted.

• Now we will convert the mentioned numbers into proper phone numbers using the TEXT function. So, we want to make phone numbers like this:

(555) 555-1234

• For this we need to use a formula as given below:

📌 Formula Explanation:

As we need to format the phone numbers like in the above example, we first need a condition to separate the last 7 digits from the given numbers. We have to use [ ] for conditional purposes. Then we need # for a decimal placeholder to form the number as per our needs.

• Firstly, in the first section of the TEXT function, it will take the given input which is our C column, as we are taking the given phone numbers without formatting from this column.
• Secondly, the format section [<=9999999] is checking the numbers from the right side if it is less than or equal to 7 digits or not. Then it converts the first 7 digits into ###-#### form which means 3 digit-4 digits pair. After that the sub-section (###) ###-#### format the whole number into like this (555) 555-1234. So, the last 3 digits get covered by () and the others 3 digit-4 digits pair.
• So, first, enter the following formula in cell D5 and then drag the Fill Handle icon below.

3. Adding Leading Zeros Before Numbers with Excel TEXT Function

Excel removes leading zeros typed before numbers automatically. But sometimes we may need to keep the leading zeros. Then the TEXT function can help us to do that with its format codes. Let’s have a dataset of some employees with their names and id.

We want to keep all the employees’ IDs in 7 digits, but some of the IDs are not fully 7 digits. We will convert all the IDs into 7 digits using the format code of the TEXT function.

• So, first, enter the following formula in cell D5 and then drag the Fill Handle icon below.

Read More: How to Use Format Function in Excel (with Suitable Examples)

4. Combining Text and Date Format Codes in Excel with TEXT Function

Sometimes, we may need to combine text and date in our desired format. We can use the TEXT function’s date format code to customize our output. To demonstrate this method, let’s think about a dataset of some products and their delivery dates.

• Now we will combine the products’ names and delivery dates and show them in a single column using TEXT function format codes.
• The syntax of the formula to do that will be as follows.

📌 Formula Explanation:

In the above formula, an ampersand (&) operator is used to combine texts. When we need to combine text with text or text with a formula, then in Excel it’s easily possible by using the ampersand (&). It is the alternative to the CONCAT function in Excel. For more information, you can check How to Add Text Before Formula in Excel.

Here we just want to combine two cells and add some text. You’ll also want to do formatting of the dates using the TEXT function’s format code.

In the first section of the parameter, it takes the values, so as we want to pass the column values of delivery dates, that’s why we need to pass the cell number here. In the double quotation, we have declared the formation of dates by using the mm/dd/yyyy format.  So, from our given dates, it will format the dates in this format where mm-> month dd-> day yyyy-> year. So, our date will be like this: 05/07/1998.

• So, first, enter the following formula in cell D5 and then drag the Fill Handle icon below.

More Examples with Excel TEXT Function to Format Codes

Here I will discuss some user problems and solutions for the TEXT function.

• Let’s calculate today’s month using =MONTH(TODAY()). It will give the current month’s number. For example, for me, it’s October so it will give 10 as the return value.

• But when I use =TEXT(MONTH(TODAY()),”mm”) this will return 01.

Why Is This Error Happening?

We are converting the date to the number 10, and then you’re telling it to convert the number 10 into a date, which is then known as 02/01/1900 (dd/mm/yyyy), which is the starting numeric value of an Excel date. So then when you run the text formula, we are getting the 1 from January.

• Another problem is calculating the date and time. If we need to find out both today’s day number and current hour. It is possible to calculate those using the TEXT function. Simply just use the formula below.

• Using this formula, the first function TEXT(TODAY(),”dd “) calculates today’s date number, and TEXT(NOW(),”hh “) finds out the current hours.

Application of Excel Text Format

Excel can automatically detect the value you try to input into a cell. So, it will convert the value to the detected format as soon as you enter it, even though you may not want it to do so. Therefore, the intelligence of Excel, which is extremely useful most of the time, may sometimes become an annoyance for you.

For example, assume you are trying to enter 5-10 in a cell to indicate 5 to 10. However, Excel will treat it as a date. So, it will be entered as Oct 5 or May 10 of the current year, depending on the system date setting. You will face similar annoying results when you are trying to enter codes with leading zeros. Because excel will consider the leading zeros as redundancy and remove them automatically.

So, what do you do to avoid such situations? Well, don’t worry. You can just format the cells as text before entering data. Then, Excel will store values as you input them without any change.

Suppose you want to enter some IDs with leading zeros in the range B2:B100. Then select the range and press CTRL + 1 or go to Home >> Format >> Format Cells. Next, select the Text category from the Number tab in the Format Cells dialog box and click OK. After that, you can input anything you want, and Excel will not change anything.

Things to Remember

• Don’t forget to use double quotes around the format codes. Otherwise, the TEXT function will return #NAME! error.
• The TEXT function converts numeric values to text strings. So, you won’t be able to use the output cell as a reference for a numeric value in other formulas. You may need to use other number formats to avoid such limitations if necessary.

Downloadable Practice Workbook

You can download the practice workbook from the download button below.

Conclusion

These are the ways of using the TEXT function format codes in Excel. I have shown all the methods with their respective examples. Also, we have discussed the fundamentals of this function and the most commonly used format codes of this function. If you have any other method of achieving this, then please feel free to share it with us. Don’t forget to visit our ExcelDemy blog to explore more about Excel. Stay with us and keep learning.

Related Articles

• How to Use CODE Function in Excel (5 Examples)
• Use CHAR Function in Excel (6 Suitable Examples)
• How to Show Grand Total in Pivot Table (3 Easy Methods)
• Show Percentage in Legend in Excel Pie Chart (with Easy Steps)
• How to Use FIXED Function in Excel (6 Suitable Examples)
• Auto Generate Number Sequence in Excel (9 Examples)

<< Go Back to Excel TEXT Function | Excel Functions | Learn Excel