|
歡迎轉(zhuǎn)發(fā)和點(diǎn)一下“在看”，文末留言互動(dòng)! 置頂公眾號(hào)或設(shè)為星標(biāo)及時(shí)接收更新不迷路 
朋友們好，這里是EXCEL應(yīng)用之家，堅(jiān)持分享EXCEL操作技巧。 今天要和大家分享一道數(shù)據(jù)拆分題目。題目很簡(jiǎn)單，一起來(lái)看看！ 原題目是這樣的： 
怎么樣按要求完成如右側(cè)的結(jié)果呢？ 
在單元格C2中輸入下列公式，并向下向右拖曳即可。 =IFERROR(INDEX(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"<","@"),">","<b>"),"@","</b>")&"</b></a>","a/b"),COLUMN(A1)),"")
海鮮大法我們?cè)?jīng)多次介紹過(guò)，這里就不再贅述了。如有任何問(wèn)題請(qǐng)給我留言吧！ 觀察一下答案，雖然我們把數(shù)值提取出來(lái)了，但是和要求的答案還是有一些差異的。
完整代碼如下： Sub 拆分?jǐn)?shù)據(jù)() Dim i As Integer, k As Integer Dim arr As Variant, brr As Variant, regex As Object, mh As Object, d As Object Set regex = CreateObject("vbscript.regexp") arr = Range("A1").CurrentRegion ReDim brr(1 To UBound(arr), 1 To 5) With regex .Pattern = "\d+(?=\<|$)" .Global = True End With For i = 1 To UBound(arr) Set mh = regex.Execute(arr(i, 1)) For Each d In mh k = k + 1 brr(i, k) = d Next k = 0 Next [C13].Resize(UBound(arr), 5) = brrEnd Sub
整個(gè)過(guò)程比較簡(jiǎn)單，簡(jiǎn)單說(shuō)兩句。 regex.Pattern = "\d+(?=\<|$)"
這個(gè)是正則表達(dá)式。它的含義是，在字符串末尾的數(shù)字；或者那些后面跟著符號(hào)“<”的數(shù)字。 For i = 1 To UBound(arr) Set mh = regex.Execute(arr(i, 1)) For Each d In mh k = k + 1 brr(i, k) = d Next k = 0Next
通過(guò)一段循環(huán)代碼，將每個(gè)匹配到的數(shù)值都錄入到數(shù)組brr中。 完整代碼如下： Sub 拆分?jǐn)?shù)據(jù)1() Dim i As Integer, k As Integer Dim arr As Variant, brr As Variant, regex As Object, mh As Object, d As Object Set regex = CreateObject("vbscript.regexp") arr = Range("A1").CurrentRegion ReDim brr(1 To UBound(arr), 1 To 5) With regex .Pattern = "(\d+)(?=\<)|(\d+)$" .Global = True End With For i = 1 To UBound(arr) Set mh = regex.Execute(arr(i, 1)) For Each d In mh k = k + 1 brr(i, k) = d Next k = 0 Next [C13].Resize(UBound(arr), 5) = brrEnd Sub
這段代碼中的正則表達(dá)式是稍有不同的，但最終效果一致。 鏈接: https://pan.baidu.com/s/1d246w-lzRvmUVkkzDd9Oxg?pwd=6ja8 提取碼: 6ja8 好了朋友們，今天和大家分享的內(nèi)容就是這些了！喜歡我的文章請(qǐng)分享、轉(zhuǎn)發(fā)、點(diǎn)贊和收藏吧！如有任何問(wèn)題可以隨時(shí)私信我哦！-END-
長(zhǎng)按下方二維碼關(guān)注EXCEL應(yīng)用之家 面對(duì)EXCEL操作問(wèn)題時(shí)不再迷茫無(wú)助
|
