Hi, this is Wayne again with a topic “Use the Excel TEXT Function to Display Numbers as Words”.
In this article, we’re going to take a look at the text function in microsoft, excel and i’ll. Give you a couple of examples of how this could be really useful to you as you work in excel. So here we have an employee list and the employees are going to be getting a bonus, but there are several different payout dates for those bonuses. Now, what if i wanted to change the way that these dates display? Maybe i want the long date to be displayed. There are different ways you could do this, but let’s look at how we could use the text function to do it now, when you think about using the text, function, think about it as being a way to convert numbers into text, and it’s really great for combining Numerical information with text as well so here in i2, i’ll just type equals text left, parenthesis and now excel, is expecting a value, comma and then the format for the text.
So what’s the value? Well, it’s this. This is the date that i want to convert from a number, and yes, this kind of a date is really a number. I want to convert that into text now that i have the value i’ll put in a comma now, what format do i want this text to take? Well, there are various format codes that excel uses and, at the end of this video i’ll show you where you can go to get the format codes that you might want to use with the text function so keep watching. But in this case i just want to display the month the day, comma and then the year now, because what i’m displaying a long date is going to be in a text format, i need to put it in quotation marks so to display the whole month. I need to put four m’s on the screen, so those m’s represent month, then i’ll want a space so i’ll put in a space and then the day and a comma and then another space and then for year.
I want the full year so i’ll put in four y’s and then i’ll close with quotation marks and with the right parenthesis tap enter on the keyboard and look what it did. My text formula took the data that was in h2 and converted it into text. That’S why it’s in quotes and the text is formatted just the way i wanted it to be the full month, the day, comma space and then the full year. Now the good news is, i don’t have to repeat that process for each of these other dates. All i have to do is go to the formula that i put into cell i2 and double click on the autofill handle, and that will extend the formula down the page and it worked out great instead of double clicking. I could have clicked and dragged that’s another way to do it now.
What if i wanted there to also be a th here so august 10th. 2022.. How would i do that again? There’S a couple of ways.
I could have done that, but let’s go here to the formula bar. This represents the formula that’s in i2 and what i’ll do is i’ll just erase the year out of there and the comma i’ll put in the quotation marks and the right parenthesis and then i’ll use the ampersand symbol to combine the month and the day with. And i need to put this in quotes, th, comma space, close quote, and then i could put another ampersand to combine that with even more information, and i could even do another text function once again, i’m going to be pulling the information from the same place. H2. Comma and then in quotes, because i want this to be presented as text i could put four y’s and so that would bring in the full date. Close quote right.
Parenthesis i’ll tap enter on the keyboard. Let’S see if it worked. It now says august 10th, 2022 and of course i could double click to copy that down the spreadsheet and it worked perfectly so again.
We use the text function in this case twice within the same formula, to look at the contents of a cell that contained numbers and then to convert it into text using a format code. This format code just pulled the month and the day this format code pulled the year. Okay: let’s look at one other example of how we could use the text function, to convert numbers into text and, in this case we’re going to end up combining numbers with words with text.
We did that a little bit here when we added th as well, but let’s say i plan to use this spreadsheet to do a mail merge. If you haven’t watched my mail merge videos, you definitely need to do that. It’S a great way to send form letters out to people based on data. That’S in a spreadsheet like this one. So now that i have the bonus amount for each of these employees and the payout date, i would like to compose a message to each of the employees explaining what the bonus will be and when the payout date will be.
How would i go about doing this? Well, in j2, i’m going to type equals and i would like to start by addressing the employee so over here in a2 i have the name of my first employee, so i’ll just click there on a2. Let’S look at our formula and i’m going to go up here to the formula bar to compose this formula. It’S just easier and cleaner to work on complicated formulas up here in the formula bar, so this cell is equal to a2 gina pullen and then i’ll use the ampersand symbol to combine the contents of a2 with some text and i’ll put this in quotes because it Is text i’ll start with a comma, so gina pullen comma? Your annual bonus for this year will be and i’ll put a space there after b, close quote and another ampersand. So i want to combine that with what comes next and here’s where i’ll use a text function, text left, parenthesis, excel, expects a value and then a text format to apply to that value as it’s changed into text.
So i need the bonus amount here. It is for gina pullen, so i’ll just click there or i could type f2 if i prefer i’ll put a comma and then how do i want this to be displayed? This format, text section, is pretty much always in quotation marks, so i’ll go ahead and put quotes there and i’ll put a dollar sign and then four zeros period and then two more zeros, close quote and right parenthesis and then i’ll combine that with a period inside Of quotes, let’s try that i’ll tap enter on the keyboard, and this is the result that i have right now: gina pullen. Your annual bonus for this year will be three thousand ninety five dollars and seventy five cents.
So hopefully you can start to see how useful the text function can be. You can use it to pull numbers from one cell and convert them into formatted text and you can format it the way you want it to show up now. Of course, i could go up here to the formula bar again having selected j2 and i could put in even more information i’ll combine that by using the ampersand symbol with a space and i’ll type in you can expect your payment on, and then i put a Space after on close quote and then i’ll combine that with another ampersand, and here i will use another text function and for this example, i’m not going to use the long date, i’m going to use the numerical short date just to demonstrate that you can do it.
This way, but i’ll select that payout date as the value now, let’s put the format for the text, needs to be in quotes month. That’S 4 m’s in this case, if you do three m’s, it will abbreviate month. So we want to do four m’s, in this case space we’ll put d for the day space and then the four y’s for the year quotation marks right parenthesis and just to make it perfectly grammatically correct.
I’M going to use the ampersand symbol and put a period inside of quotation marks, and now i’m done i tap enter on the keyboard. Let’S look at the full message for gina pullen. It says gina pullin. Your annual bonus for this year will be 3095.75.
You can expect your payment on august 10, 2022. now watch what happens when i use the autofill handle i’ll. Double click on that box in the lower right corner of the cell and the formula that was in j2 is copied all the way down the spreadsheet and look how nice? That is, the person’s name changed the amount of their bonus changed and the payout date changed. This is just beautiful, so this worked out great.
But what, if you don’t know what text format you should use or what the format is for it? In this case, i had just already learned to use four, ms, the d, the four y’s, but what, if i didn’t know that or what? If i wanted to try a different text format for my value? There are a couple of ways you can get other text format codes to use. One method is to click on the number that you’ll be converting into text and then hold ctrl and tap one on the keyboard. Now that’s for windows, computers on a mac. It’S command! 1 – and that brings up this format – cells, dialog box – if i go down here to custom look, this should look somewhat familiar.
These are text formats that you can use in your own formulas, so i can use h for the hour. I can use the pound symbol in some cases, so this should give you some hints about the text format codes that you could use in your spreadsheets. Another help that you have is this website.
It’S microsoft’s support website and they have a whole page on the text. Function you’ll see here, there are some examples of formulas and text formats that you could use there’s also some tips. There’S an example spreadsheet that you can download.
That will help you learn even more, and here we have a description of the technique. I showed you just a minute ago of control 1 in order to bring up the format cells, dialog box, and then you can look at how to format based on those examples. So i hope you can see that the text function really can be a powerful tool in your toolbox, as you’re using microsoft excel. If this video proves to be popular, i’d, love to do more, excel text function, videos, but in the meantime i hope you found it to be helpful. If you did please like follow and subscribe, and when you do click the bell so you’ll be notified. When i post another video, if you’d like to support my channel, consider clicking the thanks button below the video, you can also support me through my patreon account, or by buying channel merch and you’ll, see information about those options in the description below the video .