top of page

Separate data delimited in cells onto different rows

Use this technique in Excel to parse out data that is separated with a delimiter inside a single cell on to new rows, with information from adjacent columns repeating on the new rows.

1. On the Data tab, click 'From Table/Range'

2. Select the full range of data that you need to analyze.

3. Power Query will open. Select the column with the data that you need to separate on to new rows.

4. Go to Split Column . . . By Delimiter.

5. In the new dialog box that opens, set the delimiter for your data; choose the radial button for 'Each occurrence of the delimiter', and then in Advanced options choose 'Split into Rows'

6. Click OK, the data will be re-arranged. In this example each email address gets listed on a new row.

7. Finally in Power Query, click on 'Close & Load' and the data will be placed on a new worksheet in Excel.


bottom of page