如何拆分不规则字符？除了Ctrl+E，还有这两招！
作者：柳之来源：部落窝教育发布时间：2022-05-10 17:11:04点击：1872
编按：
哈喽，大家好，今天来给大家分享一个字符拆分的问题，说起字符拆分，大家的第一反应可能是Ctrl+E。今天来给大家讲解Ctrl+E解决不了的特殊情况，赶紧来看一看吧！
这个案例来自我们的VIP学员，这个是她工作中的遇到的问题。
表格是这样的：
A列是姓名加地址，有下面几点特殊情况需要注意：
（1）姓名中间有空格，空格的个数不定；
（2）姓名和地址之间有空格，空格个数也不同
希望得到的结果是B列和C列，其中B列为左边的姓名，C列为地址。
这个题有一定的普遍意义，觉得大家的工作中很有可能会碰到，所以把思路在这里给大家分享一下。
首先，她考虑的是用快速填充——Ctrl+E，试了半天依然不对，下面我们来说函数的方法。
分析一下：
从文本中提取字符的函数主要有LEFT(从左侧提取，提取多少位)，RIGHT(从右侧提取，多位)，MID(从中间提取，提取起点，提取多少位)。本道题的难点在于，不管哪种提取方法，问题是不知道提取多少位。
如果先用find去确定空格的位置，在这道题中，有很多连续的空格，所以，不好用find定位。
所以，我们用的另外一个，稍微大胆的方法。用substitute替换，把任何一个空格都替换为1000个空格。
对于A2单元格，输入公式=SUBSTITUTE(A2," ",REPT(" ",1000))
这样A2就变成了这样的：
哦级宏 ................至少1000个空格............. 北京市三区
变成这样以后，提取地址是不是很方便了呢？我就可以用下面的公式
=RIGHT(SUBSTITUTE(A2," ",REPT(" ",1000)),500)
因为地址前面至少有1000个空格，我们从右边开始提取500个，不会提取到姓名，但是肯定会包含所有的地址。
得到的结果就是：
............空格........... 北京市三区
然后我们再在外面套上一个Trim处理一些前面的这些空格，就得到我们想要的地址了。
完整公式为：
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",1000)),500))
得到的结果为：北京市三区
提取姓名：
得到地址以后还没有完，我们还需要获取姓名。有了地址获取姓名就比较简单了。我们先用一个Substitute函数把地址替换为空：
=SUBSTITUTE(A2,上一步获取的地址,"")
得到的结果为：哦级宏 ...空格...
然后再用Trim函数再次去除文本两端的空格，公式如下：
=TRIM(SUBSTITUTE(A2,上一步获取的地址,""))
得到的结果为：哦级宏
结果呈现：
先写C2单元格的公式：=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",1000)),500))
再写B2单元格的公式：=TRIM(SUBSTITUTE(A2,C2,""))
这里，我们需要先提取地址，然后再提取姓名，刚才已经分析了原因。
VBA方案
如果熟悉VBA的小伙伴也可以用代码来解决这个问题，代码有解释，详细如下：
Sub test()
Dim reg As Object, mh, i&, ar()
[b:c].Clear
Set reg = CreateObject("vbscript.regexp") '创建正则对象reg
reg.Pattern = "(S+ *S+(?= )) *(S+$)"
'设置reg的匹配样式，S +表示非空字符，+表示1个以上， *表示任意个空格，再跟一串汉字，直到遇上空格。再跟任意个空格，加上一串汉字结尾（$表示结尾）
'将第一串和最后一串分组，用于提取出匹配的字段
ReDim Preserve ar(1 To [a1].End(xlDown).Row, 1 To 2) '重定义数组大小
For i = 1 To [a1].End(xlDown).Row '循环区域中的单元格
Set mh = reg.Execute(Cells(i, 1).Value) '使用execute方法
ar(i, 1) = mh(0).SubMatches(0) '赋值数组，因为是完全匹配，所以只有一个匹配值，mh的item只有一个，所以用mh(0).
ar(i, 2) = mh(0).SubMatches(1) '由于有两个括号的分组，所以在match对象下的submatches属性中会有两个结果的集合，抽取第一个值用submatches(0)
第二个值用submatches(1)
Next i
[b1].Resize([a1].End(xlDown).Row, 2) = ar '重定义单元格大小，返回数组结果至单元格
以上，便是今天的所有内容，大家赶紧来试一试吧！
本文配套的练习课件请加入QQ群：902294808下载。
做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！
扫下方二维码关注公众号，可随时随地学习Excel：
相关推荐：
版权申明：
本文作者柳之；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。