Splitting Delimited Text Inside Cells Onto New Rows

Splitting Delimited Text Inside Cells Onto New Rows

January 14, 2020

The vba code shown below posted here, can be used to split text entered with delimiters inside a cell onto separate rows and repeat the information from adjacent columns on the new rows.  So, in this example we have the names of baseball players separated with commas in column A, and the city and team they played for in columns B and C. 

 

 When the macro is run the names of each player are listed on separate rows, and the name of their city and team repeats in column B and C. 

 

Edit the line "With Range("A1:C" & LR)" from C to a higher column letter to account for longer arrays.   Change given delimiters on these lines:

 

If InStr(.Value, ",") = 0 Then

 

  X = Split(.Value, ",")

 

 

Sub Splt()
Dim LR As Long, i As Long
Dim X As Variant
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
Columns("A").Insert
For i = LR To 1 Step -1
    With Range("B" & i)
        If InStr(.Value, ",") = 0 Then
            .Offset(, -1).Value = .Value
        Else
            X = Split(.Value, ",")
            .Offset(1).Resize(UBound(X)).EntireRow.Insert
            .Offset(, -1).Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)
        End If
    End With
Next i
Columns("B").Delete
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("A1:C" & LR)
    On Error Resume Next
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    On Error GoTo 0
    .Value = .Value
End With
Application.ScreenUpdating = True
End Sub
 

Please reload

Contact Me With Your Litigation Support Questions:

seankevinoshea@hotmail.com

  • Twitter Long Shadow

© 2015 by Sean O'Shea . Proudly created with Wix.com