施老师：相信许多人经常会遇到一些不懂Excel的同事和老板，将一份好好的资料在Excel中填写的是乱七八糟，甚至有时在一个单元格中弄了N多个信息，又是中文，又是英文，又是数据。如果数据少的话可以手动一个一个的拎出来，如果数据多的话就麻烦了。今天就由宁双学好网施老师给大家分享一个不错的VBA，可以将单元格中混合数据包含中文、英文、数据信息单独提取出来。

混合数据预览

先看看混乱的数据吧！也许你的数据比这更乱，不过也没关系，我们可以轻松提取我们想要的中文或英文或数据。

开始提取

一、首先是【开发工具】-【查看代码】-【Visual Basic】；

二、在弹出的界面中点击【插入】-【模块】；

三、将下列代码复制到模块中。

Function MyGet(Srg As String, Optional n As Integer = False)

Dim i As Integer

Dim s, MyString As String

Dim Bol As Boolean

For i = 1 To Len(Srg)

s = Mid(Srg, i, 1)

If n = 1 Then

Bol = Asc(s) < 0

ElseIf n = 2 Then

Bol = s Like "[a-z,A-Z]"

ElseIf n = 0 Then

Bol = s Like "#"

End If

If Bol Then MyString = MyString & s

Next

MyGet = IIf(n = 1 Or n = 2, MyString, Val(MyString))

End Function

四、返回Excel界面，然后在需要提取单元格中录入公式

【=myget(A2,1)】是提取所有中文

【=myget(A2,2)】是提取所有英文

【=myget(A2)】是提取所有的数据

五、最后鼠标相应的往下拉就行。

喜欢施老师的干货文章请点赞，关注，分享，大家碰到中英文和数字混到一个单元格中，是怎样分离的，请在下方评论区留言写下你的看法，和施老师一起探讨。

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

EXCEL狗们很多时候在进行EXCEL处理的时候，会遇到在身份证上提取生日，在一组数字与本文的字符串里提取数字或者文本。最常见的方法应该是写一大串函数吧，下面小编教大家几种快速提取数据的方法。

方法一：函数提取。函数：=TEXT(MID(A1,7,8),0000-00-00)，进行提取生日，函数的解释为：提取身份证号码从第7位数字开始的8个数，数据的显示格式为0000-00-00。下面操作起来。

方法二：利用分列按钮进行提取。

方法三：重点介绍职场利器，Ctrl+E快速智能填充功能（EXCEL 2013版本以上）。需要借left,right,mid,len等等取数都可以通过一个快捷键Ctrl+E填充功能来实现，极大提高来工作效率。下面就开始操作起来。

①先手动输入前面两个生日

②选择填充的区域，注意需要将手输入的区域一起选择。然后Ctrl+E，就可以自动填充啦。是不是很简单啊

Ctrl+E快速智能填充功能，还可以用来提取字符串当中的文本或者数字。如下操作：

Ctrl+E快速智能填充功能，不但可以用来提取，还可以用来合并。如下操作：

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

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

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

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

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

见下面的例子：

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

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

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

前言：

之前发过一篇文章：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软件实际上是非常强大的，只有想不到，没有做不到。小伙伴们，你学会了吗？快快动手试试看吧！

假设A列有500个数据，如何选出这一列里的第5、10、15、20、25个数据，以此类推。

=INDEX($A:$A,ROW(1:1)*5+1)

INDEX函数有2种语法结构。这里介绍她的第一种结构：

INDEX(要取数的目标区域，行号，列号)，其中列号可以省略。如果列号省略，则目标区域必须为单列。

处理复杂的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键，就可以提取混合在一起的数字了!

Vlookup数据调取公式应用

制作表格过程中往往遇到之前数据引用问题，所以我们在表格中如果去查找以往的数据再进行手录的话还是需要一定的操作时间的，万一遇到需要采用的数据量较大的情况下我们按照一个一个的抄录方式会浪费很大的工作时间，所以我们应该保持对Excel表格中的函数应用的学习热情。

遇到以上数据需要收录的情况下，我们往往需要利用一部分公式进行批量操作，如下图所示的Vlookup函数的使用操作图

上图演示的Vlookup函数使用过程中应当注意几点：

如果数据中有重复的内容或者重复的name数据，则Vlookup函数只读取（从上至下）第一个相对应的数据，而且在应用过程中需要了解到的是Vlookup函数只针对单同一列不重复的数据抓取，如果重复的情况下我们该怎么使用Vlookup函数呢？如下图所示:

如上图，我们使用的方式是在大量数据的情况下避免Vlookup函数无法获取第二或者更多的数据情况下，我们采用sumif函数进行区域同条件的数据进行求和，可详细查看表格中的函数应用方式，这是我平时应用比较多的结构函数。下面便给各位详细给出函数的公式内容：

函数引用以及作用详解

=vlookup（查找值，数据表，列序数，[匹配条件]）公式中查找值表示的是：我们需要查找的这个数据，求的是这个数据对应的某项数据，数据表需要选择查找项与需要获取的某项数据在内的所有序列，然后列序数表示的则是从选中的序列中的第几列，填写数字；匹配条件分精准匹配和近似匹配两种，字面意思可以清楚明白选哪个。=countif（区域，条件）公式中需要填写的内容较少，仅为两项，这个函数主要是针对该选中区域中的复合条件项个数，非常有利于查找这个条件的权重或者这个条件项的出现次数。便于分析数据。使用方式如上图所示：区域选择含有你想要查找的条件内容列，条件则选择需要选择的单项数据。=sumif（区域，条件，[求和区域]）区域与条件的选择与上一项的countif一样的选择方式，仅求和区域选择与Vlookup一样的需要获取的数据列，如上图所示。

希望上述简要说明对各位有所帮助，我在应用函数过程中，有很多函数在实际的应用过程中相当具有帮助，可能也是工作原因导致，但是在平时我们如果学会使用这些函数，会给我们不时的工作内容带来更多的帮助，这也给了我一个很好的启发就是：能让工具快速解决的问题我们尽量不耗费大量时间处理。当然还有很多有关于数据调取的函数，由于本号作者时间有限，还请给位谅解，文章会在不定期做更新！

图3则是告诉我们还有很多函数需要我们去了解，同样图4是其他函数的应用，图4的表格中所有数据均来自下方的其他工作表中的计算结果，所以表格给我一个很好的很便捷的工作内容便是，我只要提交初始数据，结果可以给我输出我需要经过长时间计算的其他结果，这样在短暂的时间准备后能够帮我后期节约大量的工作时间。希望对各位有所帮助

主旨一直不变，当然希望给我有空能够在文章下面流量进行互动，相互学习，共同成长

本号宗旨：不断学习，能力晋升

搞定Office每周三更新

「搞定Office」是黑马公社全新的七大版块之一，每周三更新，教授Office等办公软件的各种应用技巧。

◆◆◆

提取字符串中的数字并在多工作表同时录入数据

黑马说：不用任何函数，只用简单的几个操作，即可实现字符串数字的提取，SO easy 哦！

BY：熊大大

◆◆◆

图文说明

字符串中的数字可以这样快速提取

无论是你创建模板，还是模拟数据建模，

我们都需要用到将一个数据库复制在另一个文档中。

如下图，我们需要将黑马公社1的数据导入在黑马公社234的文档中，

一个个单纯的复制太过麻烦，

今天就教大家提取数据以及跨表格将其同时复制粘贴的方法！

如何跨表格同时进行复制粘贴？

●全选表格

→将全部的单元格中的工作表选中，按住「Ctrl+C」复制。

●复制粘贴

按住「Shift」键并依次单选我们需要录入的文档，黑马公社2、3、4，然后按住「Ctrl+V」即可在三个表格中同时粘贴我们刚刚录制的数据。

是不是 So So Fast 呢？

如何提取字符串中的数据？

一般人可能会直接一个一个的进行输入，这么做不但浪费了大量的时间，而且很有可能一不小心还会就将某个数据输错！

不一般的人呢，可能会用到 VB 函数将其分离来提取数字。

如果你嫌函数麻烦，或总是记不住，那就来看黑马是怎么做的吧！

●复制字符串

首先我们选中需要提取的字符串数，然后「Ctrl+C」将其复制并粘贴在我们相对应的单元格列中。

●缩小列宽

将相关表格的列宽缩小。

●两端对齐

→缩小后选择菜单栏中的 “开始” --> “填充”-->“两端对齐”。

→在弹出的提示：“文本超出选定区域” 中，选择确定即可。

●提取数据

→随意选择一个空白单元格并将其复制，复制后再选中需要提取的字符串数，然后按住快捷键「Ctrl + Shift +↓」键。

→再按住「Ctrl+Alt+V」快捷键，打开选择性粘贴对话框，在选择性对话框中，选择运算中的加选项，然后点击确定。

→再按住「Ctrl+G」快捷键，打开定位条件，选择 “定位条件” -->“常量”，将常量中的数字选项取消勾选，点击确定。

●删除多余的字符

在字符数字串这一列中，点击右键，选择 “删除” -->“下方单元格上移”。

●放大列宽

→最后我们将列宽放大，可以发现，放大后，所有的数字就被我们提取出来了！

→但是在这里，有一个表格中的数字使用了科学计数法，我们只需右击，找到 “设置单元格格式” -->“选择自定义”，再选择第二个选项即可。

常规的操作也能完成复杂的函数结果，要节约时间的赶快学起来了！

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老斯基