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.

Excel Macro to Split a Cell into Multiple Rows


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.

Excel Macro to Split a Cell into Multiple Rows


πŸ“Œ 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.

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.

Split Data from One Cell into Multiple Rows with Text to Columns & Paste Special in Excel

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

Split Data from One Cell into Multiple Rows with Text to Columns & Paste Special in Excel

  • 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

Split Data from One Cell into Multiple Rows with Power Query in Excel

  • 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

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo