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

**Table of Contents**hide

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

**Step 1:** Enter the formula in cell **D4**. ** **

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

**Step 2:** 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.

**Step 1:** Enter the below in cell **C4**.

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

**Step 2:** 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.

**Step 1:** Enter the formula in cell **D4.**

**=TEXT(C4,”0000000″)**

**Step 2:** Copy down the formula up to **D8**.

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

**Step 1:** Enter the below formula in cell **C4.**

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

**Step 2: **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.

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

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