编按:哈喽,大家好,今天来给大家分享一个字符拆分的问题,说起字符拆分,大家的第一反应可能是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("") '创建正则对象reg
= "(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 = (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有绝招!
版权申明:
本文作者柳之;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。
下一篇:最后一页
“谢谢选择我做你的妈妈!” 这封信请18年后查收 扬子晚报讯(通讯员 刘威 记者 朱鼎兆)小时候,母亲常常在家里给我们留字条,
跟新冠病毒“赛跑” 他要让机器人完成核酸检测 经常学生们还不知道我怎么想的时候,我就把自己否定了。工作中需要有自我否定的勇气
助力无接触配送 上海无人车“上岗” 【疫情防控新举措】 科技日报讯 (记者符晓波)眼下,上海疫情蔓延趋势得到有效控制,不少
“态靶辨治” 帮助患者快速转阴 近日,随着患者清零,吉林省长春市北湖奥体中心篮球馆方舱医院等多个方舱陆续“休舱”,各医疗队也
四省市联合医疗队为患者全方位“解忧” 【同心守沪抗疫】 在上海城市足迹馆定点医院的宣传墙上,各类慢性病、基础病的健康宣教手
周美亮: 搜寻野生荞麦的“追种人” ◎本报记者 马爱平 一走进位于国家作物种质库新库内的中国农业科学院作物科学研究所研究员
防晒“神器”竟是珊瑚“杀手” 科技日报北京5月8日电 (实习记者张佳欣)珊瑚礁是地球上生物最丰富、最具经济价值的生态系统之一。
X 关闭
X 关闭