excel单元格分割填充方法

在前面作者介绍了提取指定符号之间、或两个相同符号之间的文本内容，以及提取单元格中某字符最后一次出现位置的后方的所有文本内容，或提取该符号指定次数出现的位置之前或之后的文本内容……

总之，关于提取单元格内的指定文本内容，讲解了很多的案例和方法。

而今天要介绍的主题，依然是提取单元格的指定内容，但这次的任务，是要在一行单元格中引用一个单元格中的各部分文本内容。

我们根据实例来边介绍边操作，以下数据表的源数据列是某加工企业钢料配料组合。

如下图所示，源数据中有些单元格包含了一个或多个指定相同符号“+”号，它表达多个数据组合，现在需要创建多个数据列来依次填充每个位置的数据组合。

当源数据中的单元格内容不包含加号，则就是表示它只有一个数据组合，在第1列中引用该数据即可！

按照上面的说明，其实我们可以理解为以“+”号为分割符，将单元格数据分割成多个文本，并依次填充到多个单元格中。

我们来看下单元格分割填充的效果图：

解决这个问题，看起来是有规律的，因为指定符号是相同的，都是“+”号，但我们遍历excel各类函数，发现并没有能够直接提出指定内容的函数。

但其实这可能是因为作者版本的原因。

在目前最新的excel365版本中，出现了一个文本分割函数——TEXTSPLIT函数！

这个函数的作用就是按照指定符号来分割单元格内容，而且可以设置多个符号来进行分割。

所以这个函数应用在这个场景中，绝对再合适不过了。

但是！

最新版office365是要搓五指的，因此作者在这里介绍一个组合公式，来引用单元格指定符号前后的文本内容。

而且在这个公式中，应用了一些常见场景中可以直接套用的函数表达，其解题的逻辑也比较有意思。

那闲言少叙，马上进入正题。

由于要引用所有单元格内所有的数据组合，因此涉及到加号之前、之间和之后的文本内容，而且有些单元格并不包含加号，因此为了避免N个if函数的嵌套，和后续单元格的填充，作者在第1列单独设置了一个公式。

该公式为：=IFERROR(LEFT(A2,FIND("+",A2)-1),A2)

这个公式容易理解，iferror是容错函数，当left函数表达式错误时，会返回第2参数A2。而left函数又嵌套了find函数，find函数在其中作用是查询“+”号在A2单元格中的位置，当单元格中没有“+”号，那么find函数会返回错误值，这时iferror函数进行容错，返回设置的第2参数值。

那通过这个公式，我们可以填充得到下图中的第1列数据。

接下来就到了关键部分，将通过一个公式来填充引用单元格内的后续所有数据组合。

作者写下公式：

{=IF(

(LEN($A2)-LEN(SUBSTITUTE($A2,"+","")))（if函数第1参数）

"",（if函数第2参数）

IFERROR( （if函数第3参数）

MID($A2, （iferror函数第1参数）

SUM(LEN($B2:B2))+1+COUNTA($B$1:B$1),

FIND("+",$A2,SUM(LEN($B2:B2))+1+COUNTA($B$1:B$1))-SUM(LEN($B2:B2))-1-COUNTA($B$1:B$1)),

RIGHT($A2,LEN($A2)-LOOKUP(9999,FIND("+",$A2,ROW(A:A))))) （iferror函数第2参数）

)}

作者将完整公式进行了换行显示，但显然看上去还是不大好理解，最好的方法是将公式中各个函数表达式复制出来在单元格中单独运行，并解析。

但由于篇幅和时间原因，作者仅介绍一下其中最后一个表达式：RIGHT($A2,LEN($A2)-LOOKUP(9999,FIND("+",$A2,ROW(A:A)))))

它作为iferror函数的第2参数，当第1参数mid函数表达式的结果出现错误时，就会返回这个公式的结果，而这个公式的结果是提取单元格中最后一个“+”号后的数据组合。

right函数是文本函数，作用是从字符串右侧提取指定字符数量的文本内容。

LEN函数是返回字符串的长度，或者说字符数量。

它通过LOOKUP(9999,FIND("+",$A2,ROW(A:A)))的固定表达来返回最后一个“+”号的位置，然后使用len(A2)的结果进行相减，得到最后一个“+”之后的字符数量，最后利用right函数提取出这个字符数量的文本内容。

那么这个right函数组合公式实际就是求解最后一个符号后数据内容的固定表达，而整个if函数公式，也是一个可套用的数组公式。

当我们需要提取和引用类似场景中的数据时，便可以复制粘贴这个公式，来得到填充结果。当然，也要记住第1列的公式，它们缺一不可！

作者把完整公式粘贴到下方：

{=IF((LEN($A2)-LEN(SUBSTITUTE($A2,"+","")))

"",IFERROR(MID($A2,SUM(LEN($B2:B2))+1+COUNTA($B$1:B$1),FIND("+",$A2,SUM(LEN($B2:B2))+1+COUNTA($B$1:B$1))-SUM(LEN($B2:B2))-1-COUNTA($B$1:B$1)),RIGHT($A2,LEN($A2)-LOOKUP(9999,FIND("+",$A2,ROW(A:A))))))}

举报/反馈