Predictive Hacks

Power Query: A Quick Way to Split Data in Excel


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:

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


Image Captioning with HuggingFace

Image captioning with AI is a fascinating application of artificial intelligence (AI) that involves generating textual descriptions for images automatically.


Intro to Chatbots with HuggingFace

In this tutorial, we will show you how to use the Transformers library from HuggingFace to build chatbot pipelines. Let’s