3个Excel万能公式，第一个既能提取字母又能提取数字

  文 前 推 荐   

利用单双字节特点提取汉字

快速提取汉字拼音首字母

在多个数字中提取第一个数字的万能公式

提取多个分隔符之间的字符

编按：

3个提取单串数字和字母的万能公式，适合整个字符串中只有一串数字或者字母的提取。第一个公式很牛，通用，既可以提取数字，也可以提取字母。

小窝今天分享3个一串数字和字母的万能提取公式。

1.  除开数字或字母外就是汉字

字符串特点：

（1）数字或字母只有一串，其他都是汉字

（2）数字或字母在字符串中的位置不确定、位数不确定

这里的数字包含除日期、时间外的所有数字。

数字和字母万能提取公式：

=MIDB(字符串,SEARCHB("?",字符串),LEN(字符串)*2-LENB(字符串))

应用例举：

（1）提取任意位置的数字和字母

从下方数据中提取数字，数字位置不固定，长度不固定。

=MIDB(A19,SEARCHB("?",A19),LEN(A19)*2-LENB(A19))

（2）提取位于左侧或右侧的数字（或字母）

譬如下方，数字与字母都位于右侧，则可以用简化公式提取：

=RIGHT(A3,LEN(A3)*2-LENB(A3))

=RIGHT(A11,LEN(A11)*2-LENB(A11))

说明：

利用字符的单双字节区分计算字符个数、提取位置。

LENB函数把每个汉字计为2个字节，数字和单字节字母计为1个字节；LEN函数把每位汉字、字母、数字都计为1位。

如此：

汉字个数=LENB(字符串)-LEN(字符串)

数字（或字母）个数=LEN(字符串)-( LENB(字符串)-LEN(字符串))= LEN(字符串)*2- LENB(字符串)

SEARCHB函数，可查找某字符在字符串中的字节位置；通配符“？”在本处代表任何一个单字节数字或字母。

2.  除开数字或字母外还有其他单字节字符

字符串特点：

（1）数字或字母只有一串

（2）还有其他单字节字符

（3）数字或字母在字符串中的位置不确定、位数不确定

这里的数字不包含负数、分数、日期和时间。

数字万能提取公式：

=LOOKUP(9E+307,--MID(字符串,MATCH(1,MID(字符串,ROW($1:$50),1)^0,0),ROW($1:$50)))

应用例举：

下图中数字只有一串，但存在字母等单字节字符。

=LOOKUP(9E+307,--MID(A27,MATCH(1,MID(A27,ROW($1:$50),1)^0,0),ROW($1:$50)))

说明：

巧妙利用0次方幂运算，将提取出来的每个数字都化为1（非数字变成错误值），然后用MATCH查找1的位置作为MID的开始提取位置；最后用LOOKUP在一堆提取值中查找极大值，得到最大的一个数字。

字母万能提取公式：

低版本

=CONCAT(IF((MID(字符串,ROW($1:$50),1)>="A")*(MID(字符串,ROW($1:$50),1)<="Z"),MID(字符串,ROW($1:$50),1),""))

高版本优化后

=LET(A_,MID(字符串,ROW($1:$50),1),CONCAT(IF((A_>="A")*(A_<="Z"),A_,"")))

应用例举：

下方图中字母只有一串，但还存在数字等单字节字符。

=LET(A_,MID(A35,ROW($1:$50),1),CONCAT(IF((A_>="A")*(A_<="Z"),A_,"")))

说明：

用提取出来的每个字符与字母A和Z比较，只有大与等于A且小于等于Z的保留，其他都变成空。