☰

如何在 Excel 中多次解析单元格中的字符串？

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
如何在 Excel 中多次解析单元格中的字符串？

我的数据表中有一列经常包含我想放在另一列中的字符串。此字符串可能出现多次，我想将其所有实例都放在另一列中。该列是分隔符，我想提取匹配的字符串并一直到分隔符。

一个例子：

Possessions
Fruit: apple, Car: Ford, Fruit: banana,
Car: Saturn,
Fruit: orange,

我希望下一列包含：

Fruit
Fruit: apple, Fruit: banana,

Fruit: orange,

找到该字符串的第一个实例非常容易（新行是为了便于阅读）：

MID(A2, 
    FIND( *first instance of Fruit:* ), 
    FIND( *first comma after Fruit:* ) - FIND( *first instance of Fruit:* )
    )

然而我可能会多次遇到该字符串并且想要捕获它们全部。

此外，该列已经是一个计算字段（对另一张表的引用），所以我无法使用文本到列来按分隔符进行拆分。

关于如何返回字符串的所有实例有什么想法吗？如果可能的话，我宁愿避免使用 VBA 脚本并使用工作表函数，但如果无法使用函数，我愿意使用 VBA。

答案1

具有正则表达式的通用 VBA 解决方案

此方法应该可以满足您的需求。其他用户也可以使用它，借助正则表达式从给定字符串中提取多个字符串

在此处输入图片描述

  1. 打开 VBA 编辑器 ( ALT+ F11)
  2. 插入新模块 (！）并将以下代码粘贴到其中

  3. 返回 Excel 并在需要输出的单元格中使用此公式

    =REGEXTRACT(A1, "Fruit: .*?,")

公式解释

  • =REGEXTRACT()是您的新定制配方。
  • A1是放置输入数据的单元格
  • Fruit: .*?,是一个正则表达式，用于查找所有出现的fruit并匹配直到下一个逗号。
Function REGEXTRACT(objCell As Range, strPattern As String)

    Dim objMatches As Object
    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")

    RegEx.IgnoreCase = True
    RegEx.Global = True
    RegEx.Pattern = strPattern

    Set objMatches = RegEx.Execute(objCell.Value)

    If objMatches.Count <> 0 Then
        For Each objMatch In objMatches
            REGEXTRACT= REGEXTRACT+ objMatch.Value
        Next objMatch
    Else: REGEXTRACT= ""
    End If

End Function

暗示： 回顾与展望VB 的正则表达式引擎不支持表达式。因此，通过正则表达式排除逗号并不容易。但通过正常的 VBA 字符串操作是可以做到的。

答案2

这太笨重了，但似乎有用。老实说，我建议使用 VBA 解决方案。

=TRANSPOSE(LEFT(MID(A1,FIND("~~~~~~",SUBSTITUTE(A1,"Fruit:","~~~~~~",ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"Fruit","")))/LEN("Fruit"))))),IFERROR(FIND(",",A1,FIND("~~~~~~",SUBSTITUTE(A1,"Fruit:","~~~~~~",ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"Fruit","")))/LEN("Fruit"))))))-1,LEN(A1)-FIND("~~~~~~",SUBSTITUTE(A1,"Fruit:","~~~~~~",ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"Fruit","")))/LEN("Fruit")))))+1)),IFERROR(FIND(",",MID(A1,FIND("~~~~~~",SUBSTITUTE(A1,"Fruit:","~~~~~~",ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"Fruit","")))/LEN("Fruit"))))),IFERROR(FIND(",",A1,FIND("~~~~~~",SUBSTITUTE(A1,"Fruit:","~~~~~~",ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"Fruit","")))/LEN("Fruit"))))))-1,LEN(A1)-FIND("~~~~~~",SUBSTITUTE(A1,"Fruit:","~~~~~~",ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"Fruit","")))/LEN("Fruit")))))+1))),1000)-1))

这是一个数组公式，您必须至少选择与“Fruit”实例数量相同的单元格。因此，如果一列中有四种Fruit，请选择四个单元格，输入此公式，然后按 ctrl+shift+enter 确认。如果您选择了额外的单元格，它会在最后开始返回错误，因此理论上您可以选择任何列所需的最大单元格数量并自动填充。

编辑：我意识到如果只有一个水果，它会在每个单元格中返回该水果。我认为这个公式无法解决这个问题。

它还假设水果名称不会超过 1000 个字符，并且“~~~~~”通常不会出现在文本中。

解释：其中 n 是单元格中的水果数量，公式使用 FIND/SUBSTITUTE 构建一个 n 元素数组，其中第一次出现的“Fruit”被替换为“~~~~~”，然后是第二次……等等。然后它使用 MID/FIND 提取文本。由于 Excel 没有“查找字符串 x 的第 n 次出现”公式，我们发现自己会做这样的疯狂事情。IFERROR 适用于最后一个 Fruit，因为我假设它后面可能没有逗号。

相关内容