提取单元格内的数字和英文字母（单元格提取英文字母的通用公式）

在前二天的文章中分别分享如何提取数字和文本（中文），用的方法也是找规律，和分拆单元格中的字符，再合并的思路；

​分开单元格中的文本后，判断数字和文本就比较容易，其中数字通过运算就可以判断，文本通过对比最小汉字“吖”来判断，英文就相对比较复杂了，因为英文分大写、小写等，此时需要用到一个技巧，与“ACSII”码来对比，在返回的码数字范围内就是英文；

​除了这个方法还可以用高版本的Excel中的Power/Query的自定义列，录入专用函数来提取，优点是方便，缺点是需要额外打开新的界面；

​在学习以上的技巧前，还是要重点说下“找规律”，因为有些数字加字母的单元格会有显示的规律，配合一些简单文本函数就可以快速提取；；

01 英文加数字组合

使用限制：英文在前前面，长度不固定；数字在后面，长度固定

提取思路：判断总长度LEN函数，减去数字的固定长度，剩下的长度就是英文，用LEFT函数提取；

使用公式：C6=LEFT(B6,LEN(B6)-8)

02 ACSII码说明

​上面只是一些简单的有规律的提取，实际无规律的还是占大多数，所以还是用分开→判断→合并的思路来提取，在说这个思路前学习一下什么是“ACSII码 ”;

​ASCII (American Standard Code for Information Interchange)：美国信息交换标准代码是基于拉丁字母的一套电脑编码系统，主要用于显示现代英语和其他西欧语言。到目前为止共定义了128个字符

​ 通过下图就可以看明白对应的关系：

​Excel中对应的函数就是CODE，函数只有1个参数，就是返回的代码对应于本机所使用的字符集，举例说明：=CODE("A"),返回的数字就是65，与之对应的函数CHAR， 可将从其他类型计算机上的文件中获取的代码页数字转换为字符，就是=CHAR(65),返回结果就是 A;

03 ​英文提取的通用公式

​学习了ASCII的定义后，就简单了，我们还是用以下步骤来分别写函数。

1. 判断文本长度：=LEN(B30)

2. 生产连续数字：=SEQUENCE(D30)

3. 分开文本为单个单元格：=MID(B30,E30#,1)

4. 返回对应的ASCII码：=CODE(F30#)

5. 判断英文开始范围： =G30#>64

6. 判断英文结束范围：=IF(H30#,IF(G30#<123,F30#,""),"")

7. 合并单元格：=CONCAT(I30#)

8. 合并公式：=CONCAT(IF(CODE(MID(B19,SEQUENCE(LEN(B19)),1))>64,IF(CODE(MID(B19,SEQUENCE(LEN(B19)),1))<123,MID(B19,SEQUENCE(LEN(B19)),1),""),""))

上面的公式还是有一点缺陷的，当出现对应特殊符号在91到96范围的时候会出现BUG,所以，还不能算是通用公式，我们把公式更改一下，用VLOOKUP的方法来解决；

​04 英文提取的通用公式-VLOOKUP

​前面第1步到第4步一样，我们从第5步开始，就是CODE后面更改公式为如下：

​5.提前录入数字对应的英文字母：=VLOOKUP(G58#,$L$45:$M$96,2,0)

​6.屏蔽错误返回空：=IFERROR(H58#,"")

​7.合并单元格：=CONCAT(I58#)

​8.合并公式：=CONCAT(IFERROR(VLOOKUP(CODE(MID(B47,SEQUENCE(LEN(B47)),1)),$L$45:$M$96,2,0),""))

​上面的公式还不是通用的，因为额外占用一列，此时只需要把VLOOKUP的引用范围转成内置数组就成为通用公式了：

=CONCAT(IFERROR(VLOOKUP(CODE(MID(B47,SEQUENCE(LEN(B47)),1)),{65,"A";66,"B";67,"C";68,"D";69,"E";70,"F";71,"G";72,"H";73,"I";74,"J";75,"K";76,"L";77,"M";78,"N";79,"O";80,"P";81,"Q";82,"R";83,"S";84,"T";85,"U";86,"V";87,"W";88,"X";89,"Y";90,"Z";97,"a";98,"b";99,"c";100,"d";101,"e";102,"f";103,"g";104,"h";105,"i";106,"j";107,"k";108,"l";109,"m";110,"n";111,"o";112,"p";113,"q";114,"r";115,"s";116,"t";117,"u";118,"v";119,"w";120,"x";121,"y";122,"z"},2,0),""))

​使用的时候只需要把引用B47更改为任意需要提取的单元格就可以了；

我是古哥：

从事制造行业18年，在企业运营、供应链管理、智能制造系统等方面具有丰富的实战经验。企业智能化，柔性化计划运营管理专家，擅长通过企业流程优化规范，企业管理、导入计划运营提升企业效率；对提高企业准时交货率，降低企业库存，输出智能制造人才有丰富的经验。学习PMC生产计划，关注古哥计划！