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

时间:2023年08月30日

/

来源:网络

/

编辑:佚名

我们在日常办公使用Excel软件制表时,有时会需要对单元格内的部分数据进行提取,比如电话号码的区号,当数据量大时,逐个复制粘贴显然效率不高,也容易出错。其实有专门的数据提取公式可以轻松处理,本文78模板网跟大家分享。
我们通过以下这份表来示范快速提取单元格部分数值的方法:

示范数据表
我们看到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位字符的数值。这类公式有个缺点是,当多个单元格内要提取数值的位数有不同,那么就得分开或排序后用公式灵活提取。
猜你需要

豫ICP备2021026617号-1  豫公网安备:41172602000185   Copyright © 2021-2028 www.78moban.com/ All Rights Reserved

本站作品均来自互联网,转载目的在于传递更多信息,并不代表本站赞同其观点和对其真实性负责。如有侵犯您的版权,请联系 1565229909#qq.com(把#改成@),我们将立即处理。