如何用Excel公式提取单元格中唯一的6位数字（禁用VBA）

问题场景 #

单元格里混着字符串和不同位数的数字，但只有一组连续的6位纯数字（数字位置、字符间距无固定规则），需要用公式提取这组6位数字，不能用VBA。
示例：A1内容为ab 12 abcd 1234 abcdef 123456 abcdefgh 12345678，提取结果为123456（输出到B1）

方案1：Excel 365/2021（动态数组版本） #

直接用以下公式，输入后回车即可：

=FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1," ","</s><s>"),"</s><s></s><s>","</s><s>")&"</s></t>","//s[string-length(.)=6 and translate(.,'0123456789','')='']")

原理

把单元格内容按空格拆分成XML节点，再通过XPath过滤出长度为6且全为数字的节点，因为只有一组符合条件的，直接返回结果。

方案2：旧版Excel（无动态数组） #

用以下数组公式，输入完成后按 Ctrl+Shift+Enter 确认（不能只按回车）：

=MID(A1,MATCH(1,--(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1)-5)),6))),0),6)

原理

1. 遍历单元格中所有可能的起始位置，取出连续6位字符并尝试转为数字
2. 用MATCH找到第一个能转成数字的6位字符起始位置
3. 从该位置开始提取6位字符，就是目标数字

注意事项 #

• 必须保证单元格内只有一组6位纯数字，否则公式可能返回不符合预期的结果
• 旧版公式一定要按数组公式的方式输入，否则无法生效

内容的提问来源于stack exchange，提问作者WOTEVER