Excel Text Formula (TEXT Function)

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.

Workbook - Excel Text Formula

You will find a few of 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, Result have been used

We will see different uses of the TEXT function using examples using the table.

Practice Workbook

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

Basics Of the TEXT Formula

TEXT in Excel returns a value in a given format. Giving a value set a format it will provide the value as that format. The output of the TEXT function will be in text.

There are two parameters in the TEXT function. They are, value and format.

TEXT(Value, Format)

Value: Text you want to format

Format: Your desired format

Remember, you have to write your format within “ “.

For more information, you can explore the official Microsoft support site. I’m leaving the link for you here.

Use of the TEXT Formula

1. Formatting Number Values using TEXT Function

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

Example - Excel Text Formula

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

1.1. Choose Decimal Points

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(number,”#.00”) 

Choose Decimal Points

Here “#” denotes the entire number before decimal points. Regardless of how many digits your number has before decimal you need to use only one “#”.

After the decimal points, I’ve set two 0s (zeroes), since I wanted two decimal places. The number of 0s will be as many places you want to see.

Write it in Excel.

Example number Decimal Points

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.

Decimal points examples

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

1.2. Scientific Format

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

Here our number formula will be

TEXT(number,”0.0E+0”)  

Scientific Numbers- Excel Text Formula

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

Scientific number Example

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

Examples of scientific numbers format

1.3. Decimal in Division Notation

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(number, “0 ?/?”)

Division Notation formula - Excel Text Formula

0 for the full value result value(before decimal point), ?/? for 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

Example of Division Notation

Do the same for the next example value as well.

Division Notation Example - Excel Text Formula

1.4. Adding any digits prior to n Number

You can add any number of digits prior to a given number, the formula for that

TEXT(number, ”n number of 0”)

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

Add number prior - Excel Text Formula

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 zero and the rest of the seven zero will come ahead of 12.

Add number example - Excel Text Formula

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

Add alphabet - Excel Text Formula

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

You can represent any number as a phone number.

TEXT(number,”(###) ###-####”)

Phone number format formula - Excel Text Formula

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

Phone number format example - Excel Text Formula

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

Phone Number examples - Excel Text Formula

2. Formatting Currency using TEXT Function

2.1. Comma Separated Dollar Currency

Your formula to represent such a way is as follow

TEXT(value,”$ 0,000”)

In place of 0 you can write # as well.

TEXT(value,”$#,###”) 

Let’s use this # formula.

Currency formation - Excel Text Formula

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

Example currency - Excel Text Formula

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

Examples of Currency formation - Excel Text Formula

2.2. Currency value in decimal points

The formula will be the same as previous, 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(value,”$ #,###.00”) 

Decimal Currency formation - Excel Text Formula

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

Example of Decimal Currency - Excel Text Formula

Do the same for the rest of the example input.

Examples Currency Decimal - Excel Text Formula

3. TEXT Formula for Percentage Formation

3.1. Percentage Formation

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

TEXT(value,”0%”)

Percentage formation formula - Excel Text Formula

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

Example of Percentage formation - Excel Text Formula

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

Examples Percentage formation - Excel Text Formula

3.2. Percentage in Decimal Points

The formula will be the same as previous, 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(value,”0.0%”) 

Percentage decimal format- Excel Text Formula

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

Percentage decimal example - Excel Text Formula

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.

Examples Percentage decimal - Excel Text Formula

4. TEXT Function for Date-Time Values

4.1. Time in International Standard

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

TEXT(value,”HH:MM”)

Standard time format - Excel Text 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.

Example Standard Time format

As our provided time was 6:00 PM it gave us 18:00, the format we were anticipating. Next example using AM time.

AM example stand time

For input 7:00 AM we found 07:00. It’s working correctly.

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 as below

TEXT(value,”HH:MM”) 

Time only - Excel Text Formula

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

Time only example

4.3. Time Month Day Format

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

TEXT(value,”HH:MM O’Clock, MMMM DD”)

Time Month Day formula - Excel Text FormulaHH:MM represents the time

MMMM represents 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.

Time Month Day

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

4.4. Date Separating by ‘/’

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

TEXT(value,”MM/DD/YYYY”)

Date Separating - Excel Text Formula

MM: Month

DD: Date of the month

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

Date Separation Example

4.5. Day Name – Month- Year format

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

TEXT(value,”DDDD,MMMM YYYY”)

Day Name Month Year format - Excel Text Formula

DDDD: Day name

MMMM: Month name

YYYY: Year

Day name month example

Here my intention was to show day name, month name and the 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(value,”MMMM DD,YYYY”) 

Month Date Year format

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

Example of Month date year - Excel Text Formula

Conclusion

That’s all for today. I’ve tried listing a couple of ways of using Excel’s TEXT formula. Hope that will help you. Feel free to comment if anything seems hard to understand. You can also let us know any other of its uses we may have missed.


Further Readings

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo