Welcome to our first week. My name is Prashan and with me today is Nicky. So Nicky, in our business example, which is our case study, we need to help Zara from accounts address the issues in her data, usually the first steps in data analysis as you prepare the data. So I assume that is what we are going to be starting with this week. Yes, Prashan. But we actually have two objectives this week. The first thing we want to do is actually develop techniques for working confidently with Excel functions. That will help us unlock the full power of Excel. Alongside that, we're going to cover a comprehensive range of functions, the manipulating and cleaning text data. This is really important because we can't probably analyze data if it's invalid or advanced suitable format. Absolutely. Can you give us some examples of the types of text functions we'll be looking at and why we would use them? Yeah, of course. So one common problem, for example, is that we may need to join texts together. For example, let's say you want to do a lookup on somebody's full name and you have the first name and you have the surname, but they're in separate columns, making impossible. We're going to look at some functions like CONCATENATE and TEXTJOIN that will help us join text together and solve this problem. I've also encountered the opposite problem where you have two pieces of information in the same column and you need to separate them. Yeah and that's exactly what we're going to look at in our second topic, where we are going to introduce the functions LEFT, RIGHT and MID, which allow us to split data. Sounds great. But just wondering for the separating data bit, can't we just use the built-in text to columns tool on the data ribbon? Look, text to columns is a great tool for the occasional one off, but it actually has two downsides. First of all, you're going to have to redo it every time you import your data. The other problem is you lose your original values. Our long-term strategy is to build a workbook which is totally automated and can be reused every month or every week. All you have to do is put the data in and Excel does all the rest. To achieve this, we're going to need Excel functions. I'm convinced. So tell us more about cleaning data please. I've had some real issues with spaces because a lot of people automatically type a space at the end of a word. But Excel doesn't just ignore these spaces and so when I summarize the data, I end up with one total for Sydney and one total for Sydney with a space. I know exactly what you mean. But the good news is I have a miracle cure called TRIM, which will rid you of all those problematic spaces. We're also going to look at a function called CLEAN, which cleans up a lot of other weird characters that look like spaces but are actually non-printing characters. So does CLEAN clean up all the non-printing characters? ACtually that's a common misconception. It only cleans the first 32 non-printing ASCII characters. But don't worry, we're also going to look at a function called SUBSTITUTE that will help us get rid of the others. Now you mentioned ASCII characters. For those who haven't heard this term before, could you please explain what they are quickly? So ASCII is a computer encoding system where every character that we see on the screen is actually given a numeric code from naught to 255. So for example, the ASCII code for space is actually the number 32, whereas a capital A is 65. Now it's not really important to know these, but we have included an ASCII table in the practice book for your reference. Thanks Nicki. I am really excited to get started. But we're covering so much content, how are our learners going to remember it all? Well, that's the best news. I mean, they don't actually have to remember the detail of every single function as long as they understand the principle of how the functions work. We have a full function library on the Formulas ribbon. Excel's help is excellent if you need to check how our function works and the functions themselves actually guide you with prompts. We've also provided a fabulous quick reference guide in the toolbox which summarizes all the functions we're covering this week. It also has some useful keyboard shortcuts and even some bonus content for those who'd like to dive a little deeper. So to summarize, these are the topics that we'll be covering this week, functions for combining text, functions for splitting text, combining text functions, cleaning data and changing case, removing and replacing unwanted characters. Each topic has a great training video for you to watch and you will get a lot more out of them if you follow along in the practice workbooks provided. There is a workbook for each video, but you can keep working in the same workbook from the previous exercise as long as you've completed all the activities. After each video, there is a quiz for you to test your knowledge and at the end of the week, we have a practice challenge for you to practice all the amazing new skills that you have learned. Now it's over to you.