Excel VBA: Split String into Rows (6 Ideal Examples)

In this method, we’re going to show you 6 ways for Excel VBA split string(s) into rows. To demonstrate our methods, we’ve chosen a dataset with 2 columns: “Occupation”, and “Name”.

excel vba split string into rows


Download Practice Workbook


6 Examples to Split String into Rows Using Excel VBA

1. Split Space Separated String into Rows by Using Excel VBA

For our first method, we’re going to use For Next Loop in Excel VBA to split strings into rows. Here, in our dataset row, 5 and 6 have space between the Names. We’re going to split it into another row, and the left side value will be copied down for each value.

Steps:

Before writing our codes, we need to bring up the Module window, to do that –

  • Firstly, from the Developer tab >>> select Visual Basic.

Then the Visual Basic window will appear.

  • Secondly, from Insert >>> select Module.

This will bring our Module window, where we can type our VBA code.

excel vba split string into rows

  • Thirdly, type the following code.
Sub SplitCells()
    Dim cRange As Range, lRow As Long, j As Range
    Dim sRange As Range
    Dim x As Integer
    Dim iString As Variant
    Dim As String
    lRow = Cells(Rows.Count, "B").End(xlUp).Row
    Set cRange = Range("B4:B" & lRow)
    For Each j In cRange.Cells
        Set sRange = j.Offset(, 1)
        s = sRange.Value
        iString = Split(s, " ")
        For x = 0 To UBound(iString)
            Cells(Rows.Count, "B").End(xlUp).Offset(1) = j
            Cells(Rows.Count, "B").End(xlUp).Offset(, 1) = iString(x)
        Next x
    Next j
Range("B" & lRow + 1).EntireRow.Resize(1).Insert
End Sub

VBA Code Breakdown

  • Firstly, we’re calling our Sub Procedure SplitCells.
  • Secondly, declaring our variable types.
  • Thirdly, using the Range.End property to find the last row of our data.
  • Then, we’re setting our dataset range with the Set Statement.
  • After that, Nested For Next Loops are used to split the strings. Here, the delimiter is set to a “Blank Space”.

  • Then, Save and close the Module.

Now we’ll Run our code. To do that, we need to bring up the Macro dialog box, select our Sub and Run it.

  • Firstly, from the Developer tab >>> select Macros.

excel vba split string into rows

The Macro dialog box will appear.

  • Finally, select “SplitCells” and click on Run.

This will execute our code. We’ll see our strings are split into multiple rows.

excel vba split string into rows

Read More: Excel VBA: Split String into Cells (4 Useful Applications) 


2. Excel VBA to Split Comma Separated String into Rows

In this section, we’ll use another For Next Loop. This method is similar to the first method, however, we’ll replace our cells this time. Moreover, We have separated our Names by a comma.

Steps:

  • Firstly, follow method 1 to bring up the VBA Module window and type this code.
Sub SplitCells2()
Dim lRow As Long, j As Long
Dim r As Variant
Application.ScreenUpdating = False
lRow = Range("B" & Rows.Count).End(xlUp).Row
Columns("B").Insert
For j = lRow To 1 Step -1
    With Range("C" & j)
        If InStr(.Value, ",") = 0 Then
            .Offset(, -1).Value = .Value
        Else
            r = Split(.Value, ",")
            .Offset(1).Resize(UBound(r)).EntireRow.Insert
            .Offset(, -1).Resize(UBound(r) - LBound(r) + 1).Value = Application.Transpose(r)
        End If
    End With
Next j
Columns("C").Delete
lRow = Range("B" & Rows.Count).End(xlUp).Row
With Range("C4:D" & lRow)
    On Error Resume Next
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    On Error GoTo 0
    .Value = .Value
End With
Columns("D").Delete
Application.ScreenUpdating = True
End Sub

VBA Code Breakdown

  • Firstly, we’re calling our Sub Procedure SplitCells2.
  • Secondly, declaring our variable types.
  • Thirdly, using the Range.End property to find the last row of our data.
  • After that, column B is inserted.
  • Then, used the If statement within the For Next Loop to split the strings. Here, the delimiter is set to a “Comma”.
  • After that, we delete column D.
  • Finally, set the Application.ScreenUpdation property to True.

excel vba split string into rows

  • Secondly, Save and close the Module.
  • Thirdly, bring up the Macro window and select “SplitCells2”.
  • Finally, press Run.

This code will execute now. Here, the outcome will be similar to method 1, but our output data will start from cell B5. Moreover, the format of the cells will break. We need to manually add back the formatting to the cells.

excel vba split string into rows

Read More: Excel Formula to Split String by Comma (5 Examples) 


3. Split Line Break Separated String into Rows

For the third method, we’ve separated our Names by using a Line Break. Whenever our code finds a Line Break, it will split it into a newly created row. The existing data will move down.

Steps:

  • Firstly, follow method 1 to bring up the VBA Module window and type this code.
Public Sub SplitCells3()
    tCol = "C"
    lRow = Range(tCol & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For Each xRange In Range(tCol & "4" & ":" & tCol & lRow)
        If InStr(xRange.Value, vbLf) Then
            xRange.EntireRow.Copy
            xRange.EntireRow.Insert
            xRange.Offset(-1, 0) = Mid(xRange.Value, 1, InStr(xRange.Value, vbLf) - 1)
            xRange.Value = Mid(xRange.Value, Len(xRange.Offset(-1, 0).Value) + 2, Len(xRange.Value))
        End If
    Next
    lRow2 = Range(tCol & Rows.Count).End(xlUp).Row
    For Each xRange2 In Range(tCol & "4" & ":" & tCol & lRow2)
        If Len(xRange2) = 0 Then
            xRange2.EntireRow.Delete
        End If
    Next
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
End Sub

Formula Breakdown

  • Firstly, we’re calling our Sub Procedure SplitCells3.
  • Secondly, set column C to split.
  • Thirdly, using the Range.End property to find the last row of our data.
  • After that, column B is inserted.
  • Then, used the If statement within the Nested For Loops. Here, the IF statement will check whether the selected value has Line Break or not if it contains Line Break then it will split the strings. Here, we’re using “vblf” for the Line Break.
  • After that, we delete an entire row.
  • Then, set the Application.ScreenUpdation property to True.
  • Finally, we’re setting the Application.CutCopyMode as False. This will cancel the CutCopyMode.

excel vba split string into rows

  • Secondly, Save and close the Module.
  • Thirdly, bring up the Macro window and select “SplitCells3”.
  • Finally, press Run.

 

After executing the code, it’ll split our strings into rows using Excel VBA.

excel vba split string into rows

Read More: Excel VBA: Split String by Character (6 Useful Examples) 


4. Split String into Rows to Place It in a New Worksheet

For the fourth method, we’ll again use the For Next Loop to split strings into rows. You may think, we’re doing the same thing over and over again. However, in this method, we’ll display our output on a separate Sheet. Without further ado, let’s jump into the action.

Steps:

  • Firstly, follow method 1 to bring up the VBA Module window and type this code.
Option Explicit
Const tRow As String = "C"
Const sRow As Long = 5
Sub SplitCells4()
    Dim xRow As Long
    Dim lRow As Long
    Dim sheet As Worksheet
    Dim xSplit() As String
    Dim xIndex As Long
    Dim xSize As Long
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    With ThisWorkbook
        .Worksheets("separatesheet").Copy After:=.Worksheets("separatesheet")
        Set sheet = ActiveSheet
    End With
    With sheet
        lRow = .Cells(.Rows.Count, tRow).End(xlUp).Row
    End With
    For xRow = lRow To sRow Step -1
        xSplit = Split(sheet.Cells(xRow, tRow).Value2, ";")
        xSize = UBound(xSplit) - LBound(xSplit) + 1
        If xSize = 1 Then GoTo Continue
        sheet.Rows(xRow).Copy
        sheet.Rows(xRow).Resize(xSize - 1).Insert
        For xIndex = LBound(xSplit) To UBound(xSplit)
            sheet.Cells(xRow, tRow).Offset(xIndex).Value2 = xSplit(xIndex)
        Next xIndex
Continue:
    Next xRow
    Application.CutCopyMode = False
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

VBA Code Breakdown

Here, our main data starts from row 5 and the strings to split are in the C column. Therefore, we’ve defined those at the beginning of the code. Then, our main code starts.

  • Firstly, we’re calling our Sub Procedure SplitCells4.
  • Secondly, declaring our variable types.

We’re setting our Worksheet name as “separateSheet” here. Moreover, we’re setting the data to be copied after this Sheet.

  • Thirdly, using the Range.End property to find the last row of our data.
  • Then, For Next Loop is used to split the strings. Here, the delimiter is set to a “Comma”.
  • After that, we use For Next Loop.
  • Finally, set the Application.ScreenUpdation property to True.

excel vba split string into rows

  • Secondly, Save and close the Module.
  • Thirdly, bring up the Macro window and select “SplitCells4”.
  • Finally, press Run.

This will perform the operation described in the code breakdown section. Our output will be on a separate Sheet, therefore, our original data will be preserved.

excel vba split string into rows

Read More: Excel Formula to Split: 8 Examples


5. Excel VBA to Split String into Rows for a Single Row

In this section, we’re gonna split the first row of our dataset only. We’re gonna utilize For Next Loop again here. Moreover, we can define the location of the output for our split strings as per our requirements.

Steps:

  • Firstly, follow method 1 to bring up the VBA Module window and type this code.
Sub SplitCells5()
Dim s1 As String
Dim s2 As String
Dim x1 As Integer
Dim x2 As Integer
Dim rCell As Variant
s1 = Range("c5", "C5").Value
rCell = Split(s1, ",")
For x1 = 0 To UBound(rCell)
    Cells(x1 + 10, 3).Value = rCell(x1)
    Next x1
s2 = Range("b5", "B5")
rCell = Split(s2, ",")
For x2 = 0 To UBound(rCell)
    Cells(x2 + 10, 2).Value = rCell(x2)
    Next x2
 End Sub

VBA Code Breakdown

  • Firstly, we’re calling our Sub Procedure SplitCells5.
  • Secondly, declaring our variable types.
  • Thirdly, using the two separate For Next Loop to split row 5 of our dataset.
  • Here, the delimiter is set to a “Comma”.

excel vba split string into rows

  • Secondly, Save and close the Module.
  • Thirdly, bring up the Macro window and select “SplitCells5”.
  • Finally, press Run.

After this, our values from row 5 will be split into 2 rows in rows 10 and 11. Thus, we’ve shown you yet another VBA code to split strings into rows.

excel vba split string into rows

Read More: How to Split a Cell into Two Rows in Excel (3 ways)


6. Split Range of Strings into Rows

In this last method, we’ll be using Do While Statement in Excel VBA to split strings into rows. Here, we’ll not copy the values from the left side to our separated string. Instead, the value will be visible for the first instance only.

Steps:

  • Firstly, follow method 1 to bring up the VBA Module window and type this code.
Option Explicit
Sub SplitCells6()
  Dim iSplit As Long
  Dim sCell() As String
  Dim cRow As Long
  With Worksheets("dowhile")
    cRow = 5
    Do While True
      If .Cells(cRow, "C").Value = "" Then
        Exit Do
      End If
       sCell = Split(.Cells(cRow, "C").Value, ",")
      If UBound(sCell) > 0 Then
        .Cells(cRow, "C").Value = sCell(0)
        For iSplit = 1 To UBound(sCell)
          cRow = cRow + 1
          .Rows(cRow).EntireRow.Insert
          .Cells(cRow, "C").Value = sCell(iSplit)
          .Cells(cRow, "D").Value = .Cells(cRow - 1, "D").Value
        Next
      End If
      cRow = cRow + 1
    Loop
  End With
 End Sub

VBA Code Breakdown

  • Firstly, we’re calling our Sub Procedure SplitCells6.
  • Secondly, declaring our variable types.
  • Thirdly, set the row number as 5.
  • After that, the VBA If Statement is provided within the Do While loop.
  • The condition is as follows: when there is a non-blank value in column C, then it will look for a comma and split it to a newly inserted row.

excel vba split string into rows

  • Secondly, Save and close the Module.
  • Thirdly, bring up the Macro window and select “SplitCells6”.
  • Finally, press Run.

This will execute our code. In conclusion, we’ll split our strings into multiple rows as we explained in the code breakdown section.

excel vba split string into rows

Read More: Excel VBA: Split String by Number of Characters (2 Easy Methods)


Practice Section

We’ve provided a practice dataset for each method in the Excel file.


Conclusion

We’ve shown you 6 examples in Excel VBA split string into rows. These Examples will be helpful for you. Thanks for reading, keep excelling!


Related Articles

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo