主页 / computer / 问题 / 1661247
Accepted
MsGISRocker
Asked: 2021-07-08 09:17:24 +0800 CST

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

  • 772

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

在此处输入图像描述

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

=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 技能很低，但我愿意迎接新的挑战！

microsoft-excel find-and-replace

2 个回答

  1. 借助 Office 365 或 2019 Excel，我们可以使用 TEXTJOIN 和 FILTERXML。

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

    FILTERXML 解析看起来像 xml 的组合字符串。我们用空格和逗号代替</s><s>then 只返回数字节点。

    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 行，将数字中的数字更改为[#]下一个。

    在此处输入图像描述

    • 5
  2. Best Answer

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

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

    Edit2在我阅读模板后转换为后期绑定将分发

    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

    选项： ^$ 匹配换行符

    使用RegexBuddy创建

    • 2

相关问题