提问人：WOTEVER 提问时间：3/16/2023 最后编辑：WarcupineWOTEVER 更新时间：8/11/2023 访问量：444
Excel：使用公式从字符串中提取 n 个数字的序列
Excel: Extract a sequence of n numbers from a string with a formula
问：
我在 excel 中有一个单元格，其中包含一个我需要提取的 6 位数字。该单元格可能包含刺痛或其他数字，但幸运的是没有其他 6 位数字。（间距、字数、位置编号可能会有所不同） 有没有办法只提取那个 6 位数字？
如果必须是一个公式，我不能为此使用 VBA
| 单元格 A1 | 单元格 B1 |
|---|---|
| ab 12 abcd 1234 abcdef 123456 abcdefgh 12345678 | 123456 |
答：
0赞
shrivallabha.redij
3/16/2023
#1
如果函数可用，则可以测试以下内容。这有点啰嗦。在最新版本的 Excel 中，也可以使用，可以进一步减少它。LETTEXTSPLIT
=LOOKUP(10^6,LET(x,TRIM(MID(SUBSTITUTE(" "&A2," ",REPT(" ",99)),99*SEQUENCE(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1),99)),IF(LEN(x)=6,IF(ISNUMBER(x+0),x+0,"a"),"a")))
评论
0赞
Jos Woolley
3/16/2023
如果所需的提取位于字符串的末尾，例如“abc 123456”怎么办？
0赞
shrivallabha.redij
3/19/2023
@JosWoolley：使用一个字符串进行测试，该字符串可以正确返回。之前添加空格的原因是为了简化公式的序列部分，不需要后期填充。我没有考虑过 OP 我有类似的东西并且仍然认为应该是结果的部分。ab 12 abcd 1234 abcdef 1234567 abcdefgh 123456123456OP123456 6859123456
0赞
user11222393
3/16/2023
#2
如果序列前后总是有一个空格：
=TEXTJOIN(" , ",TRUE,IF(LEN(TEXTSPLIT(A2," "))=6*ISNUMBER(VALUE(TEXTSPLIT(A2," "))),TEXTSPLIT(A2," "),""))
评论
0赞
Jos Woolley
3/16/2023
对于诸如“abc 2-june”之类的示例，将给出误报（至少对于 Excel 的英语版本）。
0赞
user11222393
3/16/2023
绝对正确
0赞
WOTEVER
3/16/2023
@user11222393，我喜欢这个，因为像我这样的外行更容易理解。我还没有得到任何误报，但会继续测试，看看我是否可以搞定哈哈哈。谢谢
1赞
Jos Woolley
3/16/2023
#3
测试解析的子字符串中的每个字符的数字性更安全。否则，可能会不合理地返回子字符串（如 2-june 或 1.2E04）。
=LET(
ζ,TEXTSPLIT(A1," "),
FILTER(ζ,(LEN(ζ)=6)*MMULT(SEQUENCE(,6,,0),1-ISERR(0+MID(ζ,SEQUENCE(6),1)))=6)
)
也就是说，以下内容也应该足够严格：
=LET(ζ,TEXTSPLIT(A1," "),FILTER(ζ,(LEN(ζ)=6)*(1-ISERR(0+(ζ&"**0")))))
尽管后者将返回小数，例如前者不会。1.2345
评论
0赞
WOTEVER
3/16/2023
哇@Jos你这么快！你的第一个选择是辉煌的。顶部的 textsplit col_delimiter需要设置为一个空格，这有效，因为数字总是以开头开头，后跟 1。谢谢！
0赞
David Leal
3/16/2023
@JosWoolley，我认为不能使用空字符串作为分隔符，也许你的意思是TEXTSPLIT" "
1赞
Jos Woolley
3/16/2023
哎呀。谢谢两位 - 打错字而不是.会修改。""" "
1赞
Jos Woolley
3/16/2023
@DavidLeal 请参阅我帖子中的第一句话。如果不连接到每个子字符串，则可能会收到误报，例如，如果包含“abc 2-june”（对于英语版本）或“abc 1.2E04”。"**0"A1
1赞
Jos Woolley
3/17/2023
@DavidLeal 等价于 ，即表示索引为 0 的科学记数法。不确定 in 是否依赖于区域设置，所以似乎是一个更安全的选择。catenation of 可确保字符串（如 become 和 so）不能再被解释为数值。这同样适用于恰好与格式化为科学记数法的数字相似的字符串，例如 .不确定它是否在任何地方都有记录 - 我是从别人那里学到的。"**0""E0""E""E0""**0""**0""12-june""12-june**0""1.2E04"
1赞
Harun24hr
3/16/2023
#4
我建议使用 .尝试-FILTERXML()
=FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s[string-length()=6][number()=.]")
"<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>"将构造一个有效的 XML 字符串以通过 FILTERXML（） 公式进行处理。- XPath 参数 //s 将返回所有节点，其中仅过滤 6 位节点，并且仅返回数字值。
[string-length()=6][number()=.]
评论
0赞
Jos Woolley
3/16/2023
取决于 OP 是否也想提取小数，例如，对于“abc 1.2345 def”，您的公式将返回“1.2345”。
0赞
Harun24hr
3/16/2023
@JosWoolley 如果OP不想要，则必须调整公式。
1赞
WOTEVER
3/16/2023
@Harun，我不想要小数，但通常我的数据不应该包含任何小数。此外，您还有一个额外的优势，即您的解决方案直接以数字形式呈现。谢谢！
1赞
David Leal
3/16/2023
#5
您可以尝试以下操作：
=LET(A,A2, s,6, n,TEXTSPLIT(A,TEXTSPLIT(A,CHAR(ROW(48:57)),,1)),
FILTER(n, LEN(n)=s))
这个想法取自 @JvdV 对这个问题的回答：只连接大写字母？。如果要将结果转换为数字，请将结果乘以 。我假设您正在寻找自然数，对于任何其他类型的数字，公式需要调整或采取不同的方法。FILTER1
由于@VBasic2008，可以使用代替 和 来实现更短的公式和更易于理解的公式：SEQUENCECHARROW
=LET(A,A2, s,6, n,TEXTSPLIT(A,TEXTSPLIT(A,SEQUENCE(10,,0),,1)),
FILTER(n, LEN(n)=s))
如果有多个数字满足该条件，它将返回多行。您可以将它们组合在一行中，用 将 （） 的输出括起来。FILTERfTEXTJOIN(",",,f)
评论
1赞
WOTEVER
3/16/2023
你的假设是绝对正确的，只有自然数。这也很好用。谢谢
1赞
VBasic2008
3/16/2023
就像我喜欢的那样！不过有一点：随着该功能的出现，许多“异国情调的组合”已经灭绝，即您可以简单地使用。SEQUENCECHAR(ROW(48:57))SEQUENCE(10,,0)
1赞
David Leal
3/17/2023
@VBasic2008 谢谢，我添加了您的建议和功劳。在这种情况下，使用just ，更短但只是一个字符更容易理解，但更重要的是，它更直观。SEQUENCE
评论