asked on

How to Expand Excel Rows into multiple Rows based on a delimiter in a Field?

I have been tasked with taking a Spreadsheet Report that was exported out of an ESRI Layer in ArcMap and putting it into a format for my executive team. The problem is the way the program reports is not the way they wanted it. The "CountDisp" Row F contains some information that is concatenated by plus signs, but the way I am needing it is that the information in between each "+" needs to be in it's own Row. I was not sure the proper way to go about this and I could not figure out a Formula that would even come close to getting what I wanted. I attached an example spreadsheet with the first sheet is the original report, and the second sheet is what the executive team wants/needs to see. I formatted the first four lines manually. If nothing works then we are going to have to do this manually for over 10000 Rows, which I was trying to avoid.
TestStrandTable.xls

Wayne Taylor (webtubbs)

Try running this macro...

Sub ExpandRows()

    Dim rw As Long, arr() As String, a As Long
    
    For rw = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
        arr = Split(Cells(rw, "F"), "+")
        Cells(rw, "F") = arr(0)
        For a = 1 To UBound(arr)
            Rows(rw + 1).Insert
            Cells(rw + a, "F") = arr(a)
        Next
    Next
    
End Sub

Open in new window

ASKER CERTIFIED SOLUTION