Splitting an array into multiple rows

Question

I have multiple cells in excel that consists of an array of 70 points. How can I split each of these numbers in the array into its own row?

For example, currently I have an array as so:

Column A:

[(42.07, -86.03), (42.074092, -87.031812000000002)]

Column B:

[0.00e+00,9.06e+02]

Column C:

[1.69e+01,1.72e+00]

All of these array are on the same row. However, I want it to show up as so in two seperate rows:

(42.07, -86.03)  |0.00e+00    |1.69e+01

(42.074092, -87.031812000000002) |9.06e+02    |1.72e+00

@AlexP looking at this right now: https://www.techonthenet.com/excel/formulas/split.php However, I do not want it to return in this format. — taa06, Jun 20 '17 at 15:20

score 0 · Answer 1 · answered Jun 20 '17 at 20:15

Try something like this

Sub ArrayToRows()
'Declare your array or arrays


'Array1 would correspond to your "Column A" array, I'm not sure of the names you want, _
'just change the variables to suit your needs

For i = LBound(Array) to UBound(Array)
    Cells(1,1).Value = Array1(i) & " " & Array2(i) & " " & Array3(i)
Next i

End Sub

score 0 · Accepted Answer · answered Jun 21 '17 at 00:29

as @AlexP said the Split function is what you want here, you can then output the resulting array to the worksheet

Sub ExpandToRows()

    Dim ColumnList As String, col As Variant, c As Range
    Dim OutputArray() As String, i As Long

    'list of columns to process
    ColumnList = "A,B,C" 'change to suit

    With Sheet1 'change to suit

        For Each col In Split(ColumnList, ",")

            Set c = .Cells(1, col) 'assume row 1, change to suit

            'determine if pair group
            If InStr(c.Value, "), (") > 0 Then

                'replace delimiter
                c.Value = Replace(c.Value, "), (", ")~(")

                'create array of values
                OutputArray = Split(c.Value, "~")

            Else '...or single values

                'create array of values
                OutputArray = Split(c.Value, ",")

            End If

            'write OutputArray to worksheet
            c.Resize(UBound(OutputArray) + 1) = Application.Transpose(OutputArray)

        Next col

    End With

End Sub

I have added in handling for the pair groups although note, this assumes all values in a column entry are consistent.

Splitting an array into multiple rows

2 Answers2

Linked