Split by Delimiter into Rows (and Columns) with Power Query

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

Watch on YouTube & Subscribe to our Channel

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.

Convert String of Names and Addresses into Formatted List

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.

Copy the string of data into a single cell

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.

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.

Split Column by Delimiter Option on the Home Tabe of Power Query

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.

Split Column by Delimiter Window with Advanced Options

When you kit OK, your data string will be split so that each row contains the data that was formerly between semicolons.

Power Query spilt by rows using delimiter

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.

Split Column by Delimiter Window Custom Symbol

Here's what the data looks like now.

Power Query spilt by columns using delimiter

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.

Power Query Close & Load Button

Your new worksheet lists each of the names and email addresses in their own cells of an organized table.

Delimited output 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.

Right click and refresh the table for power query to rerun

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.

Append entries to output table by adding data to source column Power Query

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.

Modern Excel Blueprint Training Webinar Excel Campus Jon Acampora

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!

11 comments

Your email address will not be published. Required fields are marked *

    • 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 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.

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter

Free Excel Training Webinar Modern Power Tools