Sometimes we get data that’s in a format that’s not useful. How do we break this data up so that its’ useful and actionable? There is a simple and efficient way to do it in Excel: Power Query.
Let’s look at this example above. We’ve got first and last names together, as well as email, location, and age, but we only need last and first name and email. We don’t care about the other information. So, how are we going to dig this out?
First, after selecting the range of data, you go to Data > From Table/Range (see above).
A popup window will open. You have already selected the data source, make sure you click on My table has headers too. The Power Query Editor page will open, and here’s the data:
Select the column you want to split and click on Split Column right above. Now select By delimiter. Choose Custom because your data (Last Name, First Name) has got a space and a comma.
Type in a comma and space as shown in the image below. Click on OK and… the data is split! If you want you can rename the columns by double-clicking on them.
Now we have to separate the email from everything else. Highlight the column. Again, Split Column by delimiter. We want to split by Space, the Left-most space. Here we go:
Now delete the information you don’t care about, Close & Load. There it is: the data exactly how we wanted:
