This is the first of two posts showing how to split and combine rows from/to delimited cells. In days gone by, it would take a long time for us to resolve these scenarios. But with Power Query, it’s now a quick and easy process, which can be updated with a simple refresh.
In this first part, we’ll look at how to split delimited cells into rows with Power Query.
Download the example file
I recommend you download the example file for this post. Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.
Download the file: 0023 Split delimited cells into rows.zip
Watch the video:
Let’s start by looking at our data scenario.
The data contains a list of employees, along with any equipment that has been allocated to them by the company. Looking at Anna Watkins in row 3, we can see that she has a laptop, access card, phone, and home printer. Each piece of equipment is separated by a semi-colon and a space.
The attributes of each item are also recorded. For example, Anna Watkins’ laptop has an ID number of ID0011, and an access card with the number 231, etc. The item is separated from its attribute with a colon and as space.
While the format is useful for quickly seeing all employees, it’s not ideal if we want to know who has a specific item. For example, who has a company credit card? Or who has which phone number?
The following format is more usable from a data manipulation perspective.
If we had this format, we could easily filter the Equipment column and identify the individuals with a company credit card, or even create an employee telephone list.
We’re going to use Power Query to quickly convert our source data into this format.
While Power Query is the best option for this type data manipulation, other methods exist for splitting cells, which you can read about here: https://exceloffthegrid.com/how-to-split-cells-in-excel/
Load data into Power Query
To start, we need to get the data in Power Query. Select any cell within the table, then click Data -> From Table / Range.
The Power Query Editor will open, showing the data from the table.
OK, we’re now ready for the magic to begin:
- We’ll split the Equipment column into rows, using the semi-colon and space to determine where to split.
- Next, we’ll split by a colon and space to create separate columns for equipment and its attribute.
Split into rows
OK, let’s split the Equipment column into rows.
- Select the Equipment column by clicking on the column header.
- Click Home -> Split Column (drop-down) -> By Delimiter.
- In the example we’re working through, each piece of equipment is separated by a semi-colon and a space. This is not in the default delimiter list, so we need to select Custom from the drop-down, then in the box below, enter a semi-colon ( ; ) followed by a space. Choose the option to split at each occurrence of the delimiter.
- Expand the available options by clicking Advanced options, then select the Rows.
- Click OK to execute the transformation.
The data now looks like this:
We’ve managed to split the data into individual rows. The individual’s name exists multiple times, as it is against each piece of allocated equipment. In many circumstances this will be all that’s required. However, for our scenario, we’ve got an extra step.
Split a column into columns
Now it’s time to separate the equipment from its attribute.
As before, click Home -> Split Column (drop-down) -> By Delimiter to open the Split Column by Delimiter dialog box.
Instead of splitting into rows, we can split into columns. Our delimiter is a colon ( : ), followed by a space.
Click OK to split the Equipment column.
Rename the columns
Finally, rename the columns, so they have useful names; double-click on each column header and enter the name for the column (I’ve gone with Equipment and Equipment Attribute).
The preview window now looks like this.
Load data into Excel
Everything is complete, so we are ready to push the table back into Excel. Click Home -> Close & Load (drop-down) -> Close and Load To…
The Power Query Editor will close, and the view returns to Excel. In the Import Data dialog box, select to load a table into the existing worksheet and select cell E1.
Click OK to close the Import Data dialog box.
The final data is now in Excel.
If there are any changes to the original data, we can simply right-click on the table and select Refresh from the menu.
Power Query is an amazing tool. Splitting cells just isn’t available using the regular Excel interface. We would have to use a macro, or some extraordinarily complex formulas. However, with Power Query, we can split delimited cells with just a few clicks.
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you're still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: