送人玫瑰,手有余香,请将文章分享给更多朋友
动手操作是熟练掌握EXCEL的最快捷途径!
【置顶公众号】或者【设为星标】及时接收更新不迷路
小伙伴们好,今天要和大家分享一则文本提取的问题。
我曾经多次和大家强调,在数据录入的时候就要做好数据的规范录入,否则,在后期的统计、计算过程中会产生无穷无尽的问题。这不,今天就是这样一个例子。
需要把左侧的源数据拆分成右侧的格式。是谁做的源数据录入?先拉出去打50大板!
观察一下源数据,我们发现它有以下几个特点:
要拆分的数据都在文本“收款存款”右侧
除了车型“丰田RAV4”没有车牌号以外,其余的车型都有广西壮族自治区的号牌
姓名中除了“叶生”是两个字以外,其余的人的姓名都是三个字
找到这些以后,我们就可以构思公式了。
这里可以使用海鲜大法解决这个问题。
思路:
MID($A1,FIND("款",$A1)+1,100)部分,提取“收款存款”右侧的所有文本。后面所有的操作都是基于这部分。其结果是"汉兰达桂AJP586刘先生18517827356"
SUBSTITUTE(SUBSTITUTE(MID($A1,FIND("款",$A1)+1,100),"V4","V4*京"),"叶生","叶生生"),连续用两个SUBSTITUTE函数将文本字符串中的“V4”后面加上一个“*京”,使其在格式上和其它数据一致。这里可以使用任何你喜欢的汉字;同时将"叶生"替换为"叶生生"
REPLACE(SUBSTITUTE(SUBSTITUTE(MID($A1,FIND("款",$A1)+1,100),"V4","V4*京"),"叶生","叶生生"),LEN(SUBSTITUTE(SUBSTITUTE(MID($A1,FIND("款",$A1)+1,100),"V4","V4*京"),"叶生","叶生生"))-13,0,"*")部分,将文本字符串中第14位,也就是姓名前插入一个“*”,其结果是"汉兰达桂AJP586*刘先生18517827356"
REPLACE(REPLACE(SUBSTITUTE(SUBSTITUTE(MID($A1,FIND("款",$A1)+1,100),"V4","V4*京"),"叶生","叶生生"),LEN(SUBSTITUTE(SUBSTITUTE(MID($A1,FIND("款",$A1)+1,100),"V4","V4*京"),"叶生","叶生生"))-13,0,"*"),LEN(REPLACE(SUBSTITUTE(SUBSTITUTE(MID($A1,FIND("款",$A1)+1,100),"V4","V4*京"),"叶生","叶生生"),LEN(SUBSTITUTE(SUBSTITUTE(MID($A1,FIND("款",$A1)+1,100),"V4","V4*京"),"叶生","叶生生"))-13,0,"*"))-10,0,"*")部分,再上一步的基础上将文本字符串中第15位,也就是手机号码前添加一个“*”,其结果是"汉兰达桂AJP586*刘先生*18517827356"
SUBSTITUTE(REPLACE(REPLACE(SUBSTITUTE(SUBSTITUTE(MID($A1,FIND("款",$A1)+1,100),"V4","V4*京"),"叶生","叶生生"),LEN(SUBSTITUTE(SUBSTITUTE(MID($A1,FIND("款",$A1)+1,100),"V4","V4*京"),"叶生","叶生生"))-13,0,"*"),LEN(REPLACE(SUBSTITUTE(SUBSTITUTE(MID($A1,FIND("款",$A1)+1,100),"V4","V4*京"),"叶生","叶生生"),LEN(SUBSTITUTE(SUBSTITUTE(MID($A1,FIND("款",$A1)+1,100),"V4","V4*京"),"叶生","叶生生"))-13,0,"*"))-10,0,"*"),"桂","*桂")部分,将车牌“桂”字前添加一个“*”,其结果是"汉兰达*桂AJP586*刘先生*18517827356"
下面就简单了,利用INDEX函数依次提取这四项数据,同时将最开始是替换的“京”用空字符替换掉,“叶生生”替换会“叶生”即可。
如果不追求一个公式完成,那么操作就会简单很多。
-END-
长按下方二维码关注EXCEL应用之家
面对EXCEL操作问题时不再迷茫无助
我就知道你“在看”
联系客服