如何用Excel公式或VBA仅提取字符串中的Decimal(REAL)小数

嘿，我来帮你解决这个Excel提取问题！你需要从A列的字符串里精准揪出那个唯一的带小数的实数，完全忽略那些不含小数的整数，对吧？下面给你几个适配不同Excel版本的实用方案：

如果你用的是支持动态数组的新版Excel，直接用这个公式就行，不用复杂的数组按键：

=VALUE(FILTER(TEXTSPLIT(A1, " ", ",", ";", ":"), ISNUMBER(--TEXTSPLIT(A1, " ", ",", ";", ":")) * (--TEXTSPLIT(A1, " ", ",", ";", ":" ) <> INT(--TEXTSPLIT(A1, " ", ",", ";", ":")))))

逻辑说明： #

• TEXTSPLIT(A1, " ", ",", ";", ":")：把字符串按常见分隔符（空格、逗号、分号、冒号，你可以根据自己的实际字符串添加其他分隔符）拆分成单个元素
• ISNUMBER(...) * (...)：筛选出两个条件都满足的元素：一是能转成数字，二是这个数字不等于它自身的整数部分（也就是带小数的数）
• VALUE(...)：把筛选出来的文本格式数字转成真正的数值

要是你用的是Excel 2019及以前的版本，试试这个数组公式，输入完成后必须按Ctrl+Shift+Enter确认（不是直接回车）：

=LOOKUP(9.9E+307,--MID(A1,MIN(IF(ISNUMBER(--MID(A1,ROW($1:$100),1))*(MID(A1,ROW($1:$100),1)="."),ROW($1:$100)))-1,ROW($1:$100)))

逻辑说明： #

• 先定位字符串里小数点的位置，然后从小数点前的第一个数字开始，提取后续的所有字符（包括小数点和小数部分）
• LOOKUP(9.9E+307, ...)：利用LOOKUP找最大数值的特性，直接提取到那个带小数的数（因为只有一个，所以肯定是它）
• 要是你的字符串长度超过100字符，把ROW($1:$100)改成ROW($1:$200)就行

如果字符串的格式特别乱，分隔符五花八门，用正则表达式的自定义函数最靠谱：

1. 按Alt+F11打开VBA编辑器
2. 右键左侧的工程窗口，选择「插入」→「模块」
3. 粘贴下面的代码：

Function ExtractDecimal(strInput As String) As Double
    Dim regexObj As Object
    Set regexObj = CreateObject("VBScript.RegExp")
    ' 匹配带小数的数字，支持整数部分为0的情况（比如0.123）和多位整数（比如1234.56）
    regexObj.Pattern = "\d+\.\d+"
    regexObj.Global = False ' 因为只有一个目标数，不需要全局匹配
    If regexObj.Test(strInput) Then
        ExtractDecimal = CDbl(regexObj.Execute(strInput)(0).Value)
    Else
        ExtractDecimal = Empty ' 没找到带小数的数时返回空
    End If
End Function

4. 回到Excel，在B1单元格输入=ExtractDecimal(A1)，下拉填充就能用了

小提示： #

如果你的区域是用逗号做小数分隔符（比如欧洲地区的格式），把正则里的\d+\.\d+改成\d+,\d+就行。

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