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.

**Watch Video – TEXT Function to Format Codes in Excel**

**Table of Contents**hide

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

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

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:

**=”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")`

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

**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]###-####;(###) ###-####")`

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

`=TEXT(C5,"0000000")`

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

**=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")`

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

`=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.

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

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

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