Excel Text Function Format Codes

Combine Text and Number in a Custom Format

Text function is one of the meaningful functions in Excel that allows you to do different formatting types or numbers. This article will show various ways to use format codes of text functions for different purposes.

Downloadable Practice Workbook

What is the 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:

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 out the value of a specific format then we need a Text function. An example of the function is:

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

It will give the output like this:

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 Excel Text function format codes, but here I will show the most common and most used codes with their purposes. 

Format Code Purpose 
0 Digit placeholder that displays insignificant zeros.
? Digit placeholder that leaves a space for insignificant zeros but doesn’t display them.
# Digit placeholder that represents optional digits and does not display extra zeros.
. (period) Decimal point
, (comma) Thousands separator. A comma that follows a digit placeholder scales the number by a thousand.
[ ] Create conditional formats.

4 Uses of Excel Text Function Format Codes

1. Combine Text and Number in a Custom Format

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:

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

Explanation

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

Combine Text and Number in a Custom Format

Step 1: Enter the formula in cell D4.  

=”Total Price “&TEXT(B4*C4, “$###,###.00”)

Enter the formula using Text function in cell D4

Step 2: Copy down the formula up to D8.

Copy down the formula up to D8

2. Convert Values or Numbers to Phone Numbers in a Specific Format

For this process let’s assume we have some persons dataset with their phone numbers. But the given numbers are not well-formatted. Now we will convert the mentioned number 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 the formula as below:

=TEXT(B4,”[<=9999999]###-####;(###) ###-####”)

Explanation

As we need to format the phone numbers like the above example that is why first we 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 B 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 that 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 others 3 digit-4 digits pair.

onvert Values or Numbers to Phone Numbers in a Specific Format

Step 1: Enter the below in cell C4.

=TEXT(B4,”[<=9999999]###-####;(###) ###-####”)

Enter the formula using Text function for phone number format in cell C4

Step 2: Copy down the formula up to C8 to format all the numbers.

Copy down the formula up to C8 to format all the numbers

3. Adding Leading Zeros before Numbers with Variable Lengths

Microsoft Excel automatically removes leading zeros typed before a number in a cell but sometimes we may need to keep the leading zeros. This way Text function can help 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.

Adding Zeros before Numbers with Variable Lengths

Step 1: Enter the formula in cell D4.

=TEXT(C4,”0000000″)

Enter the formula using Text function to add leading zero in cell D4

Step 2: Copy down the formula up to D8.

Copy down the formula up to D8 to get all leading zero format

4. Combining Text and Date in the Desired Format

Sometimes we may need to combine text and date in our desired format. That time we can use the Text function date format to customize our output. For showing 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.

In the formula our syntax will be like this :

=Cell & Text & Text & TEXT(Cell,”mm/dd/yyyy”)

Explanation

For the above formula ampersand (&) is used to combine text. When we need to combine text with text or text with formula then in Excel it’s easily possible by using (&). It is the alternative to the CONCAT function in Excel. For more information, you can check this link: https://www.exceldemy.com/how-to-add-text-before-a-formula-in-excel/#1_Add_Text_in_a_Formula_Using_Ampersand

Here we just want to combine two cells and add some text. 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 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 like this format.

Combining Text and Date in the Desired Format

Step 1: Enter the below formula in cell C4.

=A4&”‘s “&”delivery date is  ” &TEXT(B4,”mm/dd/yyyy”)

 Enter the below formula in cell C4.

Step 2: Copy down the formula up to C10.

Copy down the formula up to C10

More Examples with TEXT Formats

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

  1. Let’s calculate today’s month using =MONTH(TODAY()). It will give the current month number. For example, for me, it’s June month so it will give 6 as the return value. But when I use =TEXT(MONTH(TODAY()),”mm”) This will return 01.
    User ProblemUser problem 2
    ExplanationAs we are converting the date to the number 6, and then you’re telling it to convert the number 6 into a date, which it then knows as 02/01/1900 (dd/mm/yyyy), which is the starting numeric value of Excel date. So then when you run the text formula, we are getting the 1 from January.
  2. Another problem is calculating 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 Simply just use the below formula:
    =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.
     User problem 3

Conclusion

These are the ways of using the TEXT function for codes in Excel. I have shown all the methods with their respective examples. Also, I 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.

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