- ?
如何在Excel单元格中提取数字?几个函数轻松帮你搞定!
期雨
展开
处理复杂的Excel表格时候,如何把单元格里面复杂的数字给提取出来呢?是将单元格里面的数字复制粘贴出来吗?NO不是!在这里教大家几个函数轻松帮你将单元格里面的数字给全部提取出来,完全不费吹灰之力!
1、如何提取单元格文本中数字
代入函数公式:
=MIDB(A2,SEARCHB("?",A2),2*LEN(A2)-LENB(A2))
将函数代入到指定的单元格,即可提取A2单元格数字,前提要将函数的坐标对应好单元格呦!
2、如何提取文本左右数字
(左侧)代入函数公式:
=LEFT(A2,2*LEN(A2)-LENB(A2))
(右侧)代入函数公式:
=RIGHT(A2,2*LEN(A2)-LENB(A2))
同理直接将函数代入到单元格里面,就可以快速提取数字,下面我演示下提取文本左侧数字。
3、如何提取混合在一起的数字
代入函数公式:
=SUM(MID(0&A2,LARGE(ISNUMBER(--MID(A2,ROW($1:$1024),1))* ROW($1:$1024),ROW($1:$308))+1,1)*10^ROW($1:$308)/10)
在复制函数到单元格的同时要按住Shift+Ctrl+Enter键,就可以提取混合在一起的数字了!
- ?
技巧 | Excel中4个快速提取手机号的模板公式,你知道几个?
Nantes
展开
Hi,大家好,我是胖斯基
慵懒的午后,销售MM带着82°C的微笑走过来,问:如何在这一堆繁杂的数据中,把手机号提取出来?
本着务实主义,打开表格一看,密密麻麻,瞬间眼花……
典型密集型恐惧患者的魔障
当然魔障归魔障,问题是依旧存在和处理的
那针对这种类型的问题处理,一般有套路可遵循,而这套路就是所谓的模板公式
1
LOOKUP经典款
公式:=-LOOKUP(,-MID(B2,ROW($1:$50),11))
说明:1是通过MID来获取多个固定长度为11的数字串,2是通过LOOKUP来取所需数值
注意:这里仅仅只获取了最后一个手机号(因LOOKUP函数的查询原理导致)
2
SUBSTITUTE替换款
公式:=MAX(IFERROR(--CLEAN(MID(SUBSTITUTE(B2,";",REPT(" ",90)),{1,91,181},90)),))
说明:这里通过分析联系电话中,电话号码的间隔是通过";"进行,故整体将";"替换为90个长度的空格,这样就可以分段获取带空格的手机号,在通过CLEAN函数和MAX函数,来获取最终的手机号
注意:这里仅仅只获取了最后一个手机号(原因,可以思考下)
3
VLOOKUP查询款
公式:=VLOOKUP(,MID(B2,ROW($1:$99),11)*{0,1},2,)
说明:这里本质上,也是通过MID来构建出一连串的11个长度的数字串,与第1种方法不同的特点在于,这里通过Vlookup函数并结合{0,1},构建出了一个查询区域,并反馈对应手机号的结果。
注意:这里仅仅只获取了第一个手机号(因VLOOKUP函数的查询原理导致)
到这里,发现这3个套路公式要么获取了第1个手机号,要么获取了最后一个手机号,如果数据信息中,涵盖了多个手机号呢。如何获取?
没问题,变通一下即可!
4
COLUMN变通款
公式:=LARGE(--TEXT(MID($B2,ROW($1:$99),11),"0;;0;!0"),COLUMN(A1))
说明:这里利用LARGE函数来构造出一个数组(里面的内容就是手机号),通过COLUMN(A1)函数并往右拖动鼠标,横向的提取一个个手机号
胖斯基 | 说
模板公司看不懂无所谓,会用即可,赶紧收藏吧!
- ?
EXCEL:快速提取单元格中的汉字
詹绯
展开
工作中我们会遇到很多千奇百怪的问题,有一天老板突然对你说,去把这些产品名称中的文字提取出来。你可能会说这个老板真有病,但有一天你真的没准儿会遇到哦。O(∩_∩)O哈哈~
如果真的有这么一天,你会怎么处理呢,复制、粘贴、复制、粘贴、复制……,这样会累死的。下面说两个简单的方法来拯救繁重的工作。
第一种,函数公式法。
=LEFT(A2,LENB(A2)-LEN(A2))
第二种,快速填充法。
嫌函数公式麻烦的朋友有福啦,
Excel2013版本提供了一个非常好用的功能:“快速填充”,借助这个功能就可以轻松完成。
在B2单元格输入汉字:技嘉,回车后下拉,并选择“快速填充”即可。
怎么样,繁重的工作就这样轻松的解决了,大家继续好好学习吧。
- ?
EXCEL中实现中文中数字的快速提取,教你2个方法快速处理!
吉书琴
展开
前言:
之前发过一篇文章:EXCEL中如何实现中文与数字的快速分离?教你一招快速处理!
教了大家如何用LEFT和SUBSTITUTE函数实现将中文左边和右边的数字进行分离的方法,但这个方法不适用数字在中文里面的情况 。
故有同学在后台留言询问:数字在中间及还有小数点的情况下如何处理呢?
今天我就教大家两个方法实现数字快速提取!
需提取中文中的数字
方法1:利用两端对齐提取数字
复制A列的字符到B列,然后把B列的列宽调整为一个汉字大小的宽度
开始选项卡 - 编辑 - 填充 - 两端对齐
从B列的第一个数字向下选取全部,打开提示下拉,点转换为数字。
选取B列,打开定位窗口 - 定位条件 - 文本。执行这一步会选取所有非数字的单元格,然后右键中点删除文本就行了。
方法2:编写自定义函数提取数字
虽然EXCEL软件并没有提供从中文中提取数字的专门函数,但我们可以自已编写一个自定义函数mygetnumber实现这一功能。将以下代码放入任一模块中,即可在工作表中使用这个函数。
Function mygetnumber(cel As Range)
With CreateObject("vbscript.regexp") ‘利用正则表达式实现数字提取
.Pattern = "[^\d.-]+"
.Global = True
mygetnumber = .Replace(cel, " ")
End With
End Function
编写自定义函数提取数字
结语:excel软件实际上是非常强大的,只有想不到,没有做不到。小伙伴们,你学会了吗?快快动手试试看吧!
- ?
从excel提取导出图片
无缘
展开
今天我们来谈谈excel图片批量导出:excel表格怎么批量导出图片的知识。
1、什么的excel图片导出:主要是预防牛逼地讲完了,一些朋友弱弱地问一句“啥是excel导出图片”的问题。所谓excel表格批量导出图片,就是从一个excel文件中(这个excel里面打开后能看到图片),把里面能看到的图片,导出到一个文件夹中;这是很多朋友都会遇到的一个事情,这里开宗明义醍醐灌顶地未卜先知大家的疑难杂症,提前教会大家,从此让大家学会了excel图片导出的技术,把握住了改变人生不会excel导出图片的机会。
2、废话不多说,图片导出的步骤如下:(1)把包含图片的excel的文件名的格式重命名为“.rar”,这时候他就变成了压缩文件(建议操作前最好备份一下,不管你喜欢不喜欢备胎,但是它有时真的不让你后悔)。(2)点击压缩文件,弹出警告的时候关闭警告,找到对应的目录,左键点住不要松手拖到到外面,打开这个文件夹,就会发现:哇塞,好厉害呀,图片都出来了,是不是很神奇呢?(3)相信经过前两步,大家觉得是相当的扯淡,根本不知道咋弄,这是正常的,因为目前业界99%的教程都是这样子,小编不是那样子的人,所以我们上动图:
小提示:每人对文字或动画视频都有自己的先天的超强领悟力和内心穿透力,所以,我会继续抱着以人为本的态度,文字和视频动画相结合,搞的越来越丰富有价值对大家有帮助一些。以上就是excel图片批量导出:excel表格怎么批量导出图片的全部内容了。
然而有人提出了一种更好的方法,步骤是:1、选择另存为,2、格式选择为“网页(*html;*.html)”,OK一切搞定了。有时候我们做了很多事情,感觉自己很牛逼的样子,但是回头一看人家一下子就秒杀了我们,遇到这些人是一种幸运,希望他们可以跟我们分享更为有用的知识或小技巧。
- ?
EXCEL多表资料提取汇总方法,值得收藏
芷云
展开
EXCEL多表资料提取汇总,如果你还在复制粘贴,那就太落后了;
如果领导给你一份含有公司所有人简历的资料表,让你把所有人员的信息提取汇总到一个表中,公司有几十或几百人,只靠复制粘贴,恐怕一天都不一定能完成;
今天我们来讲一下多表资料提取汇总的方法,分三步完成;
本教程基于excel2016;
首先,各人员姓名所在的工作表格式必须相同;
在“汇总”表内设置好表头;
1.指定名称
【公式】--【定义名称】--【名称】处随便命一个名称ML,【引用位置】处输入=GET.WORKBOOK(1);
2.提取工作表名称
在O3单元格输入=INDEX(ML,ROW(A1)),向下填充;
这样各工作表对应的名称就提取出来了;
3.提取资料
在B2单元格输入=INDIRECT($O3&"!B2"),B2姓名所对应的名字在“周小林”表中在B2单元格;
C2输入=INDIRECT($O3&"!D2"),C2性别所对应的性别在“周小林”表中在D2单元格;
D2输入=INDIRECT($O3&"!B3"),D2民族所对应的民族在“周小林”表中在B3单元格;
下同
将B2到N2单元格分别输入完成后,选择B2:N2,下拉填充;
注意点:
1)日期列数字,选中,设置单元格格式,日期
2)选中B2:N4区域,复制,右键选择性粘贴,数值,就可以删除O列;
3)因GET.WORKBOOK(1)是宏表函数,所以在完成后,将工作表“另存为”后缀为“xlsm”格式或者“启用宏的工作薄”;
- ?
技巧 | 如何在Excel中巧妙的提取你需要的数据?
Shawn
展开
Hi,大家好,我是胖斯基
又是一个突如其来的周三傍晚,风雨交夹,狂风相随……
想想那许久未更新的公众号,再摸摸那胸前的一整块腹肌,愧色依旧,还是码字减肥吧!
如何在Excel中巧妙的提取你需要的数据?
举个例子:
由于从财务系统导出的数据中,会计科目是一种全级显示,用””来展示层级的划分,但是实际在数据统计或应用的时候,需要用到末级科目,故:如何在批量数据中,快速提取末级科目的名称?
也许猴急如你,一上来就想着函数或VBA,可行是可行,但是这种霸王硬上弓的模式,往往因用力过猛而不能快速得到预期效果……
聪明人,应该懂得循序渐进!
1
查找替换法
曾经有那么一个快捷键,它可以在茫茫人海中帮你寻觅“对象”,活好话不多,Ta就是Ctrl+F。
如果你想起了Ta,那这个问题便迎刃而解,效果如下:
注意这里查找的时候,用到的查询内容是 【*\】,查找内容替换为空,过程进度的处理基本上是秒级。
说明:由于要获取的是末级科目名称,故希望在科目信息中,保留的内容是最后一个【\】后面的内容,所以这里查找的时候采用了通配符【*】,组合起来就是【*\】,故:凡是最后一个【\】前的内容都查询出来,并替换为空
想想,这种温柔的方式,是不是比直接上函数和VBA的来的更和谐?
2
快速填充法
当然条条大路通罗马,何必单恋一种法?
曾经也有那么一个智能填充工具,Ta有那么一点人工智能的特效,能够想你所想,取你所需……
Ta就是在Excel2013版及之后推出的一个快速填充工具(Ctrl+E)。如果你想起Ta,那基本也就是万花丛中笑春风般的效果,如下:
注意:这里手工输入了2条记录(目的在于给Excel更多判断的选择),然后手工拖拽,选择快速填充后,所有末级科目数据自动呈现出来,怎么样?处理进度上是不是也是秒级?
So,循序渐进是不是很重要?
慢慢来,你会发现不一样的精彩
3
函数法
这种方法呢不是特别提倡,毕竟不是人人都能上手就来函数,当然,你要是Excel大神,无妨,怎么开心怎么来。
函数1:=TRIM(RIGHT(SUBSTITUTE(B3,"",REPT(" ",200)),200))
函数2:=MID(B3,COUNT(,FIND("",B3,ROW($1:$99))),20)
函数3:=VLOOKUP("*",RIGHT(B3,ROW($3:$24)-{0,1}),2,)
具体函数逻辑就不在重复累赘说明了
胖斯基|说
在Excel中提取有效的数据,是一项频率极高的操作,其处理逻辑基本都已成为套路,学会灵活应用就好。
当然,依旧要敲黑板说明的是:观察Ta,循序渐进,温柔对待,也许灵巧的技巧,能解决你很大的问题……
更多精彩,敬请关注Excel老斯基
- ?
Excel小技巧6-Excel中如何提取数字(一)
飞双
展开
使用公式提取单元格内的数字,要根据具体的数据结构找到某种规律从而设计出对应的公式。
例一 数字在左侧,且数字位数固定。
直接用公式:=left(A2,2)
第一个参数是要提取数字的单元格,第二个参数是要提取几位(从左边算起)。对于例一的这一类数据,使用的时候根据数字的长度修改第二参数即可。
例二:数字在左侧,数字位数不固定,但是有明显的分隔符号。
见下面的例子:
1、利用公式=FIND("-",A2,1)可以确定"-"的位置。
2、find函数有三个参数,第一个参数是要找什么内容,第二个参数是从哪里找,第三个参数是从第几个字开始找(从左边算起)。
=FIND("-",A2,1) 意思是在A2单元格找“-”,从开始位置找(左边第一位)。公式的结果是数字,代表“-”在单元格的位置(第几个字符),此时我们需要提取的数字长度就是find的结果减1,因此方法就有了:=LEFT(A2,FIND("-",A2,1)-1)
- ?
用excel快速提取唯一值的N钟办法,你会哪一种呢?
七度凉
展开
喜欢、有用,就点点关注我的头条号!
问题:如何从A列中提取不重复姓名?
方法一:删除重复项
【数据】→【删除重复项】
方法二:高级删选
【数据】→【高级】
方法三:数据透视表
【插入】→【数据透视表】
方法四:函数公式
普通公式:
=LOOKUP(,0/FREQUENCY(0,ISNA(MATCH(SUBSTITUTE(A$2:A$10,"~","~~"),E$1:E1,))-1),A$2:A3)&""
知识点:该题中的姓名“仰望~星空”中有带通配符“~”的,直接使用函数MATCH会导致结果有误。当查找通配符本身时,需在该字符前键入波形符(~)。可以嵌套个函数SUBSTITUTE,将“~”替换成“~~”。
数组公式:
=IFERROR(INDEX(A$2:A$110,MATCH(,COUNTIF(G$1:G1,A$2:A$10),)),"")
按Ctrl+Shift+Enter三键结束。
方法五:SQL
- ?
EXCEL快速提取中英文、数字的4个方法,总有一个适合你!
Francesca
展开
应用场景:工作中,常常需要从单元格数据中截取一部分进行统计、计算,或者从不同系统导出的数据中提取中文、数字和英文等,如何快速提取需要的数据呢?介绍4种常用方法。
第一种方法,用文本截取函数三剑客(LEFT/RIGH/MID函数)和文本长度度量函数(LEN /LENB)来提取
1、LEFT函数和RIGHT函数
参数:
LEFT (①要截取的文本或字符串,②要截取的字符个数)
RIGHT (①要截取的文本或字符串,②要截取的字符个数)
2、MID函数
参数:
MID(①要截取的文本或字符串,②从哪个位置开始截取,③要截取的字符个数)
3、LEN和LENB函数
LEN()& LENB()函数的参数很简单,只有一个,就是您要测量长度的文本,LEN()函数和LENB()函数的区别就是前者测量字符数,后者测量字节数,只有在文本字符串中包含汉字时才会有区别。
4、案例
案例1:从身份证号中截取省市区代码、出生年月和顺序及校验码
省区代码:从左边取6位。C2公式:Left(B2,6)
出生年月:从第7位数据开始取,要取出8位数。D2公式:Mid(B2,7,8)
顺序号和校验码:从右边4位数。E2公式:Right(B2,4)
案例2: 将单元格中的中英文分开
提取英文:
①在B2单元格录入公式:=LEFT(A2,LEN(A2)*2-LENB(A2))
②下拉填充公式。
公式解析:
LEN(A2)*2得出的结果是:12 (效果相当于:love爱love爱)
LENB(A2)得出的结果是:8 (效果相当于: love爱爱)
LEN(A2)*2-LENB(A2)得出的结果是:12-8=4(效果相当于:love爱love爱- love爱爱=love)
最后用LEFT函数从左取相对应的英文字母数即可提取英文单词。
提取中文:
①在C2单元格录入公式:=RIGHT(A2,LENB(A2)-LEN(A2))
②下拉填充公式。
公式解析:
LENB(A2)得出的结果是:8 (效果相当于:love爱爱)
LEN(A2)得出的结果是:6(效果相当于:love爱)
LENB(A2)-LEN(A2)得出的结果是8-6=2 (love爱爱- love爱=爱)
最后用RIGHT函数从右取相对应的汉字字符数即可提取中文。
第二种方法:用EXCEL快速填充功能提取
快速填充是EXCEL2013以上版本的一个新功能,她能模拟、识别你的操作,推测你内心的想法,然后按照你的想法进行数据填充。
运用快速填充时,首先直接输入一组到三组数据,让Excel自动识别你的意图,再进行快速填充,快速填充的快捷键为Ctrl+E,也可以单击“数据”选项卡 > 在“数据工具”组中 >单击“快速填充”或选中示例单元格,拖动填充柄往下填充,然后在“自动填充”选项中选择“快速填充”来实现。(详细教程可见“EXCELl学习微课堂分享的《EXCEL快速填充功能如此牛逼,你知道吗?》)
案例动态演示:
第三种方法:用自定义函数myget
函数有两参数,=myget(①从哪提取,②提取什么)
=myget(字符串,0) 取出数字
=myget(字符串,1) 取出中文字符
=myget(字符串,2) 取出英文字母
=myget(字符串,3) 取出特殊字符
=myget(字符串,4) 取出取第一个数字的位置
=myget(字符串,5) 取出取最后一个数字的位置
方法步骤:
首先导入自定义函数的模板文件,点开发工具→VBA或都按ALT+F11 调出VBA 编辑器 →在工程窗口右键选择【导入文件】→选择VBA 模块文件(提取中文、英文和数字自定义函数myget.bas)→关闭VBA 编辑器。然后再输入公式即可。
动态演示:
第四种方法:用【数据】→【分列】的方法
案例步骤:
1、【选中需分列的列】→【数据】→【分列】,在弹出的文本分列向导设置窗口中,根据实际情况选择分隔符号或固定宽度,案例中都是用/分隔的,所以选【分隔符号】→【下一步】
2、选择分隔符号为其他符号【/】→【下一步】
3、选列区域的格式,案例中选择默认的常规格式即可,目标区域选择$B$1(即分列的结果放在哪)点【完成】数据按要求进行了分类提取到3列。
动态演示:
小伙伴们,EXCEL快速提取方法都齐了,大家可根据需要选取不同的方法!
我是EXCEL学习微课堂,分享EXCEL学习小技巧,小经验。如果分享的内容对您有帮助,欢迎点赞、收藏、评论、转发,更多的EXCEL技能,可以关注“EXCEL学习微课堂”。如需自定义函数myget代码文件,可私信联系我!