Excel中英文及数字字符的提取技巧.pdfVIP

中英文及数字字符的提取技巧 从混排文字中提取连续数字 如图 1-1 所示，A 列中有一些产品规格型号，其中包含了连续数字，且数字均位于字符串 的末尾，数字之后没有其他字符跟随。要从这样的文本字符串中提取连续数字，可在B2 单元格 中输入以下公式并向下填充： =-LOOKUP(9.9E+307,--RIGHT(A2,ROW($1:$99))) 图 1-1 提取连续数字 公式解析： 公式通过依次提取字符串右侧的连续 1～n 个字符，形成一个数组，然后通过 LOOKUP 函 数可以查找其中最大的数值的功能，找出其中包含连续数字最多的那个数。9.9E+307 是科学计 数法的表示方式，表示9.9 乘以10 的307 次方，这是Excel 里面超级大的一个数，以这个数作 为LOOKUP 函数的查询对象，只要数组中的数值小于它就能被查询得到。 事实上，这个公式还有不少局限性，例如一些数学符号会对结果产生影响，超过 15 位有效 数字的连续数字也会不能被正确提取等等。 假设目标字符串的数字位于字符串中部，前后都有非数字字符与其相连，如图 1-2 中A 列 的型号所示。对于这种情况，在原公式基础上进行一些修改： =LOOKUP(9.9E+307,--MID(A2,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A21234567890)), ROW($1:$99))) 1 图 1-2 数字位于字符串中部 这个公式通过 FIND 函数来定位首个数字出现的位置，然后通过MID 函数来依次取出连续 的1～n 个字符。再参照前面公式的思路得到最后结果。 从混排文字中提取连续字母 如图 1-3 表格所示，A 列中有一些车牌编号，其中包含了连续英文字母，且字母均位于字 符串的中部。不考虑字母后跟随数字的特殊性，要从这样的文本字符串中提取连续字母，可在 B2 单元格中输入以下数组公式并向下填充： {=MID(A2,MATCH(z,TEXT(CODE(MID(UPPER(A2),ROW($1:$99),1))-64,[26];;z) ,0),COUNT(N(INDIRECT(MID(A2,ROW($1:$99),1)1))))} 图 1-3 提取连续英文字母 公式解析： 此公式主体结构是通过 MID 函数取出字符串中的连续字符，其中包含了两种鉴别英文字母 的方法。 CODE(MID(UPPER(A2),ROW($1:$99),1))可以返回字符串中每个字符的ASCII 代码，其中 在65～90 之间的就是英文大写字母的ASCII 码。 INDIRECT(MID(A2,ROW($1:$99),1)1)部分则是利用了字母+数字的形式在 Excel 中是 2 作为单元格地址的表示形式，通过判断能否正确引用这些单元格地址达到鉴别是否包含字母的目 的。 从混排文字中提取连续中文 如图 1-4 表格所示，A 列中存放了一些文本字符串，其中包含了连续中文字符，且中文均 位于字符串的中部。不考虑中文后跟随数字的特殊性，要从这样的文本字符串中提取连续中文， 可在B2 单元格中输入以下数组公式并向下填充： {=MID(A2,MATCH(1,1/(MIDB(A2,ROW($1:$99),1)MID(A2,ROW($1:$99),1)),0),LEN B(A2)-LEN(A2))} 图 1-4 提取连续中文字符 公式解析： 此公式整体结构与技巧 1-2 中的公式相同，所不同的是鉴别字符的方式。在这个公式中用于 区分中文字符和其他字符的方式是通过判断字符是否为双字节字符，在目标字符串中不包含其他 全角字符的情况下可以使用这种判别方式。 本篇文章节选自 《Excel 2007 实战技巧精粹》 ISBN ：97871152183