Combine numbers and words in Excel 2013 using the VLOOKUP Function
For those who have used Excel to create invoices, the balance of text and numerical data inside of a workbook can be tricky. On the one hand, you want data to appear on an invoice along with the term that defines it. On the other hand, it is useful to be able to quickly assign a formula to calculate fields of information, and text can just get in the way.
Excel 2013 enables the combination of text and numerical data, making spreadsheets both easy to read and easy to calculate.
Watch the free video here, transcripts for the entire video follow:
Master Excel 2013 – the “Must Know” program. Get 19 hours of Excel 2013 training – click here.
Video transcripts:
Hello again and welcome back to our course on Excel 2013. We’re currently looking at the use of functions in Excel 2013 and we’re looking at the use of functions to improve the generation of invoices for this plumbing company. Now in the previous section, we used the VLOOKUP function to use what was effectively a customer database on the Customers Sheet here. So let’s just take another look at the Customers Sheet and what I’m going to do now is to make a change to this database. You’ll very often be in the position that you need to make changes, and the change that I’m going to make here is, first of all, I’m going to change the terms column. Currently the terms say things like 7 days, 30 days, 30 days, 60 days. I’m going to change the way that that column works by just specifying only the number of days and not say 7 days, 30 days, 30 days, and so on. So for instance, in that one I’m going to change that. I’m just going to delete the word Days. I’ll delete the rest of those and then rejoin you.
Now, first of all, why have I done that? Well, the reason I’ve done that is that I want to be able to calculate when an invoice is due. And the way I’m going to calculate it is by adding the number of days in the terms of business for the individual company that I’m dealing with to the date that the invoice was generated. So for instance to find when an invoice to Frequent Showers is due, I would add 7 days to the invoice date. Now, of course, that will have a consequential problem. If I go back to the invoice itself where it says Terms, it will now just say 7 instead of 7 days. And what I’m going to do now is show you the use of what are called Text Functions to put text or extract text or change text in any field.
Now I should point out here as I have done from time to time that there a few ways of doing this and I’m only showing you one of the available ways of doing it. But if you select the cell, in this case the one that should say 7 days and just says 7, we’ve still got the formula in there that we put in before with the VLOOKUP function. What I really want to do is to sort of attach the word Days to the end of that in some way. Now one of the ways of doing it is to use one of the text functions. And within the Function Library on the Formulas tab, text functions have their own little category. There are many of them. And I’m going to use the one that’s called Concatenate and concatenate joins several text strings into one text string. Now all I need to do to use concatenate is to put brackets after it and then to put the text strings I want joined together in the brackets separated by commas. Now this is where sometimes people start getting a bit confused because you see an awful lot of brackets appearing. So I’m going to take this step by step. First of all, equals in the formula bar says this is a formula. And the main part of the formula now is concatenate. Now note that I could have used that from the drop down and it would’ve put in if you like the bones of the function for me and let me put in the rest of it myself. But I’m typing it in directly as concatenate, then I’m opening brackets, and it will tell me now that let’s have the first bit of text. Now my first bit of text is what that VLOOKUP does because what that VLOOKUP does is to give me the number of days. So if I just had that, I’d get 7 or 30 or 60. But I don’t just want that. I want another piece of text as well. Now the other piece of text is always the same and it’s almost this. If you want to specifically put a piece of text in put double quotes and then put the word, in this case Days because I want it to say 7 days or 15 days or 30 days. One slight problem there though is I also want a space before the word Days. So it will 7, space, Days or 30, space, Days, etc. Now when I’ve put in all the strings that I want I put in, close the round bracket, and I will enter that as my formula. And now look what happens, it again says 7 days. And if I change, let’s try a different account, 2973, tick that. It says 30 days. So that’s great.
So I mentioned just now that the idea of introducing the number of days rather than the whole phrase was to enable me to put a due date on to an invoice and that’s what I’m going to do in the next section. I’ll see you then.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
