How to Split a Cell into Rows in Excel and Google Sheets

Reading Time: 3 minutes

Objective

Learn how to split a cell into rows in Excel and Google Sheets.

Table of Contents

1. Introduction
2. How to Split Cells into Rows in Excel
   1. Step 1: Create a Table Range
   2. Step 2: Split Column by Delimiter
   3. Step 3: Select Split Rows
3. How to Split Cells into Rows in Google Sheets

What does Splitting Cells into Rows mean?

Let’s say you are working on a dataset where a cell contains multiple values separated by a comma or any other delimiter as shown below:

Here, you want to separate values in the Name column into different rows, keeping the other column values unchanged, as shown below:

As you can clearly see in the above image, name strings in the Name column are added to different rows and other column values as before. This article guides you on how to split a cell into rows in Excel and Google Sheets.

To split a cell into rows in Excel, you will need a Power Query Editor version. You can download the version from here.

How to Split Cells into Rows in Excel

Step 1: Create a Table Range

• Select your data.
• Go to Data → Get Table/Range.

Step 2: Split Column by Delimiter

• Select the Split Column option.
• Select By Delimiter.

Step 3: Select Split Rows

• Go to Advanced Options.
• Select Rows.
• Select Split using special characters and select delimiter.
• Click on OK.

How to Split Cells into Rows in Google Sheets

Google Sheets has the following limitations while splitting a cell into rows:

• Google Sheets Split to columns tool always overwrites your original column with the first part of your data and other columns with the split parts.
• You cannot split cells by multiple separators at a time.

Therefore, if you want to split a cell into rows in Google Sheets, you will have to split the text into columns first. Then copy the separated columns. Go to paste special, and paste transpose. 

See Also

Delete Empty rows in Google Sheets: In this article we would see how to delete empty rows in Google Sheets using two different ways.

How to group rows in Google Sheets: We will learn how to group rows in Google Sheets. We will also have a look at nested grouping.

Complete Guide on the REGEXEXTRACT Function in Google Sheets for 2022: Learn how to use the REGEXEXTRACT function in Google Sheets and its variations.

You can checkout other equally good articles on Google Sheets here.