Excel字符拆分方法：当Ctrl+E不合用时，还能怎么处理？

A列是姓名加地址，有下面几点特殊情况需要注意：先写C2单元格的公式：=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",1000)),500))

编按：哈喽，大家好，今天来给大家分享一个字符拆分的问题，说起字符拆分，大家的第一反应可能是Ctrl+E。今天来给大家讲解Ctrl+E解决不了的特殊情况，赶紧来看一看吧！

这个案例来自我们的VIP学员，这个是她工作中的遇到的问题。

表格是这样的：

（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,上一步获取的地址,""))

得到的结果为：哦级宏

结果呈现：

再写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 '重定义单元格大小，返回数组结果至单元格

以上，便是今天的所有内容，大家赶紧来试一试吧！

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！

学习交流请加微信：hclhclsc进微信学习群。

如何提取品牌信息？LOOKUP函数有绝招！

没有Textjoin函数，如何解决提取数据的问题？

如何在特定位置批量插入空行等12种实用办公技巧

工资表转工资条，VLOOKUP有绝招！

版权申明：

本文作者柳之；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。