应用场景：工作中，常常需要从单元格数据中截取一部分进行统计、计算，或者从不同系统导出的数据中提取中文、数字和英文等，如何快速提取需要的数据呢？介绍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代码文件，可私信联系我！

在实际工作中，我们经常需要在一些信息中提取出所需的数字或者文本。今天和大家分享4个常用的函数提取案例。

1、拆分文本和数字

比如我们需要从信息中提取出左边的文本部分，输入公式：=LEFT(A2,LENB(A2)-LEN(A2))，往下填充公式即可。

如果提取右边的数字，则输入公式：=RIGHT(A2,(2*LEN(A2)-LENB(A2)))，往下填充公式即可。

说明：LEN函数是返回字符串的字符数，数字、字母和汉字均为1个字符长度；LENB则返回字符串的字节数，汉字为2个字节长度，数字和字母为1个字节长度。

2、从邮箱中提取用户名和域名

如图中表格所示，我们需要从邮箱地址中提取出用户名和域名。

提取用户名，选中需要输入公式的单元格区域，输入公式：=LEFT(B2,FIND("@",B2)-1)，按Ctrl+Enter组合键填充。

说明：这里用FIND函数查找出“@”符号的位置，减去1正好是用户名的长度，再利用LEFT函数从左提取即可。

如果提取域名，则选中需要输入公式的单元格区域，输入公式：=RIGHT(B2,LEN(B2)-FIND("@",B2))，按Ctrl+Enter组合键填充。

说明：这里先用LEN函数计算整个字符串长度减去@符号前面字符长度，求得域名字符串长度，再利用RIGHT函数从右提取即可。

3、提取出产品编号并判断所属类别

如下图表格所示，我们需要先提取出产品的编号，然后进行大类的判断。

这里我们选中需要输入公式的单元格区域，输入公式：

=IF(LEFT(A2,2)="DQ","电器",IF(LEFT(A2,2)="JJ","家具","装饰"))，按Ctrl+Enter组合键填充。

说明：先利用LEFT函数提取出前两个字符，再结合IF函数进行判断。

4、提取出混合字符串中的数字

如图中表格所示，需要把字符串中间的数字提取出来，先选中需要输入公式的单元格区域，输入公式：=MIDB(A2,SEARCHB("?",A2),(2*LEN(A2)-LENB(A2)))，按Ctrl+Enter组合键填充。

说明：先用SEARCHB("?",A2)查找出第一个数字在字符串中的位置；2*LEN(A2)-LENB(A2)则是计算出数字的个数；最后用MIDB函数提取出来。这里带有B后缀的函数公式，都是按照字节数进行计算。

使用公式提取单元格内的数字，要根据具体的数据结构找到某种规律从而设计出对应的公式。

例一 数字在左侧，且数字位数固定。

直接用公式：=left(A2,2)

第一个参数是要提取数字的单元格，第二个参数是要提取几位（从左边算起）。对于例一的这一类数据，使用的时候根据数字的长度修改第二参数即可。

例二：数字在左侧，数字位数不固定，但是有明显的分隔符号。

见下面的例子：

1、利用公式=FIND("-",A2,1)可以确定"-"的位置。

2、find函数有三个参数，第一个参数是要找什么内容，第二个参数是从哪里找，第三个参数是从第几个字开始找（从左边算起）。

=FIND("-",A2,1) 意思是在A2单元格找“-”，从开始位置找（左边第一位）。公式的结果是数字，代表“-”在单元格的位置（第几个字符），此时我们需要提取的数字长度就是find的结果减1，因此方法就有了：=LEFT(A2,FIND("-",A2,1)-1)

一个表格中有一列身份证号码，我们该如何快速取出身份证后六位呢？而不用逐条录入。该如何做呢？

在这里，我们还是通过Excel函数来解决，right函数即可解决

公式：=RIGHT(B2,6)

公式怎么理解呢？

right：从右面计算

B2：要获取的数值所在的单元格

6：即要获取的位数

提取小数部分

大家好，今天和大家分享"提取小数部分"，介绍4种方法

方法一：Int函数实现

1、公式截图

2、公式

=A2-INT(A2)

3、公式解释

这个函数的作用就是取整如果是正数去掉小数部分如果是负数，去掉小数部分，整数部分还要加+1，如A2单元格-45.35，得到是-46，然后-45.35-（-46）得到0.65，所以这个公式不适合负数。更多Excel学习和问题请加群：289393114、570064677

方法二：Trunc函数实现

=A1-TRUNC(A1,0)

Trunc这个函数也是截取整数部分或者指定小数位数INT 和 TRUNC 在处理负数时有所不同：TRUNC(-4.3)返回-4，而INT(-4.3)返回-5，因为 -5 是较小的数。

方法三：Mid+Find实现

=0&MID(A1,FIND(".",A1),10)

先用find找到小数点位置然后用mid提取，包括小数点最后用0&补上小数点前面的0

方法四：Mod实现

=MOD(A1,1)

mod取数函数当除数是1是，得到余数便是小数，缺点是负数不行

身份证是居民的身份象征，在各个领域都要用到身份证。身份证中可以提取许多的相关信息，然而大多数人都不知道如何应用身份证号来提取有用信息。下面，小编教大家用Excel提取出生日期、性别、年龄及户口所在地等信息。

判断身份证号码是否是18位

输入身份证号码时需要将单元格格式设置为文本。工作时有可能会将身份证号码输错，多输入或者少输入。所以，首先要判断身份证号码是否是18位，再提取身份证信息。也可以在计算公式中判断，但输入公式太多，容易出错。

公式“=IF(LEN(A3)=18,"是","否")”返回“是”即身份证号码为18位，反之则不是18位，输入错误。

LEN函数：得到字符串的长度。=LEN(A3)返回18表示A3单元格字符串长度是18。

出生日期

身份证号码是：622924199605211012第7位开始往后的4位是出生的年，第11位往后两位是出生的月份，第13位往后两位是出生日期。

公式“=1*MID(A3,7,4)”提取年份，从A3单元格第7个数字开始截取4个数字。

MID函数：一个字符串中截取出指定数量的字符，MID(A3,7,4)返回1996表示从A3单元格第7个数字开始截取4个数字。

=1*MID(A3,7,4)表示将MID(A3,7,4)提取的文本数据转为数值数据。

公式“=1*MID(A3,11,2)”提取月份，从A3单元格第11个数字开始截取2个数字

公式“=1*MID(A3,13,2)”提取日期，从A3单元格第13个数字开始截取2个数字

出生日期“=DATE(C3,D3,E3)”

或者直接用公式

“=DATE(1*MID(A3,7,4),1*MID(A3,11,2),1*MID(A3,13,2))”提取出生日期

年龄

实岁计算

=TRUNC((TODAY()-E3)/365)，当前日期减出生日期再除以365，然后用TRUNC函数去掉结果的小数部分

TRUNC函数：作用是去掉结果的小数部分。

TODAY函数：取得当前系统日期，不需要参数。

虚岁计算

=YEAR(TODAY())+1-YEAR(F3)

YEAR函数：返回某个日期的年份，YEAR(F3)返回1996表示出生年份是1996年。

实岁换算成虚岁：在本人生日到来之前，虚岁=实岁+2；在本人生日到来及以后，虚岁=实岁+1。

性别

方法一

身份证号码第17位是单数代表男性，双数代表女性。

=IF(ISEVEN(MID(A3,17,1))=TRUE,"女","男")

ISEVEN函数：ISEVEN(MID(A3,17,1))=TRUE表示身份证号码第17位是双数，为女性；ISEVEN(MID(A3,17,1))=FALSE表示身份证号码第17位是单数，为男性。

方法二

=IF(MOD(MID(A3,17,1),2)=1,"男","女")

MOD函数：两个数值表达式作除法运算后的余数，MOD(MID(A3,17,1),2)表示第17位数除以2，返回1则为男性，反之则为女性。

户口所在地

地区编码：身份证地区编码信息可直接在网上查找

城市

=VLOOKUP(1*LEFT(A2,4),地区编码!A2:B3559,2,0)

LEFT函数：一个字符串的左边截取出指定数量的字符，LEFT(A2,4)表示从A2单元格最左边开始，截取4个字符

VLOOKUP函数：一个查找函数

VLOOKUP（查找目标，查找范围，返回值的列数，精确OR模糊查找)

=VLOOKUP(1*LEFT(A2,4),地区编码!A2:B3559,2,0)表示在地区编码!A2:B3559区域内匹配身份证前4位，采用模糊查找返回“地区编码!A2:B3559”的第二列。

注意：此处必须用1*LEFT(A2,4)将LEFT(A2,4)提取的文本数据转化为数值数据才能用VLOOKUP函数查找

区/县

=VLOOKUP(1*LEFT(A2,6),地区编码!A2:B3559,2,0)

=B2&C2

或者直接提取户籍信息：

=VLOOKUP(1*LEFT(A2,4),地区编码!A2:B3559,2,0)&VLOOKUP(1*LEFT(A2,6),地区编码!A2:B3559,2,0)

以上就是用Excel提取出生日期、性别、年龄、及户口所在地等信息的具体方法，视频操作见于之前发的文章。感谢您的阅读！

每天清晨六点，准时与您相约

问题来源

昨天韩老师讲了Excel208 | OFFSET函数提取最大销量所在整列信息，有位朋友后台留言：韩老师，只查找第一列的信息，在我们单位是不够的，我们需要查找前三名的数据进行分析，怎么用公式实现？

我们只需把昨天公式中的最大值函数换成LARGE函数就好了。

公式实现

在B9单元格输入公式：

=OFFSET($A$2,ROW($A1)-1,MATCH(LARGE($B$5:$K$5,COLUMN(A1)),$B$5:$K$5,0))

公式向下、向右填充，即得销售量第一、第二、第三位的整列信息。

公式解析

LARGE($B$5:$K$5,COLUMN(A1))：

在B5:K5，所有的销量中，查找第一大的数值。

在B9单元格，COLUMN(A1)的返回值是1，即第一大的值；公式向右填充时，变为COLUMN(B1)、COLUMN(C1)，返回值自动变为2、3，即第二、第三大的值。

MATCH( LARGE($B$5:$K$5,COLUMN(A1)) ,$B$5:$K$5,0)

在B5:K5区域，匹配最大值所在列数，公式向右填充，得第二、第三大值所在列。

OFFSET($A$2,ROW($A1)-1,MATCH(LARGE($B$5:$K$5,COLUMN(A1)),$B$5:$K$5,0))

以A2为基准点，向下偏移ROW($A1)-1行，向右偏移最大值所在列数。

本示例中：

公式在B9单元格时，ROW($A1)-1的结果是0，最大值1844，以A2为基准点，偏移0行，偏移到1844所在的列，即是单元格“销售2部”；

公式向下填充，ROW($A1)-1自动变为ROW($A2)-1、ROW($A3)-1、ROW($A5)-1、ROW($A5)-1，则自动变为偏移1、2、3、4行，即得最大值1844列所有的信息。

公式向右填充，即得第二、第三大的值所在列信息。

素材下载

本次练习下载：

链接：http://pan.baidu/s/1qYNZ3as

密码：9tav

问题来源

数据取整，是EXCEL数据处理的最常用的方式。可能大家最经常用的是INT函数，但，INT函数并不能满足所有的取整要求。比如，今天一位朋友要按照某位数的倍数取整，INT就解决不了了。

韩老师总结各种取整函数，大家可以收藏起来，遇到不同的取整要求，可以翻出来找合适的函数与方法。

九种方法分析一：INT取整

举例：

特征：

1、对正数，直接截掉小数取整；

2、对负数，截掉小数再-1取整。

二：TRUNC取整

特征：不管正数还是负数，都是直接截掉小数取整。

三：ROUND小数取整

第二个参数为0，采取四舍五入方式取整。

四：ROUND整数取整

第二个参数为负数，采取四舍五入方式取整。

五：ROUNDUP向上舍入函数

1、朝着远离 0（零）的方向将数字进行向上舍入。

2、如果第二个参数为 0，则将数字向上舍入到最接近的整数。

3、如果第二个参数小于 0，则将数字向上舍入到小数点左边的相应位数。

六：ROUNDDOWN向下舍入函数

1、朝着零的方向将数字进行向下舍入；

2、如果第二个参数为 0，则将数字向下舍入到最接近的整数；

3、如果第二个参数小于 0，则将数字向下舍入到小数点左边的相应位数。

七：MROUND函数

1、返回参数按指定基数舍入后的数值；

2、舍入的方式是“四舍五入”；

3、数值和基数参数的符号必须相同。如果不相同，将返回 #NUM 错误。

八：CEILING函数

1、 向上舍入（沿绝对值增大的方向）为最接近的指定基数的倍数。

2、如果数值为正值，基数为负值，则 返回 错误值 #NUM!；

3、如果数值为负，基数为正，则对值按朝向 0 的方向进行向上舍入；

4、如果数值和基数都为负，则对值按远离 0 的方向进行向下舍入。

九：FLOOR函数

1、将数值向下舍入（沿绝对值减小的方向）为最接近的指定基数的倍数

2、如果数值为正值，基数为负值，则 返回 错误值 #NUM!

3、如果数值为负，基数为正，则对值按远离 0 的方向进行向下舍入；

4、如果数值和基数都为负，则对值按朝向 0 的方向进行向上舍入。

大家好，今天和大家分享“提取数字前面的字母你会几种方法？”，首先大家不要看我的解法，自己想想看，你可以用几种解法解出这个题，要求如下图，字母连续且在左边，数字在右边。我用6种解法分享给大家，记得全是数组公式。

一、解法1：Find找第一个数字出现的位置|更多Excel学习和问题请加群：289393114、570064677

1、公式截图

2、公式

=LEFT(A2,MIN(FIND(ROW($1:$10)-1,A2&5/19))-1)

3、公式解释

用find函数找到第1个数字出现的位置，然后减掉1，得到字母的个数，再从左边提取字母

为什么A2后面要连接一个5/19，目的是防止找不到报错

ROW($1:$10)-1指10个阿拉伯数字

min提取的第一个数字出现的位置

二、解法2：Count统计字母的个数

=LEFT(A2,COUNT(0/(MID(A2,ROW($1:$19),1)>="A")))

MID(A2,ROW($1:$19),1)>="A" 用mid函数分隔每个字符，然后判断是否大于等于A，注意不区分大小写的，得到一个维数组，由True,False构建的

0/true返回0，0/False返回报错

用Count统计0的个数，就是得到字母的个数，最后用Left函数提取字母出来

三、解法3：Match找第一个数字出现的位置

=LEFT(A2,MATCH(,MID(A2,ROW($1:$19),1)*0,)-1)

用函数mid把每一个字母分隔，然后乘0，也就是所有的分隔出来的数全变成了0

MATCH(,MID(A2,ROW($1:$19),1)*0,)-1用Match函数找到第1个0出现的位置，也就是第一个数字出现的位置，所以也要减掉1

然后用Left函数从左边提取

四、解法4：Match找最后一个字母出现的位置

=LEFT(A2,MATCH(1,0/(MID(A2,ROW($1:$19),1)>"9")))

0/(MID(A2,ROW($1:$19),1)>"9")用Mid分隔每个字符，然后判断是否大于9，记得9要加双引号，所以的字母都大于“9”，也就是说所有字母在的位置都返回true,我们找最后一个true的位置，其实就是最后一个字母的位置，用0除，目的让所有的True返回0，所有的False报错

MATCH(1,0/(MID(A2,ROW($1:$19),1)>"9"))用match找最后一个0的位置

最后用lfeft从左边提取

五、解法5：字母的个数=总字符数-数字的个数

=LEFT(A2,LEN(A2)-COUNT(--RIGHT(A2,ROW($1:$19))))

COUNT(--RIGHT(A2,ROW($1:$19)))统计数字的个数

LEN(A2)-COUNT(--RIGHT(A2,ROW($1:$19)))总的字符数减掉数字的个数，得到字母的个数

六、解法6：巧用Match第2参数

=LEFT(A2,COUNT(MATCH(MID(A2,ROW($1:$19),1),CHAR(ROW($65:$90)),)))

用mid把每个字符都分隔出来作match函数第1参数，这里很巧妙用26个字母作match函数的第2参数，也就是说单元格里每一个字符都会返回一个数字位置，这些数字的个数就是字母的总个数，然后用Count函数去统计

CHAR(ROW($65:$90))返回26个大写字母

另注意，Match函数查找字母时，不区分大小写字母

在前面两期教程中为大家讲解了LAGER函数的基础用法。那么今天龚老师就来个升级版的排名函数应用案例讲解。

之前我们讲解的是如何按排名提取成绩。今天跟大家分享一下如何按照排名提取相对应的学员姓名。

举例：

如上表是，我们的学员成绩信息表。我们现在需要快速从表中提取出前三名学员姓名。

函数公式

=LOOKUP(1,0/(LARGE($C$2:$C$13,ROW(D1))=C2:C13),B2:B13)

函数公式解释：

ROW(D1)：返回D1所在行的行号。

LARGE($C$2:$C$13,ROW(D1):返回ROW(D1)对应的排名结果。

LOOKUP(1,0/(LARGE($C$2:$C$13,ROW(D1))=C2:C13),B2:B13)用lookup函数查找学员成绩所对应的学员姓名。

是不是又学到技巧了？记得分享哦！