Excel提取数字-魔法公式

公式：

{=-LOOKUP(1,-MID(A1,MIN(FIND(ROW($1:$10)-1,A1&1/17)),ROW($1:$9)))}

说明：

①最外层的大括号表示此公式为数组公式，输入公式时不需要输入大括号{}，公式输入后按下键盘：Ctrl+Shift+Enter，即可将公式变为数组公式；

②公式用于提取A1单元格中出现的首个数字，支持小数点，不支持负数符号提取；

③此公式中设定提取的数字不超过9位，由ROW($1:$9)中的9设定，可以按需修改；

详细解释：

假设A1单元格中的内容为：例：报价0.36万元/L

①ROW($1:$10)-1

结果：{0,1,2,3,4,5,6,7,8,9}

说明：ROW(reference)函数用于获取reference所引用区域中每一行的行号，形成行号数组返回，一般用于构建等差数列，例如：ROW($1:$5)生成{1,2,3,4,5}，ROW($1:$5)*2+3生成{5,7,9,11,13}，此处生成该数组用于从A1单元格内容中查找所有数字字符。

②A1&1/17

结果：例：报价0.36万元/L0.0588235294117647

说明：将1/17的计算结果添加到A1单元格内容的后面，1/17的计算结果为无线循环小数，在Excel中转化为文本表示为0.0588235294117647，可发现这其中完整包含了0到9的数字，这样做的目的是使FIND函数依次查找所有数据时不会出错，进而在MIN函数中获取到最先出现数字的位置。

③FIND(ROW($1:$10)-1,A1&1/17)

结果：{5,25,19,7,24,16,8,27,17,23}

说明：FIND(find_text,within_text,start_num)函数会从within_text的第start_num个字符开始查找find_text，返回find_text首次出现的位置，当找不到find_text时，返回无效值#VALUE!；start_num参数可以忽略，当该参数忽略时，会从within_text的第一个字符开始查找。

这里依次在A1单元格内容中查找0到9数字字符的位置，形成位置数组。由于A1单元格内容添加了包含所有数字字符的1/17后缀，即便A1单元格内容中不含有某个数字字符，该函数也可以顺利生成位置数组。

④MIN(FIND(ROW($1:$10)-1,A1&1/17))

结果：5

说明：MIN(number1,number2,...)函数返回所有参数中最小的值。

这里用于获取A1单元格内容中首个数字的位置，由于A1单元格内容添加了1/17后缀，如果A1单元格内容不包含数字，该位置为后缀部分的首位，值为A1单元格内容的长度+1。

⑤ROW($1:$9)

结果：{1,2,3,4,5,6,7,8,9}

说明：生成该数组用于从A1单元格内容中提取相应长度的内容。

这里设置为9，即可识别出的数值长度最长为9，如果需要识别更长的数值，可以修改该值。比如想最多识别20位数值，可改为ROW($1:$20)

⑥MID(A1,MIN(FIND(ROW($1:$10)-1,A1&1/17)),ROW($1:$9))

结果：{0,0.,0.3,0.36,0.36万,0.36万元,0.36万元/,0.36万元/L,0.36万元/L}

说明：MID(text,start_num,num_chars)函数返回text自第start_num个字符开始的num_chars个字符所构成的字符串，如果num_chars超出了text的范围，则忽略超出的部分。

从A1内容中出现的第一个数字字符开始，依次提取1个至9个字符的字符串，构成字符串数组。对于MID函数来说，如果第三个参数的字符个数超出原字符串范围，则忽略超出的部分，所以可以看到数组中后两个元素相同。
⑦-MID(A1,MIN(FIND(ROW($1:$10)-1,A1&1/17)),ROW($1:$9))
结果：{0,0,-0.3,-0.36,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}

说明：添加负号使得数组内的字符串都被强制转化为数字并取负，这就导致两个结果：全部为数字字符的字符串会转化为整数并取负，小于等于0；包含其他字符的字符串运算失败，变为无效值。

这里的结果是为了便于下面的解释而修正的结果，实际上如果数组中包含一个无效值，则整个数组都变为无效，也即真实的结果是：#VALUE!。

⑧LOOKUP(1,-MID(A1,MIN(FIND(ROW($1:$10)-1,A1&1/17)),ROW($1:$9)))

结果：-0.36

说明：LOOKUP(lookup_value,array)函数从array中检索lookup_value，并返回匹配的值。

该函数具有以下特性：
Ⓐ忽略array中的无效元素。
Ⓑ预设array中的元素升序排列，以此为前提使用二分查找法确定结果（如果array并非升序排列，则可能导致结果错误）。
Ⓒ如果lookup_value的值小于array中的最小值，函数返回 #N/A 错误值；除此之外，函数会返回数组中小于或等于lookup_value的最大值。

LOOKUP函数具体请参考：https://support.microsoft.com/zh-cn/office/lookup-函数-446d94af-663b-451d-8251-369d5e3864cb#bmarray_form

基于以上特征，该函数的处理流程为：
Ⓐ取得array的中间元素，与lookup_value比较。
假设array有n个元素，则中间元素为第⌈n/2⌉个元素。符号⌈⌉表示向上取整，举例说明：
n为奇数5时，⌈n/2⌉=⌈2.5⌉=3，第3个元素就是中间元素；
n为偶数8时，⌈n/2⌉=⌈4⌉=4，第4个元素就是中间元素。
Ⓑ如果lookup_value与中间元素相等，返回中间元素。
Ⓒ如果array中只剩下一个元素，如果lookup_value比中间元素小，返回 #N/A 错误值；反之返回该元素。
Ⓓ如果lookup_value比中间元素小，将之前的元素组成数组作为array，转到Ⓐ步骤继续执行。
Ⓔ如果lookup_value比中间元素大，将之后元素组成数组作为array，转到Ⓐ步骤继续执行。

由以上分析可知，如果lookup_value大于array中所有元素，则结果一定是array中最后一个有效元素，也即最长的数字字符串。
⑨-LOOKUP(1,-MID(A1,MIN(FIND(ROW($1:$10)-1,A1&1/17)),ROW($1:$9)))
结果：0.36
说明：添加负数符号修正结果。
{=-LOOKUP(1,-MID(A1,MIN(FIND(ROW($1:$10)-1,A1&1/17)),ROW($1:$17)))}
{=-LOOKUP(1,-MID(A1,MIN(FIND(ROW($1:$10)-1,A1&1/17)),ROW(INDIRECT("1:"&LEN(A1)))))}

reference：https://www.zhihu.com/question/268335545/answer/944153347