EXCEL从字符串中截取数字(规格中取直径、长、宽、高)

由于要计算毛坯重量，要从产品规格中截取零件的直径、长、宽和高。从PDM中导出的数据中发现，设计院设置的规格不是很规范，研究方法如下：

1、外形的判断

=IF(TRIM(OR(MID(F2,1,1)="Ф",MID(F2,1,1)="∅",MID(F2,1,1)="Ø",MID(F2,1,1)="φ")),"圆料",IF(LEN(F2)-LEN(SUBSTITUTE(F2,"×",""))=2,"方料",""))

圆料是通过判断首字母是否带有直径符号，用MID(F2,1,1)；
方料规格是长×宽×高，所有是否有2个×符号为判断依据，用IF(LEN(F2)-LEN(SUBSTITUTE(F2,“×”,“”))=2，将×替换成空值，然后用原字符串长度减替换后的字符串长度。
结果如下：

2、圆料直径

=IF(G2="圆料",MID(F2,2,SEARCH("×",F2,1)-2),"")

SEARCH(“×”,F2,1)-2)是查找×在字符串的位置，-2是因为用MID从第二位开始查找。
结果如下：

3、长度

=IFNA(LOOKUP(9^99,--LEFT(MID(F2,FIND(CHAR(1),SUBSTITUTE(F2,"×",CHAR(1),LEN(F2)-LEN(SUBSTITUTE(F2,"×",""))))+1,99),ROW($1:$99))),"")

圆料的长度是×后的数字，但原数据中有的规格后面还加了说明
方料的长度是第二个×后的数字，同样有的也加了说明

参考https://blog.51cto.com/snowdream/1418455和http://www.nkmyjy.com/myjy/item_12909016_526765.html
说明：

 LEN(F22)-LEN(SUBSTITUTE(F22,"×",""))

：判断有几个×。

SUBSTITUTE(F22,"×",CHAR(1),LEN(F22)-LEN(SUBSTITUTE(F22,"×","")))

：用于替换最后一个×，CHAR(1)也可用任何一个特殊符号代替。

MID(F22,FIND(CHAR(1),SUBSTITUTE(F22,"×",CHAR(1),LEN(F22)-LEN(SUBSTITUTE(F22,"×",""))))+1,99)

截取从CHAR(1)+1后的字符，99只是虚数。

=IFNA(LOOKUP(9^99,--LEFT(MID(F22,FIND(CHAR(1),SUBSTITUTE(F22,"×",CHAR(1),LEN(F22)-LEN(SUBSTITUTE(F22,"×",""))))+1,99),ROW($1:$99))),"")

ROW($1:$99)是取1-99的行号，这里作用是程序生成数组{1，2，3，4，…99}
LEFT（字符串，ROW($1:$99)）的是生成{a,ab,abc,abcd…}这样的数组
“-LEFT”，数字生成负数，字符则生成错误码
“–LEFT”负负得正。
LOOKUP(9^99，字符串） LOOKUP 的原理是找不到与“查找值”相等的数，它会使用“查找区域”中小于或等于 “查找值”的最大值。

其中9^99也是个虚数

IFNA的作用是有无效值则为空
最终结果如下：

注：后发现圆料中也有两个×的值，会造成取值错误，增加判断，当是圆料时，取第一个×后的数字

=IF(G2="方料",LOOKUP(9^99,--LEFT(MID(F2,FIND(CHAR(1),SUBSTITUTE(F2,"×",CHAR(1),LEN(F2)-LEN(SUBSTITUTE(F2,"×",""))))+1,99),ROW($1:$99))),IF(G2="圆料",LOOKUP(9^99,--LEFT(MID(F2,FIND(CHAR(1),SUBSTITUTE(F2,"×",CHAR(1),1))+1,99),ROW($1:$99))),""))

4、宽度

=IF(G2="方料",MID(F2,FIND("×",F2,1)+1,FIND(CHAR(1),SUBSTITUTE(F2,"×",CHAR(1),LEN(F2)-LEN(SUBSTITUTE(F2,"×",""))))-FIND("×",F2,1)-1),"")

这个用MID是从方料中截取两个×之间的值
结果如下

5、高度

=IF(G2="方料",MID(F2,1,FIND("×",F2,1)-1),"")

用MID从方料中截取第一个×前的数值
结果如下：