How to Split Comma-Separated Data Using Power Query in Excel
Question details
The user needs a method to transform comma-separated purchase data into separate, analyzable records for filtering, with a process that can be automated on a monthly basis.
- Product
- Excel Power Query
- Device & OS
- not provided
- Scenario
- Analyzing monthly secondary purchases by ticket type where multiple purchased items are combined into a single text string separated by commas.
- Observed behavior
- The purchase data is currently trapped within single cells as comma-separated text, making it impossible to filter, summarize, or analyze individual ticket types easily.
Ensure your source data is formatted as an Excel Table (by pressing Ctrl+T). This allows Power Query to dynamically capture new rows of data when you update the file each month.
Split Comma-Separated Values into Rows Using Power Query
This is the recommended approach for data analysis, as splitting into rows converts your data into a normalized tabular format perfect for PivotTables and filtering.
By splitting data into rows instead of columns, each purchased item gets its own standalone record while retaining the associated customer and ticket information. This prevents your spreadsheet from growing endlessly wide and makes summarizing data much simpler.
Select any cell inside your data table. Go to the 'Data' tab on the Excel ribbon and click 'From Table/Range'. This will open the Power Query Editor.
Click on the header of the column that contains your comma-separated purchase data to highlight it.
On the 'Home' tab of the Power Query Editor, click 'Split Column' and select 'By Delimiter' from the dropdown menu.
In the dialog box, choose 'Comma' as your delimiter. Expand the 'Advanced options' section, select the 'Rows' radio button under 'Split into', and click 'OK'.
Right-click the newly split column header, navigate to 'Transform', and click 'Trim'. This removes any accidental leading or trailing spaces left over from the commas.
Click the 'Close & Load' button in the top-left corner to export your cleaned, row-based data into a new Excel worksheet.
Split Comma-Separated Values into Columns
Use this method if you specifically need a wide format where the first item is in Column A, the second in Column B, and so on.
Split Comma-Separated Data Quickly in WPS Spreadsheet
If you do not need complex automated query workflows, WPS Office provides an incredibly fast 'Text to Columns' wizard. It instantly separates delimited data without needing to open a separate query editor.
- 1. Select your data: Highlight the specific column in WPS Spreadsheet containing the comma-separated purchases.
- 2. Open Text to Columns: Navigate to the 'Data' tab and click on the 'Text to Columns' icon.
- 3. Choose Delimited: Select 'Delimited' in the wizard step 1, then click 'Next'.
- 4. Apply comma delimiter: Check the box next to 'Comma'. The data preview will update to show the split columns. Click 'Finish' to apply.
Frequently Asked Questions
Why are there extra spaces before my text after splitting data by a comma?
When lists are typed as 'Item A, Item B', splitting strictly by the comma leaves the space before 'Item B' intact. To fix this in Power Query, right-click the split column, select 'Transform', and then choose 'Trim' to remove those extra leading spaces.
Should I split my data into rows or columns for filtering?
For filtering and reporting (especially using PivotTables), you should split into rows. This creates a standard database layout where each purchase is its own row, making it incredibly easy to filter by ticket type or summarize counts.
Will my Power Query automatically update when I add new monthly data?
Yes. As long as you add your new monthly data into the original Excel Table that feeds the query, you can simply go to the 'Data' tab and click 'Refresh All'. Power Query will automatically run all your splitting and trimming steps on the new data.
Can I split data if the text uses different separators like semicolons or slashes?
Yes. In the 'Split Column by Delimiter' menu in Power Query, you can select 'Custom' from the delimiter dropdown and manually type the character (like a semicolon or pipe symbol) that separates your data.
