遇到不规则数据,Excel高手是这样分离数字和文字的~

来自:Excel技巧精选(微信号:ExcelSkill),作者:潇之骥 

在工作过程中会有将的文字、字母和数字分离,如果文字、字母和数字的组合是规则的,则可以用简单的单个函数LEFT、MID以及RIGHT三个函数来处理。如果是不规则的,可以参考以下两种方法。


如图1所示:


(图1)

一、函数法


1.分离文字


在B2单元格输入公式:

=LEFT(A2,(LENB(A2)-LEN(A2)))

通过公式填充就可以得到文字了。


【解析】


因为汉字占两个字节,而字母和数字只占一个字节。所以用LENB(A2)-LEN(A2)可以得出汉字的数目。


如图2所示:


(图2)


2.分离数字


在D2单元格中输入数组公式:

=MID(A2,MIN(IFERROR(FIND(ROW($A$1:$A$10)-1,A2),"")),20)

通过填充就可以得到数字了。


【解析】


MIN(IFERROR(FIND(ROW($A$1:$A$10)-1,A2),""))用来寻找第一个数字所在的位置。由于后面都是数字,可以用MID来提取数字。如果数字多,可以换成比20更大的数字。


1)ROW($A$1:$A$10)-1 用来生成0-9的内存数组;

2)FIND函数用来寻找各个数字所在的位置;

3)IFERROR函数用来屏蔽错误值;

4)MIN函数用来寻找第一个数字所在的位置。


【备注】


其实,可以用数组公式=RIGHT(A2,LEN(A2)-MIN(IFERROR(FIND(ROW($A$1:$A$10)-1,A2),""))+1)来提取数字。思路差不多。


如图3所示:


 (图3)


3.提取字母


文字和数字出来了,可以利用前两者来提取字母,用SUBSTITUTE函数。


在C2单元格中输入公式:

=SUBSTITUTE(SUBSTITUTE(A2,B2,""),D2,"")

这样就可以得到字母啦。


如图4所示:


(图4)


这个函数比较简单就不做解析啦。


【备注】


能达到以上效果的函数不仅仅限于列出的这几个,还有很多其他的函数组合能达到这个效果。

二、快速填充法


在OFFICE2013版本以及更高版本中有一种比较智能的填充形式——快速填充。根据你录入的数据,判断你的意图,并根据你的意图自动填充。这个功能有N种用法,是一个实用性很强的一个功能。


如图5我已经在手动录入了一部分数据。


 (图5)


选中A5然后按组合键Ctrl+E,就可以快速填充,如图6所示:


(图6)


当然,也可以按住鼠标左键填充,如图7所示:


  (图7)

推荐↓↓↓
Excel技巧精选