SEO基础学习交流社群

从Excel一个单元格里提取出一部分数据的公式

2595 0

我们在日常办公使用Excel软件制表时,有时会需要对单元格内的部分数据进行提取,比如电话号码的区号,当数据量大时,逐个复制粘贴显然效率不高,也容易出错。其实有专门的数据提取公式可以轻松处理,本文狂人SEO跟大家分享。

我们通过以下这份表来示范快速提取单元格部分数值的方法:

示范数据表

我们看到A列每个单元格有多项数据,现在需要从中提取出容值、封装和耐压三项数据,不难发现,需要提取的数据具有一定的规律性,而规律正是解决问题的关键。这里有三种方法,我们分别进行介绍。

1、快速填充法(Ctrl+ E)

在B2单元格输入0402时,要先输入一个单引号,或者把单元格修改为文本格式再输入。注意只输入一个数据可能无法通过Ctrl+E得到正确结果,这时输入两格数据再操作Ctrl+E即可。

快速填充法(Ctrl+ E)

2、TRIM-MID-SUBSTITUTE-REPT组合公式法

在一个单元格使用公式:=TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",99)),COLUMN(B1)*99,99)),然后往右+往下拉即可得到所需的结果。

TRIM-MID-SUBSTITUTE-REPT组合公式法

公式解析:

1) 这个公式里用到了五个函数,其中有我们比较熟悉的MID和COLUMN,也有我们不太常用的TRIM、SUBSTITUTE和REPT函数。以下简单说明一下这个公式的思路。

公式的核心部分是SUBSTITUTE($A2,",",REPT(" ",99)),这部分的作用是进行替换。

SUBSTITUTE函数格式为:SUBSTITUTE(在哪里替换,替换什么,换成什么,换第几个),举例如下:

① 公式:=SUBSTITUTE($A2,",","-",3),效果就是把A2单元格的第3个逗号换成-号。
② 公式:=SUBSTITUTE($A2,",","-"),省略第4个参数的时候,代表逗号全部替换。

2) A2中的逗号换成了REPT(" ",99),也就是99个空格。REPT函数的格式为:REPT(要重复的字符,重复次数),举例如下:

公式:=REPT("★",5),就是将★重复五次。

3) 使用SUBSTITUTE得到的数据还需要用MID函数来进行提取。MID函数大家应该比较熟悉了,基本格式为:MID(要提取的数据,从什么位置开始取,取几个字)。在本例中要提取的数据就是SUBSTITUTE(),而要提取的容值的位置原本是在第2个逗号之后,由于我们把逗号换成了99个空格,要提取的位置前面至少有两组空格也就是2*99个字符;相应封装的提取的位置是3*99,耐压的是4*99。

我们采用公式右拉,所以这里用COLUMN(B1)*99作为提取位置。MID的最后一个参数是要取几个字符,为了保险起见,统一提取99个字。那么产生的大量无用的空格需要过滤,因此在最外层套一个TRIM就可以去掉这些空格。TRIM函数只有一个参数,功能就是去掉字符串中多余的空格。

3、MID公式

在一空白单元格里输入MID公式,=MID(text, start-num, num-chars)。text是要截取的单元格地址即A2,start-num是A2中被截取的CPAC所在的开始位数,如第1位,小数点或半角逗号算1位,num-chars要提取的总位数,比如5位,所以在空白单元格内输入MID公式:=MID(A2,1,5),回车后即可得到提取的数值。

MID公式

同理,如果要从单元格内右起第1位开始截取字符,则用RIGHT公式:=RIGHT(A2,15),意思是提取A2单元格中从右起第1位字符到第15位字符的数值。这类公式有个缺点是,当多个单元格内要提取数值的位数有不同,那么就得分开或排序后用公式灵活提取。

发表评论 (已有0条评论)

还木有评论哦,快来抢沙发吧~