怎么提取excel中的字母数字(EXCEL快速提取中英文)

应用场景:工作中,经常需要从单元格数据中截取一部分进行统计、计算,或者从不同系统导出的数据中提取中文、数字和英文等,如何快速提取需要的数据呢?介绍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位数

怎么提取excel中的字母数字(EXCEL快速提取中英文)(1)

案例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中的字母数字(EXCEL快速提取中英文)(2)

第二种方法:用EXCEL快速填充功能提取

快速填充是EXCEL2013以上版本的一个新功能,她能模拟、识别你的操作,推测你内心的想法,然后按照你的想法进行数据填充。

运用快速填充时,首先直接输入一组到三组数据,让Excel自动识别你的意图,再进行快速填充,快速填充的快捷键为Ctrl E,也可以单击“数据”选项卡 > 在“数据工具”组中 > 单击“快速填充”或选中示例单元格,拖动填充柄往下填充,然后在“自动填充”选项中选择“快速填充”来实现。(详细教程可见“EXCELl学习微课堂分享的《EXCEL快速填充功能如此牛逼,你知道吗?》)

案例动态演示:

怎么提取excel中的字母数字(EXCEL快速提取中英文)(3)

第三种方法:用自定义函数myget

函数有两参数,=myget(①从哪提取,②提取什么)

=myget(字符串,0) 取出数字

=myget(字符串,1) 取出中文字符

=myget(字符串,2) 取出英文字母

=myget(字符串,3) 取出特殊字符

=myget(字符串,4) 取出取第一个数字的位置

=myget(字符串,5) 取出取最后一个数字的位置

方法步骤:

首先导入自定义函数的模板文件,点开发工具→VBA或都按ALT F11 调出VBA 编辑器 →在工程窗口右键选择【导入文件】→选择VBA 模块文件(提取中文、英文和数字自定义函数myget.bas)→关闭VBA 编辑器。然后再输入公式即可。

动态演示:

怎么提取excel中的字母数字(EXCEL快速提取中英文)(4)

第四种方法:用【数据】→【分列】的方法

案例步骤:

1、【选中需分列的列】→【数据】→【分列】,在弹出的文本分列向导设置窗口中,根据实际情况选择分隔符号或固定宽度,案例中都是用/分隔的,所以选【分隔符号】→【下一步】

2、选择分隔符号为其他符号【/】→【下一步】

3、选列区域的格式,案例中选择默认的常规格式即可,目标区域选择$B$1(即分列的结果放在哪)点【完成】数据按要求进行了分类提取到3列。

怎么提取excel中的字母数字(EXCEL快速提取中英文)(5)

动态演示:

怎么提取excel中的字母数字(EXCEL快速提取中英文)(6)

小伙伴们,EXCEL快速提取中英文和数字的方法都齐了,大家可根据需要选取不同的方法!

我是EXCEL学习微课堂,头条教育视频原创作者,分享EXCEL学习小技巧,小经验。如果分享的内容对您有帮助,欢迎点赞、收藏、评论、转发,更多的EXCEL技能,可以关注今日头条“EXCEL学习微课堂”。

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页