Split Comma-Separated Names into Rows in Excel
Question details
Extract comma-separated names from multiple cells into a single vertical list that can be sorted alphabetically.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Several cells contain multiple names separated by commas, and the user wants one name per row.
- Observed behavior
- The names are grouped within cells rather than listed individually for sorting.
Check that commas separate the names and select only the cells containing the source data. For Excel 2013, use the VBA method below; the built-in Power Query steps apply to Excel 2016 or later.
Split names into rows with Power Query in Excel 2016 or later
Use Power Query to turn a column of comma-separated names into a refreshable list.
Select the source column and press Ctrl+T. Confirm whether the selection has a header, then select OK.
Select a cell in the table and choose Data > From Table/Range, or Data > From Table in Excel 2016.
In Power Query Editor, select the names column and choose Split Column > By Delimiter. Select Comma, expand Advanced options, choose Rows, and select OK.
With the names column selected, choose Transform > Format > Trim to remove surrounding spaces. Optionally choose Transform > Format > Clean to remove nonprinting characters, then select Home > Close & Load.
In the loaded table, open the names column's filter menu and select Sort A to Z.

Extract names with a VBA macro in Excel 2013
Use this method when the names are in multiple selected cells and the built-in Power Query workflow is unavailable.
Create a vertical name list in WPS Spreadsheet
Use Text to Columns and Paste Special in WPS Spreadsheet to split each comma-separated cell into individual rows, then sort the combined list.
- 1. Open the workbook: Open the Excel workbook in WPS Spreadsheet and create a blank worksheet for the name list.
- 2. Split one source cell: Copy one cell of names to an empty area, leaving blank cells to its right. Select the copied cell and choose Data > Text to Columns. Select Delimited, choose Comma, and finish the wizard.
- 3. Transpose and repeat: Copy the split cells, select the first empty cell in the output column, and use Paste Special > Transpose. Repeat the split and transpose for each source cell, appending names below the existing list.
- 4. Sort the names: Select the completed list and choose Data > Sort Ascending. Check for and remove any unwanted leading spaces before sorting.

Frequently Asked Questions
Can I split names into rows in Excel 2013?
Yes. Select the source cells and run the VBA macro above using Alt+F8. It creates a new worksheet with one nonblank name per row.
How do I remove spaces after splitting names in Power Query?
Select the names column in Power Query Editor and choose Transform > Format > Trim. Use Clean as well if the text contains nonprinting characters.
Will the Power Query list update when the source names change?
Yes. After editing the source table, select a cell in the loaded result and choose Data > Refresh All to rerun the query.
What if a name itself contains a comma?
A simple comma delimiter will split that name too. Change the source delimiter or use a more structured source format before splitting.




