怎么样做电脑上的字幕表(120 Excel单元格的提取字母的通用技巧)
不管是做生产计划工作,还是其他职能部门工作,有时候都会遇到需要提取单元格中的指定内容的需求,如提取数字、字母、汉字、特殊字符等。昨天已经分享了提取数字的通用技巧。
“=CONCAT(IFERROR(--MID(B2,SEQUENCE(LEN(B2)),1),""))”
今天继续分享提取字母的通用技巧。提取字母和提取数字的思路都差不多,主要就是:分开→判断→过滤→合并。为了能够设计通用的函数公式,如下较中,字母分别在前面、后面、中间,也就是没有规律的出现。有大写的、有小写的等;
判断长度并分拆
步骤1:录入函数取字=LEN(B2),
判断出单元格中的字符长度为15,
步骤2:录入函数:=SEQUENCE(LEN(B2))
生成一个15个连续数字的数组{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15};
步骤3:录入函数:=MID(B2,SEQUENCE(LEN(B2)),1)
配合MID函数,分别提取单元格内的第1个字符、第2个字符一直到第15个字符。效果如下图所示:
把字母转换成ASCII码
提取字母前需要判断满足字母的条件是什么?其中一个思路就是用ASCII码来验证。什么是ASCII码呢?
ASCII(AmericanStandardCodeforInformationInterchange):美国信息交换标准代码是基于拉丁字母的一套电脑编码系统,主要用于显示现代英语和其他西欧语言。它是最通用的信息交换标准,并等同于国际标准ISO/IEC646。ASCII第一次以规范标准的类型发表是在1967年,最后一次更新则是在1986年,到目前为止共定义了128个字符
其中大写A到Z的对应数字:65到90;
小写a到z的对应数字是:97到122;
知道了这些信息就可以录入函数
=CODE(MID(B2,SEQUENCE(LEN(B2)),1)),就可以把字母转换成对应的ASCII码,效果如下图所示:
根据ASCII码的数字判断字母
根据这两个条件:大写A到Z的对应数字:65到90;
小写a到z的对应数字是:97到122;来写IF判断,录入函数:
如何用函数来表达上面的两个条件呢?可以用筛选函数来实现,
筛选的条件就需要符号布尔逻辑。
并的用乘号:如同时满足两个条件:大于64和小于91;
或者的用加号:符合65到90或者97到122
转成FILTER函数的话就是:
=LET(A,CODE(MID(B2,SEQUENCE(LEN(B2)),1)),FILTER(A,(A>64)*(A<91)+(A>96)*(A<123)))
函数释义:
提前把分拆后的结果定义为A,A代表分拆后的独立单元格数组,然后执行:
FILTER(A,(A>64)*(A<91)+(A>96)*(A<123)),注意观察乘号和加号,得到如下图所示结果:
转换ASCII码并合并
上面的结果还是一个ASCII码,需要再次转换,记住两个函数就可以相互转换了,一个CODE,一个是CHAR,录入函数:
=CHAR(LET(A,CODE(MID(B2,SEQUENCE(LEN(B2)),1)),FILTER(A,(A>64)*(A<91)+(A>96)*(A<123))))
把ASCII码转成了字母了,再进行合并,录入函数:
=CONCAT(CHAR(LET(A,CODE(MID(B2,SEQUENCE(LEN(B2)),1)),FILTER(A,(A>64)*(A<91)+(A>96)*(A<123)))))
就可以把字母提取出来了,效果如下图所示:
最后总结:
Excel2021或365版本通用公式:
=CONCAT(CHAR(LET(A,CODE(MID(B2,SEQUENCE(LEN(B2)),1)),FILTER(A,(A>64)*(A<91)+(A>96)*(A<123)))))
WPS版本通用公式:
由于WPS不支持LET函数,所以公式超级长:
=CONCAT(CHAR(FILTER(CODE(MID(B2,SEQUENCE(LEN(B2)),1)),(CODE(MID(B2,SEQUENCE(LEN(B2)),1))>64)*(CODE(MID(B2,SEQUENCE(LEN(B2)),1))<91)+(CODE(MID(B2,SEQUENCE(LEN(B2)),1))>96)*(CODE(MID(B2,SEQUENCE(LEN(B2)),1))<123))))
效果如下图:
我是古哥:
从事制造行业18年,在企业运营、供应链管理、智能制造系统等方面具有丰富的实战经验。企业智能化,柔性化计划运营管理专家,擅长通过企业流程优化规范,企业管理、导入计划运营提升企业效率;对提高企业准时交货率,降低企业库存,输出智能制造人才有丰富的经验。学习PMC生产计划,关注古哥计划!
欢迎私信投稿生产计划的相关问题,古老师将会把解决方案更新到公众号文章上面。