Key takeaways: Learn how to use Power Query’s row and column separator features. Also, see how to trim gaps and replace values.
Skill Level: Medium
Table of Contents
- 1 Convert email addresses into an organized list
- 2 Use Power Query to create a table
- 3 Clean up data
- 4 Export Query to Excel Table
- 5 A fully automated process
Convert email addresses into an organized list
Let’s say you’ve just joined a new group (distribution list) and you want to create a list or directory of members’ contact information. The data you have to start your list is from a group email you received.(Read more : compare two columns in Excel)
You can take a string of names and emails in the “To” field of an email and quickly turn them into an organized list using Power Query.
Use Power Query to create a table
The first step in data transformation is just copying the entire block or string of names/emails. Then paste them into a cell on the Excel sheet.
With a cell selected, open Power Query Editor. You can do that by clicking From Table/Range on the Data tab of the Ribbon.
That will bring up the Create Table window. Verify the cell you are pulling data from and that the checkbox for “ My table has headers ” is NOT selected. When you click OK, the Power Query Editor opens.(Read more : Code to count views)
1. Split the delimiter into rows
The first thing we’re going to do in the editor has split the column. On the Home tab of the Ribbon, go to the Split Column menu and select By Delimiter.
A delimiter is a character, symbol, or space that indicates the beginning or end of a data item. In this case, your email entries are separated from each other by a semicolon. So when the Split Column by Delimiter window appears, select Semicolon (semicolon) from the delimiters drop-down menu.
In the same window, under Advanced Options, select the Rows option to split delimiters into rows. Otherwise, each email item will be listed in its own column, and this is not helpful.
When you OK , the data series is split so that each row containing the previous data is between semicolons.
This is a great and somewhat hidden feature of Power Query! And it still works the same if the data table has multiple columns but you are only splitting a single column.
2. Split the delimiter into columns
The next step is to separate the name from the email address. What notation separates these pieces of data? The less than (<) symbol . Therefore, we will repeat the process separated by a delimiter, but this time will choose symbols smaller, can be entered when you select Custom for your delimiter. This time do not select Advanced Option for Rows.(Read more : create a Pivot Table)
This will be the data after execution.
Clean up data
Next, we do have some data cleanup work to do.
1. Remove Gaps with Trim
You will notice that in the first column, there is an extra space before the name. To get rid of that white space, just right-click on the column header and choose Transform, then Trim.
2. Remove extra characters with replacing the value
In the second column, to remove the greater than symbol (>) following each email address, right-click the column header, select Replace Values, and then replace the > sign with nothing. This will remove all those marks.
And finally, want to add column headers, double-click the header for each column and enter a new name . (In this case, Column 1 becomes Name and Column 2 becomes Email .)
3. Rename columns
Finally, we can double-click each column header and give the columns descriptive names.
Export Query to 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 sheet.
Your new worksheet lists each name and email address in separate cells of an organized table.
Using Power Query, we split the data using delimiters, first into rows so that each email/name has its own entry, then by column so that the name is separate from the email address.
A fully automated process
Since we built this process in Power Query, it can be reused. Any new incoming email lists can be quickly cleaned up now that the setup is done.(Read more :Excel Charts and Graphs)
To do that, you can just paste the new list into the cell with the original list and run the query again, it’s really simple. Just right-click anywhere on the output panel and select Refresh.
Or if you’re looking to add new entries to your existing output table, you can simply paste the data into the next blank cell in the source column. In the image below pasted the first email list into A2 and the second list into A3.
This setup will append the second list to the first list when you refresh the output table. Concatenation means stacking data; the second list will be placed below the first list in the output table.