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

Question

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

例如，我如何转动以下几行:

进入类似:

在 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, "")

但也没有用。

还有其他方法吗？

Answer 1

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

请注意，此算法不会评估单个空白字符的实例是否应被视为噪声(如“TUBELINES UNASSIGNED”中)或 ar 作为有效的单词分隔符(如“Unit Cost”中)。因此，在 ReplaceWhitespace 中，作为噪声的单个空格被视为特殊情况:"- -"~~> "-;-" 和 "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

运行ParseUnstructed()的结果: