在平时工作中,我们经常会遇到提取数据的情况,例如提取身份证号码中的出生日期、性别等,相信大家一定都学过(不会的同学建议翻阅往期分享)。
但另一种常见的数据提取,如从用逗号等符号分隔的长字符串中提取出指定位置的部分字符,用从身份证号码中提取出生日期的公式是没办法做到的,因为要提取的部分字符在长字符串中的字符顺序位不固定,没办法用MID函数来截取。
例如下图所示的表格,从地址中提取省、市、县(县级市或区)。
如下图所示,一般情况下,我们首先想到的方法是借助FIND函数来实现,如要提取“市”,公式如下:
=MID(A2,FIND(',',A2)+1,FIND(',',A2,FIND(',',A2)+1)-FIND(',',A2)-1)
这个公式一看,就令人头晕目眩,实在是太麻烦了。
其实,大家只知道TEXT函数的格式化功能,肯定想不到其实TEXT也可以用来实现数据的提取。对于上述案例的需求,用TEXT函数来实现,公式简单又好记。
如下图所示,用TEXT函数来实现提取“市”,公式如下:
=TEXT(-1,SUBSTITUTE(A2,',',';'))
公式中SUBSTITUTE(A2,',',';')的功能是将“,”替换为“;”,所以如果长字符串是用“,”分隔的话,那公式就更简单了,如下:
=TEXT(-1,A2)
这个公式与用一般方法的公式差别实在是太大了。
其实用TEXT函数实现数据提取的原理并不复杂,只要了解数字自定义格式是由“;”号分隔的四分位结构就可以了。
数字自定义格式的四分位结构如下:
正数;负数;零;文本
想要提取由“;”分隔的字符串中的某一部分,用不同类型的数字即可,即:用任意正数可以把第1个内容提取出来,用任意负数可以把第2个内容提取出来,依此类推。本案例的公式“=TEXT(-1,A2)”即是用负数“-1”实现提取字符串中第2个内容。
看到此处,同学们应该明白了,如果字符串用“;”分隔的话,公式最简单。如果用其他符号分隔的话,则需要将其他符号替换为“;”,本案例中公式“=TEXT(-1,SUBSTITUTE(A2,',',';'))”就是用SUBSTITUTE函数将“,”替换为“;”。
当然公式中的“-1”可以是任意负数,只不过“-1”更简捷,所以很多时候都用“-1”表示。
这个公式的确是实用又简单,利用它可以轻松实现提取用符号分隔的字符串内容。不过,它也存在局限,要求字符串是不能超过4段。
联系客服