如何根据相同分隔符提取间隔数据？

最近遇到很多提问怎么提取字符的，而这些问题都有一个相同的特征，就是要提取的内容与内容之间，都有着相同的分隔符。当然，这种问题直接用“数据” →  “分列”功能就可以一步到位实现的，但有人喜欢折腾，而更多的人又非得指定函数公式的方法，或者更多的是要保持数据的同步性。

 

下面，我们就来讲讲用函数公式应该怎么实现这个提取，首先来个数据和要求，如下图，将 - 号间隔的内容依次提取到右边单元格内：

 

如果你用FIND+MID来提取，那肯定也是可以实现的，尤其是左边第一串字符；只是你越做就越发觉，到后面你要嵌套很多个FIND、很多个MID……

 

我们先说说思路，我们可以将分隔符，如示例中用到的“-”，统一替换成N多个空格，这样原字符串就会是由空格隔开的很多串字符，接下来，我们只需要根据空格的长度和字符的长度，找到一个平衡点作为长度，从当中截取这个长度的字符串，然后再把空格清除掉应该就可以实现了，只要保证截取的长度里，多余字符只是空格，而不会与相隔的字符串有干涉。

 

当然，这个思路的理论是完全正确的，而这个N经过多方的实验，也有较为合理的值，至于平衡点，其实就可以直接＝N，下面看看通常的公式写法：

=TRIM(MID(SUBSTITUTE($A2,"-",REPT(" ",99)),COLUMN(A1)*99-98,99))

 

我们下面来一步步解读一下这个公式，由于最外层的TRIM()函数只是为了把多余的空格清除掉，所以这个可以不作深究，那么剩下的主体函数就是MID了，而MID函数的语法也简单，只有三个参数：

MID(text, start_num, num_chars)

那么，我们就来讲讲MID函数内部这三个参数分别都是怎么写的：

 

  1. text  →  SUBSTITUTE($A2,"-",REPT(" ",99))

这个就是MID要提取数据的源文本，而这个源文本就是我们思路里讲到的，将分隔符“-”统一替换为N个空格，从上面的式子我们了不难发现，这个N就等于99，REPT函数就是根据指定次数，将某个字符重复指定的次数，因此我们没必要在替换函数SUBSTITUTE的第三参数里，用手敲99个空格。

 

  2. start_num  →  COLUMN(A1)*99-98

这个初看可能会犯晕，其实COLUMN函数跟ROW函数一样，是构造等差数列的利器，因为COLUMN(A1)就是返回A1单元格所在列的列号，自然是等于1，当我们公式向右拉时，就会变成COLUMN(B1)，也就返回2，依此类推，生成1、2、3、4、5的自然数，而*99-98，你可以在单元格写写然后拖动一下，构造的数列其实就是第1个99、第2个99、第3个99……也就是1、100、199、298、397，可能你看着还有点晕吧，那待会看看示意图。

 

  3. num_chars  →  99

这个最简单了，就是从指定起始位置，取99个字符。

 

其实所有的内容，只要涉及数学或者逻辑的，就很难说清楚了，因此函数公式是最最最不好讲解的，只能讲个大概，其余的就靠个人的基础和悟性了。

所以，将上面的步骤画了一个示意图，你可以参照着上面的步骤和示意图中对字符截取长度和截取内容详细分析一下，然后吸收消化。

 

至于我们的N为什么取99呢？其实这个数值是越大越安全，也越不容易在截取时发生相隔字符干涉情况的，但对于普通文本来说，99这个两位数基本上就是足够安全的，因为你的字很少有可能会超过45个字符，而为了公式简洁，大家都约定俗成使用这个最大的两位数整数。

因此你可以根据具体的数据和提取情况，用9、15、30、或者50都是可以的，只要保证不干涉就可以了，但除了9，你用15和用99其实就没有区别了……所以又回到了出发点。

 

 

这里讲的是最通用的方法，如果你掌握了，那后续用函数公式来处理类似下面的数据，应该就不会六神无主了：

24斤、34斤、31斤、38斤  →  求重量总和

5小时6小时9小时4.5小时   →  求时间总和

25*34*2*2.5*15*26   →  求计算式的运算结果

还有很多储如此类的，如果分隔符不统一，你可以先把不一致的处理掉，然后从0开始。反正要合理运用SUBSTITUTE~~

 

 

------------------------------

Excel难题需要有偿处理的，请点击：

http://excelstudio.taobao.com

 

函数公式写、数据样式转换、数据提取分析、图表制作、VBA代码、SQL查询、网页抓取.....