【动态数组】通过TEXTSPLIT函数批量处理数据清洗问题

【动态数组】通过TEXTSPLIT函数批量处理数据清洗问题

要想下班早，表格必须用的好！

场景

今天接到一个用户咨询，说是领导给了从公司系统导出的数据，现在需要根据A列的数据提取出对应的省级放在B列，其他的市级放在从C列向右录入，具体数据如下图所示：

问题分析

首先需要根据数据进行分析，可以看出我们需要删除将“SJ1['”、“']=new Array('”、“','”和“');”替换掉，才能得到我们需要的数据，以福建省数据为例，具体如下图所所示：

我们首先想到的函数肯定是SUBSTITUTE函数来将“SJ1['”和“');”替换为空，再将“']=new Array('”和“','”替换为","来作为分隔符，最后再用TEXTSPLIT函数来按列分隔。具体公式和效果如下：

=TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"SJ1['",""),"');",""),"']=new Array('",","),"','",","),",")

📢 关于SUBSTITUTE函数使用方法，后期会专门出教程进行分享。

看来是可以得到效果的，不过就是公式写的有点长，单说SUBSTITUTE函数就嵌套了4层，外面再次嵌套了TEXTSPLIT函数，那如果有很多种这样的呢？那岂不是又要嵌套到地老天荒、海枯石烂了？如果你也经常遇到这样的问题，那就不妨看过来吧？学会一个技巧，少走很多弯路。

此时我们有没有想过用了这么多层SUBSTITUTE函数，还需要用到TEXTSPLIT函数，有没有想过只需要TEXTSPLIT函数即可搞定呢？

实现效果

=TEXTSPLIT(A2,{"SJ1['","']=new Array('","','","');"},,1)

为了对函数便于理解，我特别加了颜色加以区分，具体如下：

公式解析

录入常量数组

如果我们有很多个需要替换的字符需要替换或拆分时，我们可以选择使用TEXTSPLIT函数，其实TEXTSPLIT函数的参数2和参数3是支持以数组形式展示的。

因此我们只需要将“SJ1['”、“']=new Array('”、“','”和“');”写成数组形式即可。

数组形态为{"","","",""}，只需将各个不需要的符号写入对应的位置即可得到如下效果：

{"SJ1['","']=new Array('","','","');"}

作为分隔符拆分

最后将以上数组当做TEXTSPLIT函数的参数2按列拆分即可，具体公式和效果如下：

=TEXTSPLIT(A2,{"SJ1['","']=new Array('","','","');"})

忽略空白单元格

发现在开头和结尾有空白单元格，在前面的教程中也提及到，当分隔符在开头或结尾时，会出现空白单元格，此时我们只需要通过TEXTSPLIT函数的参数4（输入1）来忽略空白单元格，具体公式和效果如下：

=TEXTSPLIT(A2,{"SJ1['","']=new Array('","','","');"},,1)

方法总结

将很多个内容批量替换为空

当我们需要将很多内容批量替换为空时，我们可以尝试用TEXTSPLIT函数的参数2或参数3的数组形式拆分即可，然后用CONCAT函数连接。

将很多内容批量替换为某字符

当需要将很多内容批量替换为某字符时，我们一样可以尝试用TEXTSPLIT函数的参数2或参数3的数组形式拆分即可，然后用TEXTJOIN函数的参数1（替换的某字符）连接。

关于TEXTSPLIT函数批量处理数据清洗问题，你学会了吗？