细说数据拆分这点事，看完本篇后就都明白了？

大家好，欢迎来到IT知识分享网。

动手操作是熟练掌握EXCEL的最快捷途径！

数据拆分

TIPs：LENB(A2)-LEN(A2)可提取汉字；2*LEN(A2)-LENB(A2)可提取数值

EXCEL最强大的功能就是数据处理，于是，大家避免不了会遇到从一段较长的文本数据中提取符合某一特征的一段文字或数字。这时候该怎么办呢？

01

分列功能

这个是EXCEL自带的功能，大家都会用。它分为按“分隔符号”分列和按“固定宽度”分列。因为按“固定宽度”比较简单，就不再详细介绍了。

下面着重讲讲按“分隔符号”分列。

上图是向导的第二步。顾名思义，就是按照某些符号来分割数据。如上图，EXCEL提供了一些分割的符号，你只需要勾选就可以了。

强调一下最后一个选项。你可以在最后一项“其它”中输入你希望的分隔符：符号、字母，甚至汉字都可以。EXCEL为你提供了无限的分列的可能！

分列

你可以按任意方式来进行分列

难度评定：☆

02

使用LEN函数

这里先介绍两个文本函数：

LEN函数：=LEN(TEXT)，它返回字符串中的字符个数

LENB函数：=LENB(TEXT)，它返回字符串中的字节个数

在单元格B2中输入“=RIGHT(A2,LENB(A2)-LEN(A2))”即可。

在单元格C2中输入“=–LEFT(A2,2*LEN(A2)-LENB(A2))”即可。

思路：

• 由于1个汉字是2个字节，因此LENB(A2)-LEN(A2)可提取汉字；2*LEN(A2)-LENB(A2)可提取数值
• –运算将文本型数值转换为真正的数值

数据拆分

TIPs：LENB(A2)-LEN(A2)可提取汉字；2*LEN(A2)-LENB(A2)可提取数值

难度评定：☆

03

使用LOOKUP函数

关于LOOKUP函数的使用，请参考帖子总结篇-LOOKUP函数实用终极帖。

这里重点将如何用它来提取数据。

在单元格C2中输入“=-LOOKUP(1,-LEFT(A2,COLUMN(A:X)))”即可。

思路：

• 用COLUMN(A:X)来产生一个新的数组{1,2,…24}
• 用LEFT函数分别提取“1,16,168…”，并形成一个新的数组
• 加负号是数字类型的数据变成负数
• 使用LOOKUP函数在数组中查找“1”
• 查找不到“1”，因此返回数组中最后一个数字类型的数据“-”
• 最后负负得正，得到正确的答案

难度评定：☆☆

04

使用SUBSTITUTE函数

关于SUBSTITUTE函数的用法请参考之前有一个帖子Substitute函数使用简介。

这里重点将如何用它来提取数据。

在单元格D2中输入“=–RIGHT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},””))))”并CTRL+SHIFT+ENTER回车即可。

在单元格C2中输入“=LEFT(A2,LEN(A2)-LEN(D2))”即可。

思路：

• 利用SUBSTITUTE函数用“”代替原字符串中0-9的数字
• 利用LEN函数计算替代后的字符串长度
• 用替代前的字符串的长度减去替代后的字符串的长度，得出被替代的0-9每个数字的个数
• 将以上求和，得出原字符串中数字的位数
• 最后分别用LEFT函数和RIGHT函数将文本和数字分别提取出来。

难度评定：☆☆

05

使用SEARCHB函数

当遇到中文和英文混合的文本时，利用SEARCHB函数和MIDB函数，就可以向下面一样提取英文字符。

SEARCHB函数的语法结构和SEARCH函数的语法结果相同。具体请参看如何使用find函数和search函数精确查找字符。MIDB同理。

在单元格B2中输入“=MIDB(A2,SEARCHB(“?”,A2),2*LEN(A2)-LENB(A2))”并向下拖曳即可。

思路：

• 利用SEARCHB函数的通配符查找功能，查找字符串中首个单字节字符
• 2*LEN(A2)-LENB(A2)是单字节字符串的长度
• 利用MIDB函数从首个英文字符开始向右截取指定长度的字符串

难度评定：☆☆

06

综合应用（一）

当遇到中文和数字混合的文本时，就可以向下面一样提取数值。

在单元格B2中输入“=-LOOKUP(1,-MIDB(A2,SEARCHB(“?”,A2),COLUMN(A:W)))”并向下拖曳即可。

思路：

• 利用SEARCHB函数的通配符查找功能，查找字符串中首个单字节字符
• COLUMN(A:W)提供了1-23这样一个数组
• 利用MIDB函数从首个单字节字符开始向右依次提取长度为1-23的字符串，通过负运算将数字转换为负数，文本转换为错误值
• 利用LOOKUP函数查找整数，并返回最后一个负数
• 负运算得到正确的结果

难度评定：☆☆☆

07

综合应用（二）

当遇到中文和英文规格型号组成的字符串时，可以按下面的方法提取中文。

在单元格B2中输入“=MID(A2,MATCH(2,LENB(MID(A2,ROW($1:$99),1)),),LENB(A2)-LEN(A2))”并CTRL+SHIFT+ENTER回车，向下拖曳即可。

思路：

• MID(A2,ROW($1:$99),1)：返回字符串中的单个字节
• 利用LENB函数返回每个字符的字节数。汉字返回2，其它返回1
• 利用MATCH函数查找2，确定首个汉字的位置
• LENB(A2)-LEN(A2)返回汉字字符串的长度
• 再次利用MID函数取得正确结果

难度评定：☆☆☆☆

08

综合应用（三）

当英文字符串和数字构成文本时，由于无法使用SEARCHB函数嵌取得首个数字的位置，就可以利用下面的方法来取得数字。

在单元格B2中输入“=-LOOKUP(1,-MID(A2,MIN(FIND(ROW($1:$10)-1,A2&1/17)),ROW($1:$15)))”

并CTRL+SHIFT+ENTER回车，向下拖曳即可。

思路：

• 利用ROW函数构造0-9的数组
• 1/17的值包含了0-9所有的数字，和上面的数组结合在一起可以避免FIND函数查找不到数字而返回错误值
• 利用FIND函数查找10个数字在文本中的位置，结合MIN函数返回在文本中的最小位置，即首个数字的位置
• 利用MID函数向右截取长度为1-15的字符串
• 利用LOOKUP函数取得正确结果

难度评定：☆☆☆☆☆

09

综合应用（四）

当中文、英文和数字混合时，如何提取数字？

在单元格B2中输入“=–LEFT(TEXT(SUM((0&MID(A2,SMALL(IF((MID(A2,ROW($1:$99),1)>=”0″)*(MID(A2,ROW($1:$99),1)<=”9″),ROW($1:$99),100),ROW($1:$15)),1))*10^(15-ROW($1:$15))),REPT(0,15)),COUNT(-MID(A2,ROW($1:$99),1)))”即可。

思路：

记住，会套用！记住，会套用！记住，会套用！

难度评定：☆☆☆☆☆☆