☰

使用 Excel 解析格式不一致的文本数据

tag-icon • tag-icon microsoft-excel text-processors
使用 Excel 解析格式不一致的文本数据

我在解析电子表格中的文本时遇到了困难。我的电子表格中有数百条记录，这些记录与以下示例类似（这些记录都在一个单元格中）：

7431340 03 POOLS E TECHNOLOGIES INC 90 NEW ENGLAND DRIVE 16 0.00 6,900 7,060

我需要将下面的每一个解析到单独的单元格中：

[7431340] [03 POOLS E TECHNOLOGIES INC] [90 NEW ENGLAND DRIVE] [7,060]

或者在 Excel 表中显示

Excel 中的最终结果显示，A 列中显示第一个数字，B 列中显示数字加公司名称，C 列中显示街道地址，D 列中显示行中的最后一个数字

我遇到的问题是每条记录的数据不一致。更多示例：

7170258 111 HARBOR POINT OWNER LLC 115 TOWNE STREET 16 0.00 189,200 196,730
4469000 4 KIDZ INC 62 SOUTHFIELD AVENUE 16 0.00 3,000 9,500
6369875 3 HERMANOS LLC 912 EAST MAIN STREET 16 0.00 4,640 4 640

我想尽可能避免手动操作，但我担心自己可能运气不佳。

当数据格式不一致时该如何解析？

答案1

如果您的示例确实具有代表性，那么可以使用正则表达式提取出一种模式。这可以在 Excel 中使用 VBA 实现。

图案：

  • 字符串开头
  • 一系列数字后跟一个空格
  • 以一个或多个数字开头且不包含其他数字的子字符串
  • 第二个子字符串以一个或多个数字开头，且不包含其他数字
  • 多个空格分隔的数字逗号组，带有可选的逗号（返回最后一个）

所有示例都表明了这种模式。下面是一个将处理该问题的用户定义函数：

Option Explicit
Function extrAddressPart(sAddr As String, lPart As Long)
    Dim RE As Object, MC As Object
    Const sPat As String = "^(\d+)\s+(\d+\D+)\s+(\d+\D+)\s+.*\s+([\d,]+)$"

Set RE = CreateObject("vbscript.regexp")
With RE
    .Pattern = sPat
    .MultiLine = True
    .Global = False
    If .Test(sAddr) = True Then
        Set MC = .Execute(sAddr)
        extrAddressPart = MC(0).submatches(lPart - 1)
    End If
End With
End Function

在此处输入图片描述

当然，如果你的例子不能真正具有代表性，那么这个解决方案将不起作用。

答案2

由于您正在处理多个分隔符，因此在这种情况下我想向您建议两种可能的解决方案。

解决方案 1（VBA 宏）：

Function ReplaceAndSplit(ByRef Text As String, ByRef DelimChars As String) As String()
    Dim DelimLen As Long, Delim As Long
    Dim strTemp As String, Delim1 As String, Arr() As String, ThisDelim As String
    strTemp = Text
    Delim1 = Left$(DelimChars, 1)
    DelimLen = Len(DelimChars)
    For Delim = 2 To DelimLen
        ThisDelim = Mid$(DelimChars, Delim, 1)
        If InStr(strTemp, ThisDelim) <> 0 Then _
            strTemp = Replace(strTemp, ThisDelim, Delim1)
    Next
    ReplaceAndSplit = Split(strTemp, Delim1)
End Function

怎么运行的：

  1. 将此代码作为模块与工作表一起输入。

  2. 在所需的单元格中写入此公式，然后填充直至需要，按F2并完成Ctrl+Shift+Enter。

    {=ReplaceAndSplit(A2,",")}

笔记：

  1. ","作为分隔符Comma。
  2. 應該" "是Space。
  3. "[ ]"为了Brackets。

解决方案 2（Excel 公式）：

=SUBSTITUTE(TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",256)),(COLUMNS($A:A)-1)*255+1,255)),"]","")

笔记：

执行公式前，需要根据分隔符对公式的这一部分进行编辑，例如：

  1. SUBSTITUTE($A1,","作为Comma分隔符。
  2. SUBSTITUTE($A1," "作为Space分隔符。
  3. SUBSTITUTE($A1,"["作为Bracket分隔符。

相关内容