今天
就給大家分享的
案例
就是這個(群裡某位群友的需求)
,先看圖
0
1
圖片中,B列
(
標紅字元
)
包含某些關鍵字(C
1:H1
就是 關鍵字),它後面都有一串數字,現在需要將這些數字提取出來,
知道規則
就好辦了
,
提取的方法有很多。
這裡就拿
F
ind
、Len、Middle,
Replac
和
IfError
這
五
個函式來巢狀解決吧。
先來簡單認識一下這
5
個函式
Find
函式
,將某個字元在
另一個
字串中的位置給它找出來,什麼意思呢?還是來寫個例子來看吧
=FIND(“土豆”,“今天土豆賣了15塊錢”)
返回結果是3
意思是把
“
土豆
”
在字元
串
“
今天土豆賣了15塊錢
”
中的什麼位置給算出來,所以結果是3,還是放單元格中看一下結果吧!
L
en
函式
,返回字元數(字元長度),繼續剛才的例子
=LEN(“今天土豆賣了15塊錢”)
返回結果是1
0
因為是直接使用的字串,所以兩邊的雙引號是不能算進去的,趕緊放單元格測試吧!
Mid
函式
,從字串中指定位置開始,擷取指定長度的字元,什麼意思呢,來看一個簡單例子
=MID(“今天土豆賣了15塊錢”,3,2)
返回結果是土豆
意思是從
“
今天土豆賣了15塊錢
”
這個字串裡的第
3
個字元開始,擷取
2
個字元出來,
所以結果是土豆,
把它複製到單元格里測試一下吧!
R
eplace
函式
,將字串中從指定位置開始,指定長度的內容替換為新內容,測試公式
=REPLACE(“今天土豆賣了15塊錢”,1,2,“”)
返回結果是
土豆賣了15塊錢
從第一個字元開始,連續2個字元,替換為空,所以得出了此結果,趕緊放單元格測試吧!
If
E
rror
函式
,將錯誤
值處理
成需要的新值
,來做小測試,認識一下
=IFERROR(“今天土豆賣了15塊錢”+1,“土豆
好笨”)
返回結果是
土豆好笨
因為字串無法做四則運算,所以這個計算式會返回錯誤,因此會返回
土豆
好笨,
堅持繼續在單元格里測試一下吧!
好啦,函式的基本功能都有了瞭解以後,就可以開始解題了!
既然要根據C
1
:H
1
的內容來提取,那我們就要先查詢一下這些關鍵字在B列中的位置,這就要用到Find函式,來看一下公式圖吧,圖0
2
寫好以後,記得驗證一下位置對不對喲,右
拉下拉
填充好以後我們發現有錯誤值,
那是因為這些關鍵字在B列不存在
,這裡先不管它,最後再用
ife
rror
函式來處理。
既然是擷取,那就要計算出從第幾個字元開始擷取對吧,顯然這裡的1,1
4
,2
6
並不能直接使用,因這它只找出來關鍵字所在位置,要從這個位置開始,加上關鍵字長度,再加個1
(關鍵字後面都有個逗號)
才是我們所需要數字的開始位置,
瞭解清楚這一點後,我們再給它加個Len函式計算一下,就可以了,來看公式圖0
3
這樣計算出來的
8
,2
1
,3
3
,是不是剛好就是這些關鍵字所需數字的開始位置了呢?(怎麼會這麼巧??)
開始位置拿到以後,就可以擷取資料啦,
因為關鍵字後面數字長度不一樣,為了降低挑戰難度,
這裡先大致擷取一
段出來再說,來看擷取公式圖0
4
到這裡你可能會問Mid的引數3為什麼是7?因為我
大致看
了一下數值大小不會破百萬,所以這裡用7足夠了。
從目前的結果來看,裡面還有很多我們不需要的內容,這時候有同學會說,我可以複製
貼上為值
,然後再查詢替換,其實這也是
很方便快捷的方法
,不過
今天是
函式
的戲
,
那
就用
得讓函式演到底了。
好拉!
再看一下現在的結果,不難看出,每個數字後面都有逗號,所以我們只需要將逗號及後面的都刪除掉,就可以了。
接下來就得使用Repl
ace
函式來處理了,來看公式圖
4
這一次公式就變老長了,不要怕,在寫之前,先複製一下,然後再寫,對應的位置直接貼上公式就好了,
為什麼R
eplace
的引數3要用9呢?其實我們前面只截取了7個字元出來,按最少一個數字來算,那後面最多隻有6個字元是需要處理掉的,9比6
大保證
不會有遺漏。(你也可以用9
9
,9
99
反正不是錢,隨便你用)
接下來就是處理錯誤值啦,把錯誤
值處理面
空值,直接套個
Iferror
函式在外面就行了,看圖
5
擔心有同學會抄錯,我這裡把最終公式直接貼出來,你們可以貼上進驗證一下
=IFERROR(REPLACE(MID($B2,FIND(C$1,$B2)+LEN(C$1)+1,7),FIND(“,”,MID($B2,FIND(C$1,$B2)+LEN(C$1)+1,7)),19,“”),“”)
最後再放一張公式字元數相對少些的公式圖出來,圖
6
此公式有對擷取
出來的字元
長度要求比較嚴格,使用起來要特別注意下!
有沒有發現,其實函式巢狀並不難,分段來寫,
一
步步實現了再放到對應的引數中就可以了!