如何从Excel公式中提取行号至其他单元格？MID函数适配性不佳

提取第一个行号区域 #

针对公式中第一个类似X:X的行号区域，使用以下通用公式即可适配1位、2位或3位行号：

=MID(FORMULATEXT(A1),SEARCH("!",FORMULATEXT(A1))+1,SEARCH(",",FORMULATEXT(A1),SEARCH("!",FORMULATEXT(A1)))-SEARCH("!",FORMULATEXT(A1))-1)

原理：

• FORMULATEXT(A1)：获取目标单元格的公式文本
• SEARCH("!",...)：定位公式里第一个!的位置，以此作为截取的起始偏移点
• SEARCH(",",...,SEARCH("!",...))：从!的位置开始查找第一个逗号，确定截取的结束边界
• 通过两个位置的差值计算截取长度，自动适配不同位数的行号

提取所有行号区域 #

如果需要提取公式中所有的行号区域（比如示例里的17:17和1:1），可以用XML筛选的方式实现：

=TEXTJOIN(", ",TRUE,FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(FORMULATEXT(A1),"!","</s><s>"),",","</s><s>")&"</s></t>","//s[contains(.,':')]"))

原理：

• 先用SUBSTITUTE把公式中的!和逗号替换为XML节点标签，将文本拆分为独立片段
• FILTERXML筛选出包含冒号的片段（即行号区域）
• TEXTJOIN把所有符合条件的行号区域合并为一个用逗号分隔的字符串

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