如何使用Excel公式从带单位混合文本中提取仅含数字与小数点的数值

• 逻辑分支无效：原公式里IF(".", 分支1, 分支2)的判断参数是非空文本"."，Excel在布尔判定时非空文本永远等价于TRUE，分支2根本不会被执行，属于无效判断
• 截断位置偏移错误：原逻辑是匹配第一个无法转成数字的字符的位置索引，正确截断长度应该是这个索引值减1；但原公式在两个分支里都给索引值加了2的偏移，会把后续单位文本的内容也截取进来，无法得到纯数值

全版本兼容写法（支持Excel 2007及以上、WPS表格） #

直接在B2对应的结果单元格输入公式：

=LOOKUP(9E+307,--LEFT(B2,ROW($1:$99)))

• 旧版Excel（2019及更早）输入完成后，需要按下Ctrl+Shift+Enter组合键触发数组计算，公式会被自动包裹大括号
• Microsoft 365/2021及以上版本直接按下回车即可运行

公式运行逻辑说明 #

• ROW($1:$99)会生成1到99的连续序列，搭配LEFT(B2, ...)会依次截取B2单元格从第1位到第99位的文本片段
• 两个负号--会把截取到的文本片段转成数值，遇到字母、空格这类无法转成数值的内容时，会返回错误值
• LOOKUP(9E+307, ...)会在生成的数值序列里，定位最后一个有效数值——也就是文本开头连续的数字+小数点组成的纯数值片段，自动忽略后面所有带单位的文本内容

把提供的所有测试数据代入公式，输出结果和预期完全一致：

• 31.55c/kWh → 31.55
• 49.66c/kWh → 49.66
• 12.84 cents/kWh → 12.84
• 19.24 cents/kWh → 19.24
• 15.30 cents/day → 15.3
• 15.30 cents/day → 15.3
• 12 cents/kWh data → 12
• 7 cents/kWh data →7

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