今天记录一个关于字符串拆分的案例

需求

将一个单元格中多行字符串，按内容拆分成多行多列！

先来看看源数据，总共3列，区域代码、项目代码和投保标的物信息，投保标的物信息中每一行都用空行间隔。
任一行字符串，又分为4段，第1段是标的物，第2段是数量，第3段是单位，第4段是总投保金额。我们需要提取每一段中，“：”后与空格前这之间的内容。
最后的输出结果是这样的，区域代码、项目代码、标的物、数量、单位和总投保金额。一个标的物一行，项目有多个标的物，就输出多行。

尝试使用 VBA 和 Power Query 两种解决方法。

VBA

点击查看VBA代码：Excel VBA 正则表达式拆分字符串

Power Query

今天一起来学习下使用Excel Power Query来解决这个案例。

第一步：加载到Power Query

点击数据区域中任意单元格，点击菜单“数据”下的“来自表格和区域”，创建表勾选“表包含标题”，来到Power Query编辑界面。

第二步：替换掉投保标的物信息中的空行。

点击菜单“转换”下的“替换值”，展开“高级选项”，勾选“使用特殊字符替换”，插入特殊字符下拉，选择“换行符”,点击“确定“。

第三步：将投保标的物信息按不同标的物拆分成多行。

按分隔符‘；’（分号）拆分，点击菜单“添加列”下的“自定义列”，在自定义公式的“=”后输入，“Text.Split([投保标的物信息],";")”，注意函数大小写，点击“确定“。

第四步：展开自定义列并删除投保标的物信息列。

点击自定义列右侧双向箭头，选择“扩展到新行”；在投保标的物信息列上单击右键，点击“删除”。

第五步：再次拆分字符串。

观察一下每一行的数据，需要提取位于“：“后与空格之间的内容。所以再次对每一行数据进行拆分。按分隔符“：“和空格进行拆分，使用 Text.SplitAny。点击菜单“添加列”下的“自定义列”，新列名输入“自定义2 “，在自定义公式的“=”后输入，“Text.SplitAny([自定义],": ")”，点击“确定“。注意": "中冒号后有一个空格。

第六步：筛选拆分结果。

点击任意一个List，发现里面包含了所有字符内容，只要提取对应的标的物名称、数量、单位和总投保金额，其它的元素不需要。再仔细看，发现标的物名称、数量、单位和总投保金额是从第2个开始，每隔2个，保留1个。这里就需要使用List列表的 List.Alternate 函数。在 Text.SplitAny 外层继续输入，“List.Alternate(Text.SplitAny([自定义],": "),2,1,2) “。

第七步：跳过不需要的拆分结果。

再次点击任意List，发现需要的元素都已经提取出来了，但第1个元素标的物名称，不是需要的，可以使用 List.Skip 跳过。在 List.Alternate 外层继续输入，"List.Skip(List.Alternate(Text.SplitAny([自定义],": "),2,1,2), 1)“ 。 List.Skip 默认跳过第1个，后面的1可以省略，变成”List.Skip(List.Alternate(Text.SplitAny([自定义],": "),2,1,2))“。

第八步：把拆分结果转成表。

在 List.Skip 外层继续输入,” Table.FromRows(List.Skip(List.Alternate(Text.SplitAny([自定义],": "),2,1,2)))”。结果出现了 Error，报错了。点击任意一行看一下，提示“无法将值转换成List”，我们就手动将值转成List，在 List.Skip 外层手工输入一对{}。注意输入的位置。

第九步：删除不要的列，展开表。

在自定义列上单击右键，点击“删除”。点击自定义2右侧双向箭头，然后点击“确定”。
列名不对，编辑栏中第一组{}中是Table中的原列名，第二组{}中是展开后的新列名。在编辑栏把第二组{}的新列名，改成想要的列名，这里分别改成"标的物", "数量", "单位", "投保金额"（这个根据实际需要修改）。

第十步：修改列的数据类型。

在"标的物", "数量", "单位", "投保金额"列上 ABC123 的上面点击，选择合适的数据类型。

最后：上载结果到表中。

依次点击菜单“主页”下的“关闭并上载”，选择“关闭并上载至”，在导入数据界面选择“表”，放置位置选择“新工作表”，点击“确定”，完成。