🔥Get 20+ Excel courses at Simon Sez IT, including training for Excel 365, 2021, 2019, 2016, 2013, and more ➡️ [ Ссылка ] ✅ Get a free 3 hour Excel 2019 course from Simon Sez IT ➡️ [ Ссылка ]
During this Excel 2013 tutorial, learn how to use text function and with other functions in Excel 2013.
Get the full course on Excel 2013 course here: [ Ссылка ]
Watch the Excel 2013 playlist: [ Ссылка ]
Stay in touch:
SimonSezIT.com: [ Ссылка ]
The Simon Sez IT email newsletter: [ Ссылка ]
YouTube Channel: [ Ссылка ]
Facebook: [ Ссылка ]
Twitter: [ Ссылка ]
Google+: [ Ссылка ]
If you enjoyed the video, please give a "thumbs up" and subscribe to the channel -)
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.
Sorry, we couldn't fit the entire video transcription here since YouTube only allows 5000 characters.
