Power Query Problems

Split Comma-Separated Names into Rows in Excel

Camila MilosovichCamila Milosovich Nov 22, 2026 868 views

Question details

Extract comma-separated names from multiple cells into a single vertical list that can be sorted alphabetically.

Split Comma-Separated Names into Rows in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Create a table

Select the source column and press Ctrl+T. Confirm whether the selection has a header, then select OK.

2
Open Power Query

Select a cell in the table and choose Data > From Table/Range, or Data > From Table in Excel 2016.

3
Split by rows

In Power Query Editor, select the names column and choose Split Column > By Delimiter. Select Comma, expand Advanced options, choose Rows, and select OK.

4
Clean and load the names

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.

5
Sort the list

In the loaded table, open the names column's filter menu and select Sort A to Z.

Split names into rows with Power Query in Excel 2016 or later
Multiple source columns: This method splits one selected column. If names are spread across multiple columns, first combine them into one column or use the Excel 2013 macro method.
Work with names in WPS Spreadsheet

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. 1. Open the workbook: Open the Excel workbook in WPS Spreadsheet and create a blank worksheet for the name list.
  2. 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. 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. 4. Sort the names: Select the completed list and choose Data > Sort Ascending. Check for and remove any unwanted leading spaces before sorting.
Split and sort comma-separated names without writing a macroOpen and save Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceWork with a free, lightweight Office alternative
QA img-9

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.