Excel offers multiple ways to split data in a cell. Among these are the venerable Text to Columns tool, Flash Fill, and writing formulas that incorporate Excel functions such as LEFT, MID, RIGHT and LEN. However, the issue with Text to Columns and Flash Fill is that you must re-run these tools when new data is added – hardly a model of efficiency! As for the for the function-based approach, the formulas you create can be tricky, depending upon the data you need to manipulate. In this tip, you will learn how you can use Power Query to split data into multiple columns.
How to Create the Transformation
With the data in the table shown in Figure 1, we will use Power Query to split the City, State, and Zip Code into separate columns.
The steps to complete the process are as follows.
- Select a cell in the table of data and click From Table/Range on the Data tab of the Ribbon to open the Power Query Editor.
- Click Split Column on the Home tab of the Ribbon and select By Delimiter. For the first instance, use Comma as the delimiting character and choose Each occurrence of the delimiter as shown in Figure 2. Then, click OK to complete the process for that column.
- Repeat the procedure outlined in the previous step on the newly created column. However, in this instance, select Space as the delimiting character to split the State and Zip code.
- Select the blank column and click Remove Columns on the Home tab of the Ribbon.
- Double click each header and rename them to City, State, and Zip.
- Select the Zip column and, on the Home tab of the Ribbon using the Data Type drop-down, select Text.
- Click Close & Load to return the data as a table to Excel.
Summary
These are all the steps necessary for you to use Power Query to split data into multiple columns. One very clear advantage to this approach is that you do not have to write any formulas to manipulate your data. A second – and potentially more important – advantage is that you now have a repeatable, refreshable process. Whenever your data changes, just right-click on the query results table and click Refresh and you will see that Power Query immediately transforms the data. It really couldn’t be any easier!
For more information on Power Query, click here to visit Microsoft’s website on this topic.
To learn more about Power Query from K2 Enterprises, consider participating in the following courses.
