How to Split Comma-Separated Values into Rows in Excel
Question details
The user needs to transform rows of data containing comma-separated values into a normalized dataset where each individual value is assigned to its own row.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Normalizing a dataset containing multiple identifiers (like RNTI and DCI) grouped in a single cell separated by commas.
- Observed behavior
- Multiple data points are clustered together in single cells with commas, which prevents proper sorting, filtering, and data analysis.
Format your source data as an official Excel Table by selecting the range and pressing Ctrl+T, ensuring that future data additions will be captured automatically.
Use Power Query to Split and Expand Rows
The most robust and backward-compatible method for expanding comma-separated data into individual rows without writing complex formulas.
Power Query features a built-in function specifically designed to handle delimiters and expand data vertically. This is the preferred method as it easily processes large datasets and handles adjacent columns flawlessly.
Select any cell inside your data table, navigate to the Data tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.
Click on the header of the column that contains your comma-separated values to highlight it.
Under the Home tab, click 'Split Column' and choose 'By Delimiter'. In the dialog box, ensure 'Comma' is selected as the delimiter.
Expand the 'Advanced options' section in the same dialog box, select the 'Rows' radio button under 'Split into', and click OK.
Click the 'Close & Load' button in the top-left corner to output your newly normalized, row-expanded data into a new worksheet.
Use Microsoft 365 Dynamic Array Formulas
A dynamic, formula-based approach that updates in real-time, ideal for users with access to modern Microsoft 365 functions.
A Free, Lightweight Alternative for Complex Spreadsheets
Dealing with advanced data manipulation or struggling with expensive software subscriptions? WPS Office is a highly compatible, free alternative to Microsoft Excel that supports advanced data cleaning, dynamic arrays, and seamless file compatibility.
- 1. Download and Install: Visit the official WPS website to download the free WPS Office suite and run the lightweight installer.
- 2. Open Your Excel File: Launch WPS Spreadsheet and open your existing .xlsx or .csv files containing the comma-separated data.
- 3. Process Your Data: Utilize built-in data tools and advanced formulas to manipulate, split, and analyze your datasets just as you would in Microsoft Excel.
Frequently Asked Questions
Can I split values into rows using standard Excel features without Power Query?
Yes, but it requires a manual workaround. You can use the 'Text to Columns' feature under the Data tab to split the comma-separated values into separate columns first. Then, copy those new columns, right-click a new cell, and choose 'Paste Special' > 'Transpose' to convert the columns into rows.
Why is the 'Split into Rows' option grayed out in Power Query?
The 'Split into Rows' option is only available when you are splitting by a specific delimiter. Ensure you have selected a delimiter (such as a comma or space) in the split menu. If you choose to split by the number of characters or by positions, the rows option will be disabled.
Do dynamic array formulas like TEXTSPLIT work in older Excel versions?
No, dynamic array functions such as TEXTSPLIT, TOCOL, VSTACK, and HSTACK are only available in Microsoft 365 and Excel for the Web. If you are using Excel 2019, 2016, or older, you must use Power Query or VBA to achieve the same automated row expansion.
