Predictive Hacks

Power Query: A Quick Way to Split Data in Excel

excel-tutorial

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.

Power Query: A Quick Way to Split Data in Excel 1

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?

Power Query: A Quick Way to Split Data in Excel 2

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:

Power Query: A Quick Way to Split Data in Excel 3

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.

Power Query: A Quick Way to Split Data in Excel 4

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:

Power Query: A Quick Way to Split Data in Excel 5

Now delete the information you don’t care about, Close & Load. There it is: the data exactly how we wanted:

Power Query: A Quick Way to Split Data in Excel 6

Share This Post

Share on facebook
Share on linkedin
Share on twitter
Share on email

Leave a Comment

Subscribe To Our Newsletter

Get updates and learn from the best

More To Explore

snowflake
Miscellaneous

How to Schedule Tasks in Snowflake

We have started a series of Snowflake tutorials, like How to Get Data from Snowflake using Python, How to Load