蘭色看到一個同學提問:把括弧 () 內含有 * 號的內容提取出來。
肯定有不少人說:這還不容易,用
Ctrl E
就可以了。那蘭色就幫你試一試,結果是錯的。
餵!蘭色,你跑題了吧,今天的標題主角是
Vlookup
,這個問題和Vlookup有什麽關系?難道要用它?這不太可能吧,Vlookup什麽時候可以提取字元了?
沒錯, 蘭色試了很多方法,而用Vlookup函式寫的公式最簡單。
那公式怎麽寫,蘭色快告訴我。先別急,這個需要從最近蘭色遇到的另外一個簡單例子說起。
【例】如下圖所示在右表中設定Vlookup公式,根據E列的內容從左表中尋找,明明看上去一樣公式也沒錯誤,結果卻尋找不到。
=VLOOKUP(E2,$B$2:$C$6,2,0)
究其原因,原來是B列的內容後含有換行符。
換行符很多同學不知道怎麽給刪除掉(
可以用分列
),那只能在公式中處理。蘭色看了有不少同學用
clean
或
trim
,但有時候你的表中可能有這兩個函式刪除不掉的不可見字元。這時候,本文另一個主角要出場了,它就是標題中我們說的神秘字元
*
(
星號
)
* 是表示任意多個字元的通配符,Vlookup的第一個參數又支持用通配符尋找,所以二者合作,正好可以解決這個問題。
=VLOOKUP(E2&"*",$B$2:$C$6,2,0)
咱們理解了
Vlookup
+
*
的模糊尋找用法後,就可以解決今天遇到的超復雜字元提取問題了。
分析:
1、因為字串中有多組括弧,所以第一步我們需要找到*後的括弧位置
=FIND(")",A2, FIND("*",A2) )
公式說明:先尋找
*
號的位置(
因find不支持通配符,所以這裏只是一個普通符號,並不是通配符
),然後再用find函式尋找
*
後 "
)
" 的位置。FIND函式第3個參數為尋找開始位置。
2、用left函式把字元截取出來,括弧後面多余的部份刪除掉。
=LEFT(A2,FIND(")",A2,FIND("*",A2)))
3、從字串後用right函式分別截取1,2,3,4......15次(
提取次數要大於括弧內的字元長度
),你會發現終有一個是我們要想的結果。
4、問題是怎麽把兩邊有括弧的行提取出來?嘿嘿,這時候該Vlookup閃亮登場了,配合*號完美的實作了提取。
=VLOOKUP(" (*) ",RIGHT(LEFT(A2,FIND(")",A2,FIND("*",A2))),ROW(1:15)),1,0)
註:
(*)
表示模糊尋找兩邊有括弧的。
最終的公式為:
=VLOOKUP("(*)",RIGHT(LEFT(A2,FIND(")",A2,FIND("*",A2))),ROW($1:15)),1,0)
如果以為這只是
vlookup
和
*
偶爾套用,不足以支持「逆天」稱號,你可曾記得蘭色前段時間分享的另一個範例,也是用了同樣的思路,解決了一個超難的字串提取難題。
蘭色說 :透過今天的範例,是不是又重新整理了對Vlookup認知。這個你認為已完全掌握的函式,竟然還可以這麽用。
蘭色根據多年經驗,錄制了一全套適合新手和初中級階段使用者學習的Excel教程。包括 Excel表格88個函式用法( 即將更新幾十個新函式,範例整理中 )、119個使用技巧、透視表從入門到精通50集、圖表從入門到精通186集 。( 綠卡會員 有效期內 免費 )詳情點選下方連結