Anything that you write is a form of text. They come in many different forms and can be represented in many different ways. In **Excel**, we can present values in a particular format using the** TEXT formula or function**. In today’s session, I’m going to show you a different use of the function in Excel. Before diving into the big picture, let’s get to know today’s workbook. You will find a few sheets (4 sheets in particular) in the workbook. All will represent various forms of values. But the basic table will remain the same. A total of four columns, **Example Input, Desired Value Format, Formula, and Result **have been used**. **

**Download Practice Workbook**

I have shared the workbook with you. You can download it from the link below.

**Introduction to Excel TEXT Function**

**Function Objective:**

TEXT function is used to convert a value to text in a specific number format.

**Syntax:**

**=TEXT(value, format_text)**

**Arguments Explanation:**

ARGUMENT | REQUIREMENT | EXPLANATION |
---|---|---|

value |
Required | Value in a numeric form that has to be formatted. |

format_text |
Required | Specified number format. |

**Return Parameter:**

A numeric value in a specified format.

**4 Suitable Methods of Using TEXT Function in Excel**

In Microsoft Excel, the TEXT function is generally used to convert a numeric value to a specified format for various purposes. In this article, you’ll get to learn how you can use this TEXT function effectively in Excel with appropriate illustrations. You’ll learn more about the methods and different formats to use the **TEXT** function with ease in the following sections of this article.

### 1. Formatting Number Values using the TEXT Function

You may need to format different number values for different forms of representation. First, here are some notable formats that are frequently used

In the example sheet of *Numbers,* we have a few example inputs and some desired input formats. Let’s see how we can achieve our desired format.

#### 1.1. Choose Decimal Points

Now, for a given number you may need to choose up to how many decimal places you want to see. For the time being, let, you need to set up to 2 decimal points. Then, the formula will be

`=TEXT(B5,"#.00")`

Again, “**#**” denotes the entire number before decimal points. Regardless of how many digits your number has before the decimal, you need to use only one “**#**”. After the decimal points, I set two 0s (zeroes), since I wanted two decimal places. The number of 0s will be as many places as you want to see.

It gave the result. We got the value with up to **2** decimal places. There is another one similar to this formation. Let’s do the same for that.

**Read More:** **How to Use FIXED Function in Excel (6 Suitable Examples)**

#### 1.2. Scientific Format

Moreover, you may need to form a number in a scientific format. Usually, we prefer any number presented in the *number E+ n digit* as a scientific one. You can pronounce it as the

*number*.

**E**to the power nHere are the number formula that will be

`=TEXT(B7,"0.0E+0")`

I wanted to go up to 1 decimal place (you can choose your format) before “**E+**” and then the number of powers. Let’s write it in Excel.

The larger number is now in a form of a much shorter and quicker readable format. Do the same for the next value as well.

#### 1.3. Decimal in Division Notation

Next, all our decimal values come from some division. Whenever you divide any value, the remainder forms the decimal places.

To write in division notation formula is as below

`=TEXT(B9,"0 ?/?")`

**0** for the full value result value(before the decimal point), **?/?** for the formation of the digits to present the remainder. Since it’s not known what will be the digits to represent the remainder as division so **?** is used

Do the same for the next example value as well.

#### 1.4. Adding Any Digits Before n Number

Furthermore, you can add any number of digits before a given number, the formula for that

`=TEXT(B11,"000000000")`

**N** can be any number, you want. If you want a 3-digit number within ** “ “** write 000.

Here I’ve wanted to represent **12** as a number of starting seven 0. So within the ** “ “ **I’ve written nine 0s.

**12**will replace the last two zeros and the rest of the seven zeros will come ahead of 12.

You can also write any alphabet as well. The letter will be shown within the text once you have put that here within the formatted text.

In this example, I add ‘** C00100**’ ahead of

**282**. You can do the same by choosing your suitable letter or digit.

#### 1.5. Represent Phone Numbers

Afterward, you can represent any number as a phone number.

`=TEXT(B13,"(###) ###-####")`

In the **USA** you will find a phone number of 10 digits. The first* three* are the area number, then the *three digits *of the exchange code, *last four* are the line number. Usually, the area code is written within bracket **() **and the exchange code and line number are separated by using a dash ( **–** ).

It gave the above result. Let’s do the same for the rest of the examples.

**Read More:** **How to Use TEXT Function to Format Codes in Excel**

### 2. Formatting Currency Using the TEXT Function

At times, when dealing with currency, we need to convert currency in Excel very frequently. It is quicker and handier if we can use any formula to convert currency. In this article, I will show you how to format currency using **the TEXT function** in Excel.

#### 2.1. Comma Separated Dollar Currency

Now, your formula to represent such a way is as follows

`=TEXT(B5,"$ #,##0")`

Here the value will be started with a **$** sign at the beginning and after every 3 digits, a *comma* will take place.

It gave the presentation we wanted. For the rest of the two, use the same formula and you will get the answer.

#### 2.2. Currency Value in Decimal Points

The formula will be the same as the previous one, just add a decimal point and zeros up to the place you want to see. Let we want to see up to two decimal points

`=TEXT(B8,"$ #,##0.00")`

Writing the formula in Excel we will find the result for our example as in the image below.

Do the same for the rest of the example input.

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

### 3. TEXT Formula for Percentage Formation

In this case, our goal is to learn how to use the** TEXT **function in **Percentage** formulas. We can learn this by first creating a **percentage** column and then using it under a certain condition. To use percentage form, we have to learn to convert a normal numeric value into a percentage. Technically Excel will convert any input data into a percentage by multiplying it by 100 and adding a percentage symbol (%) on the right if you opt to choose percentage formatting. But you can also convert a number directly to a percentage value without letting it be multiplied by 100 in Excel. The steps of this method are as follows.

#### 3.1. Percentage Formation

We can convert a decimal number into a percentage format. To do so, use the formula written below

`=TEXT(B5,"0%")`

This will convert the decimal value into percentage format. Write it in Excel.

Use the formula for the rest of the examples under this section.

#### 3.2. Percentage in Decimal Points

The formula will be the same as the previous one, just add a decimal point and zeros up to the place you want to see. Let we want to see up to one decimal point

`=TEXT(B7,"0.0%")`

Here I’ve set only up to 1 decimal place, you can choose your preferred one.

Let’s do the same for the next two examples as well. Here for example purpose, we have less amount of value. But, in the real scenario, you may have a chunk of values, use the AutoFill feature then.

**Read More:** **How to Show Percentage in Legend in Excel Pie Chart (with Easy Steps)**

### 4. TEXT Function for Date-Time Values

To format a timestamp, we have to use **HH (Hour), MM (Minute), SS (Second), and AM/PM **characters to define the required parameters. Here you have to keep in mind- in a **12-hour clock** system, you have to input the AM/PM exactly in **“AM/PM”** text, not in “PM/AM” format at all, otherwise, the function will return with an unknown text value- “P1/A1” at the defined position in the timestamp. In the following screenshot, a fixed timestamp has been shown in different but common formats after formatting. You can easily convert a 12-hour clock system to a 24-hour clock system and vice-versa by using **this TEXT function**.

#### 4.1. Time in International Standard

To convert your local time into the 24-hour standard form you can use the formula –

`=TEXT(B5,"hh:mm")`

**HH:** Hour

**MM:** Minutes

*Use AM/PM in your input time to let Excel understand the right time.*

Write the formula for this example in the sheet.

As our provided time was **6:00 PM** it gave us **18:00**, the format we were anticipating. The next example uses AM time.

#### 4.2. Only Time from Full Date-Time

If you use **the NOW function** you will find the current date and the time. To see the time only write the formula below

`=TEXT(B6,"hh:mm")`

Similar to the previous one, since the previous one was also shown the time. Write the formula for this example.

#### 4.3. Time Month Day Format

If you want to show time -month- day from a particular time just use the function below

`=TEXT(B7,"HH:MM O'Clock, MMMM DD")`

**HH: MM** represents the time

**MMMM** represents the month name

**DD** represents Date

For a better understanding of time, I used **O’Clock**, so that you can differentiate that it is time value. Let’s write the formula for the example time input. This input time has been generated using the **NOW** function.

We have found the result in time month and date format.

#### 4.4. Date Separating by ‘/’

More often you will write the date separating by “-“, but if you want to write it using “/”, then use the formula –

`=TEXT(B8,"MM/DD/YYYY")`

**MM:** Month

**DD:** Date of the month

**YYYY:** Year ( this will show the full 4-digit year, use **YY** to show 2 digits of the year)

#### 4.5. Day Name–Month-Year Format

You may need to form the date in a way of the day of the week, month name, and year. The formula for that will be

`=TEXT(B9,"DDDD,MMMM YYYY")`

**DDDD:** Day name

**MMMM:** Month name

**YYYY:** Year

Here my intention was to show the *day name, month name, *and *year, *that’s why I have written in this way. You can choose your suitable format.

#### 4.6. Month-Date-Year Format

By the time we are in this section, you have already understood how to do this task. Though I’m writing the formula for you. I suggest you write your own first and then check, that will evaluate your understanding.

The formula will be

`=TEXT(B11,"MMMM DD,YYYY")`

Hope you understand the meaning of **MMMM, DD, YYYY**. Let’s see the result of the example.

**Read More:** **How to Use CHAR Function in Excel (6 Suitable Examples)**

## Conclusion

That’s all for today. I’ve tried listing a couple of ways of using Excel’s **TEXT** formula. Henceforth, follow the above-described methods. We will be glad to know if you can execute the task in any other way. Follow the **ExcelDemy** website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our level best to solve the problem or work with your suggestions.