汉字与数字混合字符中提取连续的数字和汉字

施施的EXCEL后备军 2021-08-27

907

有时候，在工作中需要将一段连续的字符（数字或者汉字分别提取出来），如图1所示，需要将房间名称与房间分离出来。

（图1）

【方法一】数组函数法。

解题思路：（以下涉及的函数均是数组函数，要按CTRL+SHIFT+ENTER组合键）

因各个数字和汉字均是连续的。只要求出第一个数字所在的位置，因为数字均是连续的，再数字的连续个数（长度），在用MID函数可以得到数字串。得到数字串后，再用替换函数SUBSTITUTE得到汉字。

【步骤】

1.设计数组函数求出第一个数字所在的位置：

=MIN(IFERROR(FIND(ROW($1:$10)-1,B2),9E+307))

然后按CTRL+SHIFT+ENTER组合键输出结果4。

函数解析：

①用ROW($1:$10)-1形成一个{0;1;2;3;4;5;6;7;8;9}数组；

②用FIND函数找出0-9每个数字所在的第一次出现位置；

③用IFERROR函数来屏蔽错误值。没有查找到相应数字符号就会返回错误值。

④用MIN函数求出第一个数字所在的位置。

备注：9E+307是EXCEL里的科学计数法，相当于9*10^307，接近于EXCEL中允许输入的最大数值。

2.设计另一个数组函数求出数字的个数（长度）：

=SUM(ISNUMBER(--MID(B2,ROW($1:$100),1))*1)

然后按CTRL+SHIFT+ENTER组合键输出结果3。

函数解析：

①用ROW($1:$100)形成一个0至100的数组；

②通过MID函数将B2中每个字符分开来。每次MID函数从第1个字符开始截取单位长度为1的字符，直到第100个字符。（当截图字符的开始位超过字符的总体长度，则返回结果为空。）；

③通过“--”（减负运算）使文字转为错误值，将文字数字转为常规数字；

④用ISNUMBER函数将减负运算后的数组转换成TRUE和FLASE的数组；

⑤用*1是因为在四则运算中，TRUE相当于1，FLASE相当于0，这样就把非数字的所有字符都转为0，数字的都转为1；

⑥用SUM函数将数组求和，得到的数值就是数字的长度。

3.用MID函数将两个函数融合起来，在C2中输入如下公式：

=MID(B2,MIN(IFERROR(FIND(ROW($1:$10)-1,B2),9E+307)),SUM(ISNUMBER(--MID(B2,ROW($1:$100),1))*1))

然后按CTRL+SHIFT+ENTER组合键。

如图2所示。

（图2）

4.得到数字串后，就可以用替换函数SUBSTITUTE得到汉字。

在D2输入如下公式：

=SUBSTITUTE(B2,C2,)

此函数不是数组函数，按ENTER键就可以了。

如图3所示。

（图3）

【方法二】智能填充法。

解题思路：在office2013版本以上的Excel中，可以通过先输入几个数据结果（一般来说，3-5个结果），然后通过智能填充，让Excel智能判断操作者意图，然后按CTRL+E组合键完成操作。

【步骤】

1.在C2:C4中分别输入：101、102、、103、104；

2.选中C2:C12，然后按CTRL+E完成。

如图4所示。

（图4）

这样就得到了数字串，然后也可以再次通过这个方法得到汉字。

Tips1：CTRL+E智能填充条件是OFFICE2013版本以上，有时候需要多输入几个数字才能填充，但这个功能也不是万能的，对于规律不明显的或者特征不明显的，即使输入再多，也不能识别。

Tips2：数组公式和数组思维是通往函数大门的必经之路，虽然有时候此函数不太容易理解，但是还是需要花费较多时间和精力去接触和熟悉它，直到掌握数组函数。

Tips3：如果文中有什么错误的地方，还请多多指正，感谢。

Tips4：万丈高楼平地起！

数据库

文章转载自施施的EXCEL后备军，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

汉字与数字混合字符中提取连续的数字和汉字

评论