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

Question 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

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

提取字符串开头后面的任何数字字符串；或者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

创建于正则表达式好友

Question 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 个，如果可能的话，可以添加更多行，将其中的数字更改为[#]下一个。

Answer