How to Split Data with Newline Characters into Separate Rows in Excel Using Power Query

When working with datasets in Excel, you might encounter situations where multiple values are stored in a single cell, separated by a newline character (added using Alt + Enter). This can make data analysis challenging.

In this blog, we’ll walk you through how to split such data into separate rows using Power Query, a powerful tool within Excel for data transformation.

Example Dataset:

Let’s consider the above example.

Step-by-Step Guide to Using Power Query

Step 1: Load Data into Power Query

1. Select your dataset by CTRL+A (including headers).
2. Go to the Data tab → Click From Table/Range.
3. In the dialog box, ensure My table has headers is checked → Click OK.
   
4. This will open a new window.

Step 2: Split Column by Delimiter

1. 1. Select a column where multiple values are separated by newlines.
   2. Under the Home tab, find Split Columns and select By Delimiter.
   3. In the Advanced Options, choose to split the values into Rows or Columns, In this case we are splitting the columns in row wise. Hence selected as Rows.
   4. Under Insert Special Characters, select Line Feed and click OK.

Once it is loaded into the new Sheet then below is the output data.

By following these steps, you can efficiently split data with newline characters into separate rows, making your data analysis much easier.

Source: Read More