【原】这是一道非常难的文本拆分题目，据说只有1%的人可以做出来！

EXCEL应用之家 2023-07-21 发布于上海

展开全文

送人玫瑰，手有余香，请将文章分享给更多朋友

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

【置顶公众号】或者【设为星标】及时接收更新不迷路

小伙伴们好，字符串拆分始终是我们遇到的难点之一。我们学习过SUBSTITUTE函数、LOOKUP函数和FILTERXML函数等等都可以实现某些特点的字符串拆分。今天要想大家分享的这一组公式，是用来对付那些无规律的字符串拆分，并可以应用于今后类似的问题。

原题是这样子的：

怎么样，是不是有一种老虎吃天，无从下口的感觉？在2019版本及以上、365中有新版函数可以解决这类问题。如果你还不能使用新版函数，那么就一定要收藏这一篇推文了！

先来看看如何提取数字。最基本的思路就是，我们首先要定位到每一个数字最左边的那一个，然后就可以利用LOOKUP函数来提取数字了。

在单元格A6中输入公式“=IFERROR(LOOKUP(9^9,MID(A$1,SMALL(IF(ISERR(-MID("A"&A$1,ROW($1:$100),1))*ISNUMBER(-MID(A$1,ROW($1:$100),1)),ROW($1:$100)),ROW(A1)),ROW($1:$10))*1),"")”，三键回车并向下拖曳即可。

思路：

ISNUMBER(-MID(A$1,ROW($1:$100),1))部分，将源数据中每一个字符都提取出来，通过“-”这个小技巧后来判断是否为数字
ISERR(-MID("A"&A$1,ROW($1:$100),1))部分，在源数据最左侧添加一个字符，实现一个位置的错位。然后同样是提取、添加“-”并判断是否为错误值
以上两部分相乘，得到的结果是{1;0;0;0;0;0;0;1;0;0;0;0;0;1;0;0;0;0;1;0;0;0;0;1;0;0;0;0;0;0;0;1;0;0;0;1;0;0;0;1;0;0;0;1;0;0;0;1;0;0;0;0;1;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}。你看，这些“1”所对应的位置正好是每组数字的第一个位置
接下来利用IF函数将每一个“1”都返回一个对应的自然数，行成一个序列
SMALL函数依次提取第1、2、3..小的序列值
MID函数从当前定位点向右提取长度为1、2、3..个字符串，例如，{"3";"33";"331";"331晋";"331晋国";"331晋国大";"331晋国大乱";"331晋国大乱3";"331晋国大乱35";"331晋国大乱35三"}
乘以1后将文本型数字转换为数值型数字
LOOKUP函数的经典用法提取到完整数值

数字部分完成后我们接下来再来看看文字部分。文字部分的公式思路和数字部分是一致的，只是在公式书写上略有不同。

在单元格B6中输入公式“=IFERROR(MID(A$1,SMALL(IF(ISNUMBER(-MID("A"&A$1,ROW($1:$100),1))*ISERR(-MID(A$1,ROW($1:$100),1)),ROW($1:$100)),ROW(A1)),MATCH(,0/MID(A$1&9,SMALL(IF(ISNUMBER(-MID("A"&A$1,ROW($1:$100),1))*ISERR(-MID(A$1,ROW($1:$100),1)),ROW($1:$100)),ROW(A1))+ROW($1:$10)-1,1),)-1),"")”，三键回车并向下拖曳即可。

思路：

SMALL(IF(ISNUMBER(-MID("A"&A$1,ROW($1:$100),1))*ISERR(-MID(A$1,ROW($1:$100),1)),ROW($1:$100)),ROW(A1))这部分的作用和前面介绍的一样，是定位每一个汉字的第一个位置。具体的写法和前面略有不同，小伙伴们可以自己分解
定位完成后，利用0/()这样的技巧将所有的0值变为错误值，其余的数值变为0值

MATCH(,0/MID(A$1&9,SMALL(IF(ISNUMBER(-MID("A"&A$1,ROW($1:$100),1))*ISERR(-MID(A$1,ROW($1:$100),1)),ROW($1:$100)),ROW(A1))+ROW($1:$10)-1,1),)-1这部分中：

SMALL(IF(ISNUMBER(-MID("A"&A$1,ROW($1:$100),1))*ISERR(-MID(A$1,ROW($1:$100),1)),ROW($1:$100)),ROW(A1))部分依次提取第1、2、3..小的值，也即每组汉字部分的第一个字的位置

上面的部分确定了MID函数提取汉字的起始位置。下面将要确定需要提取的字符串的长度。

MID(A$1&9,SMALL(IF(ISNUMBER(-MID("A"&A$1,ROW($1:$100),1))*ISERR(-MID(A$1,ROW($1:$100),1)),ROW($1:$100)),ROW(A1))+ROW($1:$10)-1,1)部分，随着SMALL第二参数ROW(A1)的增大，依次在每次找到的第一个汉字的位置上开始，向右依次移动1、2、3..个字符，并提取每一个字符。这里A$1&9是为了容错，可以使任意一个数字
MID函数提取后的内存数组中包含当前汉字字符串的第一个位置往后的所有当个字符。这里就有汉字字符，也有数字字符
MATCH(,0/())部分返回紧接着当前汉字字符串的第一个数字的位置，因为比当前汉字字符串多一位，所以最后要减去1，得到当前汉字字符串的长度
接下来，就是提取、屏蔽错误值了

下面给大家提供一个彩蛋。这是一条公式完成。

在单元格D6中输入公式“=MID(LEFT($A$1,SMALL(IF(MMULT(--ISERR(-MID($A$1&1,ROW($1:$99)+{0,1},1)),{3;1})=3^(COLUMN(A1)>1),ROW($1:$99)),ROW(A1))),SUM(LEN(C$5:E5),LEN(C6),1),99)”，三键回车并向下拖曳即可。

朋友们可以自己拆解这条公式吗？有问题可以私信我哦

本期内容练习文件提取方式：

链接：https://pan.baidu.com/s/1Hv163ixqD5OBuG9KRSUkAg?pwd=o2bj

提取码：o2bj

好了朋友们，今天和大家分享的内容就是这些了！喜欢我的文章请分享、转发、点赞和收藏吧！如有任何问题可以随时私信我哦！

-END-

长按下方二维码关注EXCEL应用之家

面对EXCEL操作问题时不再迷茫无助

我就知道你“在看”