用函数在Excel中从文本字符串提取数字

59 阅读 0 评论 39 点赞

我是靠谱客的博主务实荔枝，最近开发中收集的这篇文章主要介绍用函数在Excel中从文本字符串提取数字，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

Excel输入数据过程中，经常出现在单元格中输入这样的字符串：GH0012JI、ACVB908、华升12-58JK、五香12.56元、0001#、010258等。在进行数据处理时，又需要把其中的数字0012、908、12-58、12.56、0001提取出来。

如何通过使用Excel的工作表函数，提取出字符串中的数字？

一、问题分析

对于已经输入单元格中的字符串，每一个字符在字符串中都有自己固定的位置，这个固定位置都可以用序列数（1、2、3、……）来表示，用这些序列数可以构成一个可用的常数数组。

以字符串“五香12.56元”为例：序列数1、2、3、4、5、6、7、8分别对应着字符串“五香12.56元”中字符“五”、“香”、“1”、“2”、“.”、“5”、“6”、“元”。由序列数组成一个保存在内存中的新数组{1；2；3；4；5；6；7；8}（用列的形式保存），对应字符串中的字符构成的数组{“五”；“香”；“1”；“2”；“.”；“5”；“6”；“元”}。因此解决问题可以从数组着手思考。

二、思路框架

问题的关键是，如何用序列数重点描述出字符串中的数字部分的起始位置和终止位置，从而用MID函数从指定位置开始提取出指定个数的字符（数字）。

不难看出，两个保存在内存中的新数组：

{“五”；“香”；“1”；“2”；“.”；“5”；“6”；“元”}

{1；2；3；4；5；6；7；8}

数组具有相同大小的数据范围，而后一个数组中的每一个数值可以准确地描述出字符串中字符位置。

字符与序列数的对应关系如下表所示：

字符字符位置

五 —— 1

香 —— 2

1 —— 3

2 —— 4

. —— 5

5 —— 6

6 —— 7

元 —— 8

所以解决问题的基本框架是：

用MID函数从字符串的第一个数字位置起提取到最后一个数字止的字符个数。即{=MID（字符串，第一个数字位置，最后一个字符位置-第一个字符位置+1}。其中“+1”是补上最后一个数字位置减去第一个数字位置而减少的一个数字位。

三、解决方案及步骤

假定字符串输入在A2单元格。

⑴确定A2中字符串的长度。

即用LEN函数计算出A2中字符串中字符的个数，这个字符个数值就是字符串中最后一个字符在字符串中的位置：LEN（A2）。

⑵确认字符串中的每一个字符位置序列数组成的新数组。

用INDIRECT函数返回一个由文本字符串指定的引用：

=INDIRECT("1:"&LEN($A2))

用返回行数的函数ROW确定文本引用INDIRECT("1:"&LEN($A2))构成的新数组：{=ROW（INDIRECT("1:"&LEN($A2))）}

⑶用按指定位置开始返回指定个数字符的函数MID返回由新数组{=ROW（INDIRECT("1:"&LEN($A2))）}确定位置的每一个字符，并将文本转化成数值型数据：

{=--MID($A2,ROW(INDIRECT("1:"&LEN($A2)))，1)}

注意：

函数MID返回的字符是文本，将文本转化为数值型数据，可以用函数VALUE，也可以同等功能地用符号“- -”或“+0”或“-0”简化表达，这里用“- -”表示。

⑷函数ISNUMBER判别MID函数提取出来的字符是不是数字，是数字返回TRUE，不是数字返回FALSE。

具体公式是：

{=ISNUMBER(--MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1))}

⑸逻辑函数IF根据用函数ISNUMBER检测MID函数提取出来的字符是否数值的真假，返回数字字符在字符串中的位置，如果不是数字则返回空白字符。

具体公式是：

{=IF(ISNUMBER(--MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)),ROW(INDIRECT("1:"&LEN($A2))),"")}

⑹用MIN函数返回数字位置数组成数组中的最小数。

具体公式是：

{=MIN（IF(ISNUMBER(--MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)),ROW(INDIRECT("1:"&LEN($A2))),"")）}

⑺用MAX函数返回数字位置数组中的最大数。

具体公式是：

{=MAX（IF(ISNUMBER(--MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)),ROW(INDIRECT("1:"&LEN($A2))),"")）}

⑻确认字符串中第一个数字的起始位置：

{=MIN（IF(ISNUMBER(--MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)),ROW(INDIRECT("1:"&LEN($A2))),"")）}

⑼确认字符串中第一个数字与最后一个数字之间的字符个数：

{=MAX（IF(ISNUMBER(--MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)),ROW(INDIRECT("1:"&LEN($A2))),"")）- MIN（IF(ISNUMBER(--MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)),ROW(INDIRECT("1:"&LEN($A2))),"")）+1}

注意：

公式中的“+1”，是对字符串中最后一个数字位数减去第一个数字位数，造成第一个数字与最后一个数字之间的字符个数少1的补充。

⑽用函数MID在A1中按指定位置开始提取指定个数的字符（数字）。

综上所述，第⑻步的公式为MID函数的第2个参数，第⑼步的公式为MID函数的第3个参数。组合后提取A1中数字的具体公式如下。

在B2单元格编辑公式：

=MID($A2,MIN(IF(ISNUMBER(--MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)),ROW(INDIRECT("1:"&LEN($A2))),"")),MAX(IF(ISNUMBER(--MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)),ROW(INDIRECT("1:"&LEN($A2))),""))-MIN(IF(ISNUMBER(--MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)),ROW(INDIRECT("1:"&LEN($A2))),""))+1)

用三键确认公式输入，即用组合键Ctrl+Shift+Enter进行公式确认。

本公式不适用的文本字符串类型：形如WE1234GH098PIU等。

四、适当简化公式

基于文本数字转化为数值型数字表达方式——用函数VALUE、符号“--”和“+0”或“-0”效果完全一致，所以具体的提取文本中数字的公式可以适当简化为：

{=MID($A2,MIN(IF(ISNUMBER(MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)+0),ROW(INDIRECT("1:"&LEN($A2))))),MAX(IF(ISNUMBER(MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)+0),ROW(INDIRECT("1:"&LEN($A2)))))-MIN(IF(ISNUMBER(MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)+0),ROW(INDIRECT("1:"&LEN($A2)))))+1)}

五、编后语

特别指出，对于提取文本中的数字，本公式不是最简方法，同时也不一定是最佳方案。

通过对字符串中数字的提取操作，试图用Excel的工作表函数直接来完成原始的数字提取工作，因此编辑的公式冗长；庖丁解牛的解决方案试图说明对文本中数字提取的想法和函数、数组原始的理解及使用，从而描述清楚整个公式构成框架。