Shakil Ahmed
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.
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.
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
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
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.
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
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
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
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.
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
In place of 0 you can write # as well.
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
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
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
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 –
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
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
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 –
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
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
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.
