Splitting columns with Power Query - Microsoft Excel Tutorial

- A lot of times we have to split data apart. In this situation, in column B we have names and emails that are all in the same cell. And look at the first one. We have Paul Maddocks, PhD and then Paul's email address, PhDPaul@99MothLabs.org. Then there's Fayette Sandoval and the email address, Fay@Pizza5988.com. And then over in column C, we would like to split out the double E in the first entry. We want to split out the RT and then the LLL etc., on down the column. This is a mess to do in native Excel but there is text-to-columns but text-to-columns would create a mess for us, so we're not even going to look at that right now. We're going straight into power query. The cursor is in a data set I'm going to right click get data from table/range. Okay. Here is how we can split out the email addresses first. The column is highlighted, split column. Now look at all of these options that we have: by delimiter, by positions, by digit to non digit all of these. Make up some data play with these. You got to get to know them, I'm going to split by delimeter. I'm going to split by yes, custom, but not the @ symbol. I want to split by comma space. Now I have options Left most, Right most, each occurrence of the delivery. I don't want each occurrence. I want the Right most and I split by comma space so that I don't have a space to fix later. Okay. Look at that. Now I can go and rename this email. Good. And then I'm not going to mess with this yet because I need to split this now. Highlight, right-click. Here's another way to get at it: split column by delimiter. Now I need to be careful because Paul Maddocks has the professional designation of PhD. So does Peyton Dunn and Bernita Poole is a CPA. So we got to be careful. Now to split the name out. Okay. I'm going to to do comma space again, Right most, Ok. Now I have the first names peeled out, first name and then last name. Good. Next to split out the ID because we want to get the EEY, the RT, LLL etc. And notice what makes this tricky is some of these IDs have two letters, some have three letters and then you go down to row 11. There are only two digits before the three letters that we want to extract. Here's what I'm going to do. I'm going to right-click and duplicate the column because I want to keep the ID intact but I do want to split these letters out. With that new column, highlighted, split column, I want to split it where a digit shifts to a non-digit. So digit to non-digit. Look at that. Now I'm going to get rid of this, remove it. And then with this new column highlighted, split column non-digit to digit, slide over, highlight, right-click, remove. We've got our data peeled down. Now there's one other thing that I will show you. I'm going to to add a column and see if I can extract the email domains. Okay? And I'm going to use column from example. I'm going to highlight that column, column from example from selection. Now, slide this over so that I can see. Let's see, I'm entering this 99mothlabs, enter. Wow, let's see. Let's check it out for Leanne, We have PLFSwift. That's right. Pizza5988 for Fay. That's right. CrimsonForay99, SummerWhooshApartments, PLFSwift. You always have to check these things that are based on machine learning. And also look at the formula. Power query has created a step force that is text dot between delimiters, email column, and split between the @ symbol and the period. So that's what we needed. And that's fine for this situation. Column from example helped us split out the domains. Now we can click, Ok. Call this Domains. Good. We've split the data down. So many beautiful ways! File, Closing load to, existing worksheet. I am going to put this data right here. Okay. And there it is. All split out and power query is telling us 13 rolls loaded. Now the data is all split out and useful because power query gave us so many options for splitting the columns.