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.

Download Practice Workbook

You can download the practice workbook from the download button below.

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

📌 Declare Necessary Variables

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

📌 Copy Data to a New Cell

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

📌 Select the New Cell

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

📌 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

📌 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

📌 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

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.

Read More: How to Split Comma Separated Values into Rows or Columns in Excel

2 More Ways to Split Data from One Cell into Multiple Rows in Excel

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.

Read More: Excel Split Data into Columns by Comma (7 Methods)

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.

📌 Steps

• First, select the cell containing the data i.e. cell B2 in this case. Then select Data >> From Table/Range as shown below.

• Then select OK to create an Excel Table with the data. Besides, it will lead you to the Power Query Editor.

• Now select Split Column >> By Delimiter from the Home tab in the editor.

• Then choose Comma as the delimiter and select the radio button for Each occurrence of the delimiter. After that, select Rows from the Advanced options. Next, hit the OK button.

• After that, you will see a preview of the split data. Then select Close & Load >> Close & Load To as shown in the following picture

• Now select Table and then Existing Worksheet as shown below. Then use the upward arrow to select the cell (B5) where you want to get the split data. Next, hit the OK button.

• You will see the following while using the arrow to select a cell.

• After hitting the OK button, you will see the split data as follows. Now you can convert the tables to ranges and clear the formatting and headers if you want.

Read More: How to Split Data from One Cell into Multiple Rows in Excel (3 Methods)

Things to Remember

• Always select the cell containing the data before applying the methods.
• You may need to use CTRL+SHIFT+Enter to apply the formula in the second method.

Conclusion

Now you know how to create an excel macro to split data from one cell into multiple rows in excel. Please let us know if this article has helped you to solve your problem. You can also use the comment section below for further queries or suggestions. Do visit our ExcelDemy blog to read more on excel. Stay with us and keep learning.

Further Readings

• How to Split Data in Excel (5 Ways)
• Split Data into Equal Groups in Excel (3 Methods)
• How to Split Data in One Excel Cell into Multiple Columns (5 Methods)
• Excel Macro to Split Data into Multiple Files (With Simple Steps)
• How to Split Data into Multiple Worksheets in Excel