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

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

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

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 ?/?”) 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 Do the same for the next example value as well. #### 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. 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. 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. 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,”(###) ###-####”) 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 ( ). It gave the above result. Let’s do the same for the rest of the example. ### 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. 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 had 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 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”) 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. ### 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%”) This will convert the decimal value into percentage format. Write it in Excel. Use the formula for the rest of the example under this section. #### 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%”) 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. ### 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”) 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. Next example using AM 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”) Similar to the previous one, since the previous 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 as below

TEXT(value,”HH:MM O’Clock, MMMM DD”) HH: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. 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”) MM: Month

DD: Date of the month

YYYY: Year ( this will show full 4 digit year, use YY to show 2 digit of year) #### 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”) DDDD: Day name

MMMM: Month name

YYYY: Year 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”) Hope you understand the meaning of MMMM,DD,YYYY. Let’s see the result of the example. ## 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.  