如何从字符串中提取数字并用逗号（或其他分隔符）分隔它们（替换、替换、擦除、isnumber、移除、删除......）

Question

我需要从 Excel 中的字符串中提取数字，并且数字需要用逗号分隔，如第 3 行所示（或任何其他分隔符）。

我可以通过以下方式提取第一行的数字：

=SUMPRODUCT(MID(0&A1, LARGE(INDEX(ISNUMBER(--MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1)) * ROW(INDIRECT("1:"&LEN(A1))), 0), ROW(INDIRECT("1:"&LEN(A1))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A1)))/10)´´´

或者在第 2 行中：

=SUM(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)

然而，我需要以某种方式将它们分开。

笔记：我有多达 200 种不同的数字和汽车品牌组合，这导致简单的替代函数达到极限。如果可能的话，我更喜欢基于公式的解决方案，而不需要隐藏的中间列（当然不需要手动点击鼠标）。我的 VBA 技能较低，但我愿意接受新的挑战！

Answer 1

编辑：
似乎真实的数据比最初提交的样本更复杂。最简单的编程方法是使用正则表达式来提取数字

• 提取字符串开头后面的任何数字字符串；或者comma可选地，后面跟着一个或多个spaces

編輯2我读完模板后将分发转换为后期绑定

Option Explicit
Function extrNums(cellRef) As String
    Dim RE As Object, MC As Object, M As Object
    Dim sTemp As Variant
    Const sPat As String = "(?:^|,\s*)(\d+)\b"
    
Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .MultiLine = True
    .Pattern = sPat
    If .Test(cellRef) Then
        Set MC = .Execute(cellRef)
        For Each M In MC
            sTemp = sTemp & ", " & M.SubMatches(0)
        Next M
    End If
End With

extrNums = Mid(sTemp, 3)

End Function

正则表达式解释
提取数字
（？：^|，\s*）（\d+）\b

选项：^$ 匹配换行符

• 匹配下面的正则表达式 (?:^|,\s*)
  • 匹配此替代方案 ^
    • 断言字符串开头的位置 ^
  • 或者匹配此替代方案 ,\s*
    • 逐字匹配字符“，” ,
    • 匹配单个“空白字符” \s*
      • 在零次至无限次之间，尽可能多次，根据需要回馈（贪婪） *
• 匹配下面的正则表达式，并将其匹配捕获到反向引用编号 1 中 (\d+)
  • 匹配单个“数字”字符 \d+
    • 一次至无限次之间，尽可能多次，根据需要回馈（贪婪） +
• 断言字边界处的位置 \b

创建于正则表达式好友

Answer 2

使用 Office 365 或 2019 Excel 我们可以使用 TEXTJOIN 和 FILTERXML。

=TEXTJOIN(",",,FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,","," ")," ","</s><s>")&"</s></t>","//s[number()=.]"))

FILTERXML 解析看起来像 xml 的虚构字符串。我们用空格和逗号替换，然后</s><s>仅返回数字节点。

TEXTJOIN 获取返回的数组并在每个元素之间添加逗号。

没有文本连接，我们也可以连接许多：

IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,","," "),"  "," ")," ","</s><s>")&"</s></t>","//s[number()=.][1]"),"")

添加[1]到 FILTERXML 的是要返回哪个节点。所以我们可以将一堆串在一起：

=IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,","," "),"  "," ")," ","</s><s>")&"</s></t>","//s[number()=.][1]"),"")&
IFERROR(", "&FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,","," "),"  "," ")," ","</s><s>")&"</s></t>","//s[number()=.][2]"),"")&
IFERROR(", "&FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,","," "),"  "," ")," ","</s><s>")&"</s></t>","//s[number()=.][3]"),"")&
IFERROR(", "&FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,","," "),"  "," ")," ","</s><s>")&"</s></t>","//s[number()=.][4]"),"")&
IFERROR(", "&FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,","," "),"  "," ")," ","</s><s>")&"</s></t>","//s[number()=.][5]"),"")&
IFERROR(", "&FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,","," "),"  "," ")," ","</s><s>")&"</s></t>","//s[number()=.][6]"),"")

最多可以达到 6 个，如果可能的话，可以添加更多行，将其中的数字更改为[#]下一个。