Oz du Soleil shows how to split columns of data when—in this example—name, age, and email address are all in the same cell. You do so using the Split Column by Delimiter function.
- [Narrator] Alright, now we're about to do some good stuff here. We're going to split by delimiter, and if you want to follow along, pull up the exercise file called Split by Delimiter. Check out this data. We get data from a lot of places, and it can look all kind of strange ways including this. We've got names, ages and emails. But also notice, like Margo Jeffries, most people have their name and age separated, like Tempest Benoit, 50 years old.
But now Margo Jeffries, PhD, JD, 51 years old. We need to separate the names from the ages and keep those professional designations and like Cyrus La Croix III, we want to keep that as well. To do this in native Excel, it is messy and there are a lot of hacky workarounds. We can do this easily in Get & Transform, now let's do that.
We're inside the data set, Data, start the query From Table. Here is our data. Let's start by addressing the three asterisks between the age and the email address. The column is already highlighted, Split Column, By Delimiter. Now check this out. It's defaulted to Custom, but notice, we have all of these other choices.
Let's leave it at Custom and put in three asterisks, it's already got one, two, three. Oh, look at that. Cleanly separated. Now let's get the ages separated from the names and the professional designation. The column is still highlighted, let's go to Split Column, By Delimiter. It's guessing at space, but no, we don't want to separate there, we want to separate by the comma, most specifically, we want to split by the rightmost comma.
See, look, Donovan Gray, MSW, 38 years old. We only want to split by that rightmost comma. Let's do it. Look at that. Let's close and load it to the workbook. And this is exactly what we wanted. Except let's go ahead and clean this up a bit, let's go back into the query, Edit to query. Rename this Name, Age, Email.
Close and Load. And here we are. We have exactly what we wanted. Everything's separated by using Split by Delimiter.
- What is Get & Transform?
- Querying data
- Working with columns
- Using formulas
- Pivoting and unpivoting data
- Grouping data
- Appending a query
- Merging/combining data with joins