Power Query is a data management tool in Excel. In this guide, we're going to show you how to split text with Power Query in Excel.
What is Power Query
Power Query is a feature of Excel that makes access to data easier and manageable. If you have Excel 2016 or newer, you can find Power Query tools under the Data tab. Excel 2010 or Excel 2013 users should download as add-in.
To learn more about Power Query check out Power Query 101.
Data
In the example below, we have data separated by “|” characters. Each part represents an individual column.
Splitting text with Power Query
- Start by accessing your text data with Power Query. Select the tool that match your data. For example, we are using From Table/Range button because our data resides in same workbook we are working. Choose From Text/CSV if yours is in a text file.
- If the data is not in an Excel Table, Excel converts it into an Excel Table first.
- Once the Power Query window is open, make sure the column containing text is selected.
- Find the Split Column under the Transform tab and click to see the options.
- Select the approach that fits your data layout. The data in our example is suitable for By Delimiter since the data is separated by “|”.
- Power Query will show the delimiter character. If you are not seeing the expected delimiter, choose from the list, or enter it yourself.
- You can also split the data into two pieces, by the left-most or right-most delimiter.
- Click OK when you are done.
After clicking OK, you will see that your strings are split into columns. From this point you can work on data as a regular database table.
You can use the Close & Load button to move split data into your workbook.
By default, Excel creates a new worksheet and inserts the data into a table. You can move this table anywhere afterwards. The split data will be connected to the original data source. If the source is updated, you can update the split data by clicking the Refresh button under the Data tab of the Ribbon.
