【置顶公众号】或【设为星标】☝ 及时接收不迷路

关键字：函数；Ctrl+E；解决方案

栏目：你问我答

全文1435字， 预计4分钟读完

哈喽，小伙伴们，你们好呀~

说起Excel里的常用快捷键，Ctrl+E可谓是赫赫有名！

但就算是交口称赞的Ctrl+E也会有失灵的时候，我们又该如何操作呢？

今天来给大家分享两个解决方案，函数+VBA，赶快一起来看看吧！

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

表格是这样的：

A列是姓名加地址，有下面两点特殊情况需要注意：

（1）姓名中间有空格，空格的个数不定；

（2）姓名和地址之间有空格，空格个数也不同

希望得到的结果是B列和C列，其中B列为左边的姓名，C列为地址。

首先，她考虑的是用快速填充——Ctrl+E，尝试了半天，依然没有得到正确答案。

1.下面我们来说函数的操作。

先写C2单元格的公式：=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",1000)),500))

展开全文

再写B2单元格的公式：=TRIM(SUBSTITUTE(A2,C2,""))

这里，我们需要先提取地址，然后再提取姓名，来分析一下原因：

从文本中提取字符的函数主要有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,上一步获取的地址,""))

得到的结果为：哦级宏

结果呈现：

2. VBA方案

如果熟悉VBA的小伙伴也可以用代码来解决这个问题，代码有解释，详细如下：

Sub test

Dim reg As Object, mh, i&, ar

[b:c].Clear

Set reg = CreateObject("vb.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基础不扎实的同学，欢迎学习下面这套工作用得上的Excel训练营