用rept函数拆分数据就是好用

观察一下可以发现原数据的特点，都是以相同的分隔符(~)连接的。这让我想到了以前的拆分方式，以~为分隔符进行拆分，提取拆分后的每个值，就和分列的效果一样。效果图如下：

它的拆分公式为=TRIM(MID(SUBSTITUTE($A15,"~",REPT(" ",99)),COLUMN(A1)*99-98,99))。这个套路以前也说过好几次，首先将分隔符(~)替换为99个空格，然后用mid提取，提取的起始位置构成一个以1开始，99为等差的序列数，第一个单元的起始位置为1，第二个单元格的起始位置为100，第三个单元格的起始位置为199......以此类推；提取的长度都是99。提取后的字符串肯定会有多余的空格，最后用trim去除多余的空格。

我这样说，比较抽象，你可能理解不了。最好是按F9一步一步查看公式运行的结果；同时画出一个将分隔符替换为99个空格后的字符串的图，再来看它提取的起始位置和长度。如下图所示。

如果你上面那个公式看懂了，再来看今天的题目。同样还是将分隔符(~)替换为99个空格，提取的起始位置还是以1开始，99为等差的序列数，但是提取的长度改变了，变成了99*2。因为多提取了内容。后面同样是用trim去除多余的空格。trim后的效果为下图：

可以看到上面那个公式的结果有2个问题，第一，2个内容之间的分隔符变成空格；第二，如上图绿色所示，将最后一个分隔符后面的内容提取出来了，这部分是我们不要的。

首先需要将第1个问题解决，只需将空格替换为分隔符(~)就ok了。效果如下：

下面只需要解决第2个问题了。怎么将绿色部分给去掉？思路是这样的，用提取后内容的序列号和源数据中分隔符的个数比较，当序列号大于源数据分隔符的个数时，显示为空。以A2单元格的数据为例说明，ab~cd~efg中有2个分隔符(~)，提取后的内容分别是B2，C2，D2，它们对应的序列号分别是1，2，3。B2的序列号是1，不大于2，还显示它本来的值。D2的序列号为3，大于2，显示为空文本""。

公式为=IF(COLUMN(A1)>LEN($A2)-LEN(SUBSTITUTE($A2,"~",)),"",SUBSTITUTE(TRIM(MID(SUBSTITUTE($A2,"~",REPT(" ",99)),COLUMN(A1)*99-98,99*2))," ","~"))。结果如下图：

其中LEN($A2)-LEN(SUBSTITUTE($A2,"~",))这部分是计算A2中分隔符(~)的个数。虽然这个条件判断将之前绿色的部分给去除了，但同时也将B6的Excel给去除了，如上图红色部分所示。因为A6中分隔符(~)的个数为0，B6的序列号1大于0，所以为空文本。这样的话还需要加一个条件，就是源数据中包含分隔符(~)。

所以最后的条件是提取后内容的序号大于源数据中分隔符(~)的个数，且源数据中包含分隔符(~)。当这2个条件同时满足时，返回空文本，否则返回拆分的公式。完整的公式为=IF(AND(COLUMN(A1)>LEN($A2)-LEN(SUBSTITUTE($A2,"~",)),ISNUMBER(FIND("~",$A2))),"",SUBSTITUTE(TRIM(MID(SUBSTITUTE($A2,"~",REPT(" ",99)),COLUMN(A1)*99-98,99*2))," ","~"))。

还有其他公式可以完成，感觉都比较复杂和冗长。这个公式是我能想到的比较简短的公式。大家也可以想想其他的方法。

链接：

https://pan.baidu.com/s/1av_xNZvSmu-GA0NjGbU4Hw

提取码：7mbs

赞 (0)

相关推荐