Need VBA code to extract text which are separated by comma and space in one cell RRS feed

  • Question

  • I need a vba code that separates text that is separated by comma and space in one cell. The code below separates items that are separated by Alt+Enter in one cell. Basically i need this code modified which will do the same but instead of Alt + Enter the item is separated by comma followed by space.

    Please see images for expected result.

    Sub SeperateItemsInCell_Option2()
        Dim s As String
        Dim v As Variant
        Dim l As Long
        Dim lngMaxRow As Long
        Dim r As Long
        lngMaxRow = 500
        'Alternative method of finding last used row in column K
        'lngMaxRow = Cells(Rows.Count, "K").End(xlUp).Row
        For r = lngMaxRow To 2 Step -1    'Must work backwards when inserting or deleting rows
            s = Cells(r, "K").Value
            v = Split(s, vbLf)
            l = UBound(v) - LBound(v) + 1
            If l > 1 Then
                Cells(r, "K").Offset(1, 0).Resize(l - 1, 1).EntireRow.Insert
                Cells(r, "K").Resize(l, 1).Value = Application.Transpose(v)
                Cells(r, "C").Offset(1, 0).Resize(l - 1, 1).Value = Cells(r, "C").Value
            End If
        Next r
    End Sub


    Expected Result:

    Thank you!


    Monday, January 9, 2017 3:29 AM


All replies