How to Use Excel TEXT Formula (4 Suitable Methods)

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:

• 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

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

Here are the number formula that will be

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

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

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.

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

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

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

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

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 –

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

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

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 –

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

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

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.

Further Readings

• Show Grand Total in Pivot Table (3 Easy Methods)
• How to Use CODE Function in Excel (5 Examples)
• Auto Generate Number Sequence in Excel (9 Examples)
• How to Use Format Function in Excel (with Suitable Examples)