Bottom Line: Learn how to use the Split by Delimiter into Rows and Columns features of Power Query. Also see how to trim blank spaces and replace values.
Skill Level: Intermediate
Watch the Tutorial
Download the Excel File
Follow along or practice with the same Excel workbook that I use in the video. Download it here:
Convert a Block of Email Addresses to an Organized List
Let's say you've just joined a new group (distribution list) and you want to make a list or directory of the members' contact information. The data that you have to start your list is from a group email that you received.
You can take the string of names and emails in the “To” field of your email and quickly turn them into an organized list using Power Query. I'll show you how to turn the data on the right into a table like the one on the left.
Using Power Query to Create a Table
The first step in transforming our data is just to copy the entire block, or string, of names/emails. Then paste them into one cell on an Excel worksheet.
With that one cell selected, open up the Power Query Editor. You can do that by clicking on From Table/Range on the Data tab of the Ribbon.
That will bring up the Create Table window. Verify the cell that you are pulling the data from, and that the checkbox for “My table has headers” is NOT checked. When you click OK, the Power Query Editor will open.
1. Split by Delimiter into Rows
The first thing we are going to do in the editor is split our column. On the Home tab of the Ribbon, go to the Split Column menu and choose By Delimiter.
A delimiter is a character, symbol, or space that indicates the beginning or end of a data item. In our case, our email entries are separated from each other by a semicolon. So when the Split Column by Delimiter window appears, select Semicolon from the dropdown menu of delimiters.
In the same window, under Advanced Options, select the option for splitting into Rows. Otherwise each of our email entries will be listed in its own column, which isn't helpful for us.
When you kit OK, your data string will be split so that each row contains the data that was formerly between semicolons.
This is an awesome and somewhat hidden feature of Power Query! And it still works the same if your data table has multiple columns but you are only splitting a single column.
2. Split by Delimiter into Columns
Our next step is to separate the names from the email addresses. What symbol separates these pieces of data? The less than symbol (<). So we will repeat the process of splitting by delimiter, but this time we will choose the less than symbol, which can be typed in when you choose the Custom option for your delimiter. Don't select the Advanced Option for Rows this time. We want our data to be split into columns.
Here's what the data looks like now.
Cleaning Up the Data
Next we have a bit of data cleansing work to do.
1. Remove Blank Spaces with Trim
You'll notice that in the first column, there are extra spaces before the names. To get rid of those, just right-click on the column header and select Transform, then Trim.
2. Remove Extra Characters with Replace Values
In the second column, we want to get rid of the greater than symbols (>) that follow each email address. To do that, right-click on the column header, select Replace Values, and then replace the > with nothing. This will remove all of those marks.
And finally, we want to add column header titles. To do that, double-click the header for each column and type in the new name. (In our case, Column 1 becomes Name and Column 2 becomes Email.)
3. Rename the Columns
Finally, we can double-click each column header and give the columns descriptive names.
Output the Query to an Excel Table
Now click on the top half of the Close & Load button. This exports the data out of Power Query into its own new worksheet.
Your new worksheet lists each of the names and email addresses in their own cells of an organized table.
Using Power Query, we've split the data using delimiters, first into rows so each email/name has its own entry, then by columns so the name is separated from the email address.
A Fully Automated Process
Because we built this process in Power Query, we are able to reuse it. Any new list of emails that comes in can be cleaned up quickly now that we've done the work of setting it up.
To do that, you can just paste the new list into the cell that had the original list and rerun the query, which is really simple to do. just right-click anywhere on the output table and choose Refresh.
Or if you are looking to add new entries to your existing output table, you can simply paste the data into the next empty cell in the source column. In the image below, I've pasted the first list of emails into A2 and the second list into A3.
This set-up will append the second list to the first list when you refresh the output table. Append means stacking the data; the second list will be put below the first list in the output table.
Related Tutorials
If you'd like to learn more about Power Query, I invite you to check out these two related posts:
Power Query Overview: An Introduction to Excel’s Most Powerful Data Tool
How to Combine Tables with Power Query
Free Training Webinar on the Power Tools
Right now I'm running a free training webinar on all of the Power Tools in Excel. This includes Power Query, Power Pivot, Power BI, pivot tables, macros & VBA, and more.
It's called The Modern Excel Blueprint. During the webinar I explain what these tools are and how they can fit into your workflow.
You will also learn how to become the Excel Hero of your organization, that go-to gal or guy that everyone relies on for Excel help and fun projects.
The webinar is running at multiple days and times. Please click the link below to get registered and save your seat.
Click Here to Register for the Free Webinar
Conclusion
Power Query is a cool way to automate this process of changing a string of data to an organized table using delimiters. I hope this post is helpful for you. If you have questions or comments, I encourage you to leave them in the comments section. See you next time!
what about a file where some emails use < > and others use ()
Great question, Joyce! There are a few ways to go about it. One relatively easy way is to replace the open parenthesis ( with the less than symbol < BEFORE doing the split to columns step. This way all of the delimiters will be the same character. You can then take an additional step to replace the close parenthesis ) with a blank before/after replacing the greater than symbol >.
The nice part about this is that you could leave the query this way even if the string does not contain the parentheses sometimes. The query will still work and you won’t need to modify the query every time the delimiters change.
I hope that helps. Thanks again and have a nice day!
Hi Jon,
What I am hearing is that you can add a step to include changing the the open parenthesis to the less than and if there are no open parenthesis the process will just continue without an error. Am I correct?
I would like to add, awesome videos, thank you.
what if you email addresses do not all have around them? How do you split them into columns?
Very useful
Amazing!
Very useful. Thanks
what does it mean when you only see two options; “Delimiter” and “number of columns”? Unlike in your screenshot that shows seven options
This is exactly what I am trying to do, (split the value of a cell with delimiter & transpose it to somewhere else in the same sheet), but I do not have power query, so wondering if there is a way to do this with VBA?.
I have managed to get the cell to split, but can’t find a way to transpose the date it just goes to in a row.
Are you able to help (not sure if you have a video for it)
I am following the same steps. The data is getting split into rows except 1 cell. Can anyone help what is wrong with the particular cell.
Split by Delimiter into Rows is exactly what I needed. Thank you for covering the option.