The text formula in excel is quite versatile and can be used to solve many problems. In a previous blog, I showed how to use the Date formula in excel.
You can use the text formula in excel in combination with the date formula to make various date formats.
Essentially, the TEXT formula is used to convert numbers into text, and you can use this feature to your advantage by doing lots of things.
So, let’s look at how to use TEXT formula in Excel.
How to Use Text Formula in Excel?
As I have mentioned before, there are a lot of things you can do with a TEXT formula in excel. You just need to know about the basic feature of the formula and then you can tweak it yourself and use it in your own ways to solve problems.
So, let’s look at how to use TEXT formula one by one.
Use#1 Converting Date Into Text
Let’s say you want today’s date in a text format. So, type the following formula.
=TEXT(TODAY(),”DD/MM/YY”)
You can use any date instead of today’s date and there are other formats as well besides the format I have shown.
Few other formats of dates are shown in the table below.
| Format | Description | Example |
|---|---|---|
| DD/MM/YY | Day/Month/Year | 31/07/21 |
| MM/DD/YY | Month/Day/Year | 07/31/21 |
| DD/MM/YYYY | Day/Month/Year | 31/07/2021 |
| MMM | Short form of a month | Jul |
| MMMM | Full form of a month | July |
| YY | Last two digits of a year | 21 |
| YYYY | 4 digits of a year | 2021 |
| DD | Two-digit number with a leading zero | 01 to 31 |
| DDD | First three letters of a day | Sun |
| DDDD | Day of the week | Sunday |
Use#2 Converting Time Into Text
Let’s say you want today’s date and time in a text format. So, type the following formula.
=TEXT(NOW(),” hh mm ddd mmmm yyyy”)
Use#3 Starting a Number With a Leading Zero
In a previous blog, we talked about putting 0 in front of numbers. You can use the TEXT formula in excel to put a zero in front of numbers.
In excel, if you want to put a 0 in front of numbers excel doesn’t count it because the 0 on the left most side has no value. So, when inputting phone numbers it creates a problem.
You can use the TEXT formula in excel to eliminate this problem.
Type the formula
=TEXT(A1,”0000000000″)
The rule is to count the digits, then type zeros. The zeros will be equal to the number of digits in a number and the number of zeros you want to add in front of the number.
For example: if a number has 9 digits and you want to add one zero in front of the number, type 10 zeros.
Use#4 You Can Combine a Number and Text
Suppose you have different dates for everyone to come to meet. So, what you can do is type “Let’s meet on” and conjoin the dates. You can use the text formula here.
The formula is:
=”Let’s Meet on “&TEXT(A1,”DD MMMM”)
Conclusion
TEXT formula might not be very critical but it solves a lot of tedious problems. Now, you know how to use Text formula in Excel and solve those problems.
Hi there, I am Naimuz Saadat. I am an undergrad studying finance and banking. My academic and professional aspects have led me to revere Microsoft Excel. So, I am here to create a community that respects and loves Microsoft Excel. The community will be fun, helpful, and respectful and will nurture individuals into great excel enthusiasts.