excel - Excel或VBA将非结构化文本转换为列

没有适当的分隔符的非结构化文本时，如何将文本转换为列。

例如，我如何改变以下几行：



变成类似：



在Excel中，“文本到”列似乎找不到正确的分隔符（空格，制表符...）。我在VBA中尝试了以下操作：

I1 = Mid(Cells(i, 1), 1, 16)
I2 = Mid(Cells(i, 1), 17, 33)
I3 = Mid(Cells(i, 1), 34, 49)
I4 = Mid(Cells(i, 1), 50, 53)
I5 = Mid(Cells(i, 1), 54, 66)
I6 = Mid(Cells(i, 1), 67, 82)
I7 = Mid(Cells(i, 1), 83, 99)
I8 = Mid(Cells(i, 1), 100, 116)
I9 = Mid(Cells(i, 1), 117, 133)

但是我知道它不适用于所有列。例如，对于I3，我得到了更多期望的值，例如：



我也尝试过替换选项卡（如果存在的话），例如：

MyString = Replace(MyString, vbTab, "")

但是也没有用。

还有其他方法可以解决吗？

最佳答案

这是尝试使用自定义的ReplaceWhitespace函数，该函数根据长度依次替换空白部分。作为中间步骤，空白用分号代替；删除不必要的分号作为最后一步。 Split is used to read将已解析的字符串转换为数组，然后将array is used将结果读取到工作表中。根据您的特定需求调整ReplaceWhitespace应该很简单。

请注意，此算法不会评估将单个空格字符的实例视为噪声（如在“未指定的管道中”）还是应视为有效的单词除错器（如在“单位成本”中）。因此，在ReplaceWhitespace中将单个空白作为噪声被视为特殊情况："- -" ~~> "-;-"和" UNASSIGNED " ~~> ";UNASSIGNED;"

假设屏幕截图中的数据位于A1:A4范围内，则此代码或多或少会产生所需的输出，如下面的屏幕截图所示。

编辑：ReplaceWhitespace的初始设计是基于反复试验。经过一番思考之后，我意识到空白字符或分号为composite number的模式将由算法中寻找那些字符数为质数的模式的行来处理。我已经相应地更新了代码。

Sub ParseUnstructured()
    Dim i As Long
    For Each cell In Range("A1:A4")
        i = i + 1
        ' Clean whitespace:
        sRow = ReplaceWhitespace(cell.Value)
        ' Read to array
        Dim sArray() As String
        sArray() = Split(sRow, ";")
        ' Read to worksheet:
        Range("A1").Offset(5 + i).Resize(1, UBound(sArray)+1).Value = sArray
    Next cell
End Sub

Function ReplaceWhitespace(sInput As String) As String
    Dim sOutput As String
    ' Look for special cases with single-whitespace noise:
    sOutput = Replace(sInput, "- -", "-;-") ' Take care of "----- ----"
    sOutput = Replace(sOutput, "UNASSIGNED", ";UNASSIGNED;")
    ' Look for patterns where the number of "noise" characters is a prime number:
    sOutput = Replace(sOutput, "       ", ";") ' 7 whitespaces
    sOutput = Replace(sOutput, "     ", ";") ' 5
    sOutput = Replace(sOutput, "   ", ";") ' 3
    sOutput = Replace(sOutput, "  ", ";") ' 2
    ' sOutput = Replace(sOutput, " ", "_") ' 1 Optional
    sOutput = Replace(sOutput, ";;;;;", ";") ' 5 semicolons
    sOutput = Replace(sOutput, ";;;", ";") ' 3
    sOutput = Replace(sOutput, ";;", ";") ' 2
    sOutput = Replace(sOutput, "; ", ";") ' Takes care of some leftovers.
    ReplaceWhitespace = sOutput
End Function

运行ParseUnstructured()的结果：