Excel Macro to Split a Cell into Multiple Rows (With Easy Steps)

This article illustrates how to create an Excel macro to split data from one cell into multiple rows in excel. You may need to do this if you have similar data in a single cell separated by commas or any other delimiters. This will save you a lot of time and effort. Have a quick look through this article to see how to do that easily. This article also highlights 3 other methods to do that if you don’t want to use VBA for it. The following picture highlights the purpose of this article.

How to Create an Excel Macro to Split a Cell into Multiple Rows

You can use the VBA editor in excel to create a macro if you often need to split data from one cell into multiple rows. We will see how to do that in this section.

Steps to Build an Excel Macro to Split a Cell into Multiple Rows

📌 Open the VBA Editor in Excel

First, select cell B2 which contains the data you want to split. Then press ALT+F11 to open the VBA window.
Next, select Insert >> Module as shown below to create a new blank module. We will build the code in that module.

📌 Create a Sub Procedure

Sub SplitCell()

End Sub

We will write the code in this procedure.

📌 Declare Necessary Variables

Sub SplitCell()
Dim Cell, SplitCell() As String
End Sub

The Cell variable takes the value of the active cell. The SplitCell() variable stores the array of rows into which the data will split.

📌 Copy Data to a New Cell

Sub SplitCell()
Dim Cell, SplitCell() As String
ActiveCell.Copy ActiveCell.Offset(2, 0)
End Sub

The undo command (CTRL+Z) in excel doesn’t work for the changes made with VBA. So we will copy the data to a new location.

ActiveCell.Copy is to copy the data from the currently active cell.

ActiveCell.Offset(2, 0) is the destination for the copy operation. It refers to a cell that is directly 2 rows below the active cell.

📌 Select the New Cell

Sub SplitCell()
Dim Cell, SplitCell() As String
ActiveCell.Copy ActiveCell.Offset(2, 0)
ActiveCell.Offset(2, 0).Select
End Sub

ActiveCell.Offset(2, 0).Select activates the new cell to which we copied our data. This way the data in the original cell will remain unchanged.

📌 Set the Value for the Variables

Sub SplitCell()
Dim Cell, SplitCell() As String
ActiveCell.Copy ActiveCell.Offset(2, 0)
ActiveCell.Offset(2, 0).Select
Cell = ActiveCell.Value
SplitCell = Split(Cell, ",")
End Sub

Cell = ActiveCell.Value specifies that the Cell variable will take the value of the new active cell.

SplitCell = Split(Cell, “,”) will split the value stored against the Cell variable considering comma (,) as the split criteria. You can use other delimiters (. ; / etc.) inside the double quotes (“ ”) based on your data.

📌 For Loop to Split Data

Sub SplitCell()
Dim Cell, SplitCell() As String
ActiveCell.Copy ActiveCell.Offset(2, 0)
ActiveCell.Offset(2, 0).Select
Cell = ActiveCell.Value
SplitCell = Split(Cell, ",")
For i = 0 To UBound(SplitCell)
Next i
End Sub

The For Loop will split the data every time the comma appears.

UBound(SplitCell) refers to the largest possible size of the SplitCell array.

📌 Filling Rows with Split Data

Sub SplitCell()
Dim Cell, SplitCell() As String
ActiveCell.Copy ActiveCell.Offset(2, 0)
ActiveCell.Offset(2, 0).Select
Cell = ActiveCell.Value
SplitCell = Split(Cell, ",")
For i = 0 To UBound(SplitCell)
ActiveCell.Offset(i, 0).Value = SplitCell(i)
Next i
End Sub

ActiveCell.Offset(i, 0).Value = SplitCell(i) returns the corresponding value into each row of the SplitCell array.

Run the VBA Code

Now keep the cursor in the code and press F5 to run it. After that, the data in cell B2 will split into multiple rows as shown in the following picture.

How to Split Data from One Cell into Multiple Rows in Excel:

2 More Ways

Here I am going to illustrate 2 more methods for you in case you don’t want to use VBA. So, let’s begin.

1. Split a Cell into Multiple Rows with Text to Columns & Paste Special in Excel

You can get the same result obtained with VBA using the Text to Columns wizard and the Paste Special feature in excel. Follow the steps below to be able to do that.

📌 Steps

First, select cell B2 i.e. the cell containing the data you want to split.
Then select the Text to Columns wizard from the Data tab as shown in the following picture.

After that, keep the radio button for the Delimited file type selected. Then select the Next button as shown below.

Then check Comma as Delimiters and select the Next button again.

Now the default General data format converts numeric values to numbers, date values to dates, and all remaining values to text. So keep the Column data format to its’ default. After that, use the upward arrow in the Destination field to choose the cell (B4) where you want to get the split data. Then select the Finish button.

After that, you will see the data from cell B2 split into multiple columns as follows.

Now copy the range C4:M4 and paste it as Transpose on cell B5.

After that, you can delete the helper cells. Finally, you will get the desired result as follows.

2. Split a Cell into Multiple Rows with Power Query in Excel

Follow the steps below to use the Power Query to split data from one cell into multiple rows in excel.