Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Downloadable Practice Workbook

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


What Are the Format Codes for TEXT Function?

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

TEXT(value, format_text)

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.

=TEXT(TODAY(),"MM/DD/YY")

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.

Format Code Purpose
0 Shows leading zeros.
? Leaves spaces instead of showing leading zeros.
# Represents optional digits and does not show extra zeros.
. (period) Appears decimal point.
, (comma) Thousands separator.
[ ] Create conditional formats.

4 Examples of TEXT Function to Format Codes in Excel

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. Combine Texts and Numbers with Custom Formatting

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

dataset to combine text and numbers

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:

=”Text” & TEXT( Formula, “$###,###.00”)

📌 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.
="Total Price "&TEXT(C5*D5, "$###,###.00")

excel TEXT function format codes

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


2. Applying Proper Phone Number Format

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

dataset to format phone numbers

  • 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:
TEXT(Cell,”[<=9999999]###-####;(###) ###-####”)

📌 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.
=TEXT(C5,"[<=9999999]###-####;(###) ###-####")

format phone numbers using excel text function


3. Adding Leading Zeros Before Numbers

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.

dataset to add leading zeros

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.
=TEXT(C5,"0000000")

leading zeros with excel text function

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


4. Combining Text and Date in the Desired Format

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.

dataset to combine text and date

  • 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.
=Cell & Text & TEXT(Cell,”mm/dd/yyyy”)

📌 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.

TEXT(Cell,”mm/dd/yyyy”)

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.
=B5&"'s delivery date is  " &TEXT(C5,"mm/dd/yyyy")

conbine text and date with excel TEXT function


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.

current month in excel

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

current month with TEXT function
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.
=TEXT(TODAY(),"dd ") & "Days " & TEXT(NOW(),"hh ") & "Hours"
  • Using this formula, the first function TEXT(TODAY(),”dd “) calculates today’s date number, and TEXT(NOW(),”hh “) finds out the current hours.

combine date and time


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.

excel text format

Note: Also, you can press CTRL + 1 to open the Format Cells dialog box.

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.

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.


Further Readings

Md. Abdullah Al Murad

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo