将数组拆分为多行 [英] Splitting an array into multiple rows

问题描述

我在excel中有多个单元格，其中包含70个点的数组.如何将数组中的每个数字拆分为自己的行?

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:

A列:

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

B列:

[0.00e+00,9.06e+02]

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所说的Split函数就是您想要的，然后可以将结果数组输出到工作表中

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.

这篇关于将数组拆分为多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！