欢迎转发和点一下“在看”,文末留言互动!
置顶公众号或设为星标及时接收更新不迷路
小伙伴们好,今天继续和大家分享FILTERXML函数的用法。如果你没有高版本函数,那么FILTERXML就是一款非常不错的文本处理函数,它能够处理许多复杂的问题。
原题目是这样的:
题目要求将A列中的源数据拆分成3列,如B、C和D列所示。这又是一个非常有规律的数据。分隔符“=”和“--”刚好将源数据分割成了最终结果所希望的。处理这类问题正是FILTERXML函数的强项。
有些朋友可能会问了,这道题目是否可以使用SUBSTITUTE函数的经典分列公式呢?答案是肯定的。不过,SUBSTITUTE函数却有一定的局限性。
在字符串很长,分列的字符串段长度不一致,分列的数量不固定时,TRIM+MID+SUBSTITUTE的组合就不适用了,而FILTERXML+SUBSTITUTE组合就能很好地处理。
在这一点上,FILTERXML函数的组合对源数据有更强的处理能力。
SUBSTITUTE($A2,"--","=")
利用SUBSTITUTE函数将“--”替换为“=”,为下面的替换做准备。
SUBSTITUTE(SUBSTITUTE($A2,"--","="),"=","</b><b>")
再次利用SUBSTITUTE函数将“=”替换为“</b><b>”。这也是FILTERXML函数对数据格式的要求。
"<a><b>"&SUBSTITUTE(SUBSTITUTE($A2,"--","="),"=","</b><b>")&"</b></a>"
在上面字符串的最左侧添加“<a><b>”,最右侧添加“</b></a>”之后,就可以使用FILTERXML函数来提取数值了。
最后在单元格B2中输入下列公式,并向下向右拖曳即可。
=INDEX(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE($A2,"--","="),"=","</b><b>")&"</b></a>","a/b"),COLUMN(A1))
提取的结果是三段字符{20000;"26*37*10.5";0.319},最后利用INDEX函数依次提取出来到对应的列中就可以了。
本期内容练习文件提取方式:
链接:https://pan.baidu.com/s/1Ebl6YCBvYt57mOiZ2AD1eA?pwd=uzk1
提取码:uzk1
-END-
长按下方二维码关注EXCEL应用之家
面对EXCEL操作问题时不再迷茫无助
我就知道你“在看”
联系客服