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”.

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.

• 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

• 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.

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.

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

• 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.

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

• 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.

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

• 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.

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

• 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.

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

• 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.

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

• Excel Split Cell by Delimiter Formula
• How to Split One Cell into Two in Excel (5 Useful Methods)
• How to split a single cell in half in Excel (diagonally & horizontally)
• VBA to Split String into Multiple Columns in Excel (2 Ways)
• How to Split Cells in Excel (5 Easy Tricks)
• How to Make Two Lines in One Cell in Excel (4 Methods)