函数综合运用：提取特定文本后的数值

今天再来介绍一个常规思路的函数综合运用，方法不是最优的，关键是介绍一种解题思路。

遇到问题，先去分析，把思路搞清楚，然后再根据分析的思路每一步去找合适的函数或组合去实现。

多次这样锻炼以后，即使你掌握的函数个数或技巧没有增加多少，但是你的Excel综合应用水平和解决问题的能力会逐渐得到锻炼和提高的。

1

源数据和要达到的目的描述

如下图所示，要达到的目的是：

当C列为“Combination”时，提取D列“combine to”或者“combine with”后面的数字到B列。

2

首先，初步观察原始数据

通过观察原始数据，得到如下规律：

1）D列里的数据每个单元格中最多存在“combine to”或者“combine with”之一，有的单元格这两个词组都没有；

2）后面紧挨着的就是要提取的数字；

3）D列每个单元格内容可能以数字开头；

4）要提取的数字后面还有可能有数字；

5）行文格式是英文，每个单词后面都有一个空格。

（其实如果敏感还有更细致的观察，先不说，因为这个示例数据是我整理的典型数据，实际大量数据时前几个单元格可能观察不了那么全面，后续随着发现随着处理）

3

思路安排

通过观察上面的数据特点，可以想到如下思路：

1）首先要确定被提取数字的第一位的位置，那么很明显就是“combine to”或者“combine with”词组后面的空格后的第一个字符；

2）查找“combine to”或者“combine with”这样的字符串，用FIND函数就好啦；

3）忽然意识到，FIND函数能查找的是“combine to”或者“combine with”第一个字符“c”出现的位置，比如说=FIND('morning','good morning')结果返回6，就是“morning”第一个字符所在的位置，也就是good四个字符加上1个空格后整个字符串的第6个字符。

那么确定“combine to”或者“combine with”后面的数字的开头位置，就要用FIND函数查找的结果加上“combine to”或者“combine with”的字符长度然后再加1个空格的长度；

4）D列有些单元格没有“combine to”或者“combine with”，FIND函数查找会出现错误，怎么办？用IFERROR函数屏蔽就好了；

5）知道了要提取数字的位置，然后怎么办呢？我们可以把要提取数字左面的内容全部截掉，比如说“5897:combine to 353.3247:clark will provide the fix this friday.”这个文本，我们要留下“353.3247:clark will provide the fix this friday.”，这个你也会，就是用MID()函数从数字起始位置取99个字符嘛；

6）提取出“353.3247:clark will provide the fix this friday.”怎么办呢？怎么把这个文本最左面的数字353提取出来？

这个你可能不知道，但是你搜索一下”excel提取左边的数字“，相信你会找到类似LEFT+COUNT+LEFT+ROW或者LOOKUP+LEFT这种组合，研究一下你就懂得怎么提取了。

这样把问题逐步分解，不会的单独搜索一下，看似综合的一个问题就能解决了。下面具体来在表格中实现一下。

4

将解题思路在Excel表格中实现

首先，用FIND函数查找“combine to”或者“combine with”出现的位置。由于一次性要查找俩词组，所以我们用：

=FIND({'combine to ','combine with '},D2)

如果能找到某个词组，就返回词组第一个字符出现的位置，否则返回错误值。

然后，要加上“combine to”或者“combine with”和空格的字符长度，所以要提取数字出现的位置：

用LEN函数可以获得“combine to”或者“combine with”的字符长度+1个空格的字符长度={11,13}。

现在发现有错误值，下一步运算出错，怎么办？用IFERROR屏蔽吧：

=IFERROR({11,13}+FIND({'combine to ','combine with '},D2),)

就上面的数据来说，这一步返回{17,0}

错误值返回0，然后用MAX或者SUM将上面得到的17提取出来：

=MAX(IFERROR({11,13}+FIND({'combine to ','combine with '},D2),))

下一步用MID函数将文本从要提取数字的第一位开始，提取99个字符（99可以换成更大的数字）：

=MID(D2,MAX(IFERROR({11,13}+FIND({'combine to ','combine with '},D2),)),99)

这步骤结果返回：“353.3247:clark will provide the fix this friday.”

然后用刚搜索到的LOOKUP函数把最左面的353提取出来就可以啦。

=-LOOKUP(,-LEFT(MID(D2,MAX(IFERROR({11,13}+FIND({'combine to ','combine with '},D2),)),99),ROW(A$1:A$5)))

LOOKUP这里的用法简单说就是：-LOOKUP(0,-LEFT(文本,ROW($1:$N)))，这里的N是不小于要提取数字长度的数字即可，由于这里数据都是几百（三位数），所以公式中用的是5。

将公式拉下来，怎么有没有提取出来的呢？

仔细观察一下，原来combine有的大写，有的小写啊！

函数中用的都是小写，所以会出错。

这里，可以改变FIND函数为SEARCH，或者LOWER一下D列就可以啦。

这里添加一个LOWER：

=-LOOKUP(,-LEFT(MID(D2,MAX(IFERROR({11,13}+FIND({'combine to ','combine with '},LOWER(D2)),)),99),ROW(A$1:A$5)))

最后再加上C列是Combination这个条件，然后屏蔽下错误值，就得到了最终的公式：

=IFERROR(IF(C2='Combination',-LOOKUP(,-LEFT(MID(D2,MAX(IFERROR({11,13}+FIND({'combine to ','combine with '},LOWER(D2)),)),99),ROW(A$1:A$5))),''),'')

这样一步一步的分析下来，思路顺了，不会的点去查查，搜索一下，是不是就能解决你的实际问题了呢？第一次可能困难，那么经过第二次第三次，你肯定会提高的！

-LOOKUP(,-LEFT(字符串,ROW($1:$99)))这种用法自己搜索一下，如果不着急学习，等着以后公众号的解释吧。

图文作者：ExcelHome超级版主 shaowu459