Excel将单个数组拆分为多个单元格

An application I use exports strings of data in an array in one cell, like so:

我使用的应用程序在一个单元格中导出数组中的数据字符串，如下所示：

"data1"= value1; "data2"= value2; "data3"= value3; "data4"= value4; "data5"= value5;

I'd like an equation or macro or something that could split it like this:

我想要一个方程式或宏或者可以像这样拆分它的东西：

----------------------------------------------------------------
|   |  data0  |  data1  |  data2 |  data3  |  data4  |  data5  |
----------------------------------------------------------------
| 1 |  ARRAY  |  value1 | value2 |  value3 |  value4 |  value5 |
----------------------------------------------------------------
| 2 |  ARRAY  |  value1 | value2 |  value3 |  value4 |  value5 |
----------------------------------------------------------------
| 3 |  ARRAY  |  value1 | value2 |  value3 |  value4 |  value5 |
----------------------------------------------------------------
. . . . and so on.

Is this possible?

这可能吗？

Here is a string from the document:

这是文档中的字符串：

"search-terms1" = Chinese jade; "search-terms2" = Chinese archers; "search-terms3" = Antique jade; "search-terms4" = Asian jade; "search-terms5" = China history;

It will always be search-terms1 - search-terms5, but the 'values' will be different for each row.

它将始终是search-terms1 - search-terms5，但每行的'values'将不同。

Excel 2003 on Windows XP

Windows XP上的Excel 2003

3 个解决方案

#1

This will start in A2 and go down for how ever many rows have data. It's splits the values out into the columns to the right.

这将从A2开始，然后向下看多行有多少数据。它将值拆分为右侧的列。

Sub SplitSearch()

    Dim vaTerms As Variant
    Dim vaSearch As Variant
    Dim rCell As Range
    Dim vaOutput As Variant
    Dim i As Long

    For Each rCell In Sheet1.Range("A2", Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp)).Cells
        vaSearch = Split(rCell.Value, ";")
        ReDim vaOutput(1 To 1, 1 To UBound(vaSearch))
        For i = LBound(vaSearch) To UBound(vaSearch)
            vaTerms = Split(vaSearch(i), "=")
            If UBound(vaTerms) > -1 Then
                vaOutput(1, i + 1) = Trim(vaTerms(UBound(vaTerms)))
            End If
        Next i
        rCell.Offset(0, 1).Resize(1, UBound(vaOutput, 2)).Value = vaOutput
    Next rCell
End Sub

#2

You you probably should do a search and replace on the complete array string, replacing the semi-colons with a linefeed, \n or vbCrLf BEFORE the array string is brought into excel because cell string size limits may cause truncation of your content, if you are pulling a lot of data in the string. (Stuff may be cut off and you won't know)

您可能应该对完整的数组字符串进行搜索和替换，在将数组字符串置于excel之前用换行符\ n或vbCrLf替换分号，因为单元格字符串大小限制可能会导致截断您的内容，如果您正在拉动字符串中的大量数据。（东西可能被切断，你不会知道）

Anything less may not be correctly importing all of the results you are dealing with.

少一些可能无法正确导入您正在处理的所有结果。

Once you replace the semi-colons with linefeeds import the text, or open the file with '=' as the delimiter

使用换行符替换分号后，导入文本，或使用“=”作为分隔符打开文件

#3

Split the string first at ";", than at "=". The code below can get you started:

首先在“;”处拆分字符串，而不是在“=”处拆分。下面的代码可以帮助您入门：

Dim s As String
Dim parts As Variant
s = """data1""= value1; ""data2""= value2;"
parts = Split(s, ";")

For i = 0 To UBound(parts)
    Debug.Print parts(i)
    ' TODO: Split part along "=" and insert into cell
Next i

#1