二维码 购物车
部落窝在线教育欢迎您!

如何拆分不规则字符?除了Ctrl+E,还有这两招!

 

作者:柳之来源:部落窝教育发布时间:2022-05-10 17:11:04点击:245

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

哈喽,大家好,今天来给大家分享一个字符拆分的问题,说起字符拆分,大家的第一反应可能是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

IMG_256

相关推荐:

如何提取品牌信息?LOOKUP函数有绝招!

没有Textjoin函数,如何解决提取数据的问题?

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

工资表转工资条,VLOOKUP有绝招!

版权申明:

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