EXCEL中混合文本中如何提取數字部分?

這幾天在百度知道裡回答關於EXCEL的問題。面對各種各樣的問題，讓我感覺我所了解的EXCEL知識就算是解決常用基本問題，也是不能妥善解答的。比如前天回答了一個關於AVERAGE()函數的問題，求平均數函數，常用吧？問題是問AVERAGE(13，TRUE，-5)等於多少？什麼？還有邏輯值？趕緊查AVERAGE在包含邏輯值時是否計算……

短路

這只是一個小插曲，我在回答的問題中有很多是字符提取或數字提取的問題，比如：如果提取數量單位混合填寫中的數量值，或是如何提起車牌號的最後一位數字？大家了解麼？今天我們就來聊一聊這個問題。

如何提取數量單位混合填寫中的數量值：

如下圖，如何提取數量/單位中的數量值？如果是數量值或是單位位數統一，比如都是元、萬元等，直接數值-分列即可。但下圖中恰恰是數量和單位位數都不統一，那如何取值？

如何提取數量/單位中的數量值

SUBSTITUTE函數+字符串提取函數

我想的用常用的公式組合可以用兩種方法解決：

一、SUBSTITUTE函數+字符串提取函數，先看公式C13=SUBSTITUTE(B13,RIGHT(B13,LENB(B13)-LEN(B13)),"")

分解一下公式：

1、LENB(B13)-LEN(B13)=1，利用LENB()和LEN()函數計算字符長度不同作相減，得到即文本中的漢字個數（關於LENB和LEN函數的介紹，參見關於文本函數的發文）；

2、RIGHT(B13,LENB(B13)-LEN(B13))，即RIGHT(B13,1)=「元」；

3、SUBSTITUTE(B13,RIGHT(B13,LENB(B13)-LEN(B13)),"")，即SUBSTITUTE(B13,"元","")=3000，SUBSTITUTE()函數是常用替換函數之一，結構是SUBSTITUTE(文本內容，需要替換的文本，替換為)，所以該函數即從"3000元"中，將「元」替換為空，從而得到文本中的數字部分。

過程基本如上，注意該函數組合因為是文本替換函數為結尾，最後得到的數值是文本型。所以截圖中3000是靠左的。

二、LOOKUP函數+字符串提取函數+ROW()，第一種提取雖然可以完成，但我不作重點推薦，個人偏好使用EXCEL中的查找神器LOOKUP函數，這個是可以成套路的。

先看公式C12=-LOOKUP(1,-LEFT(B12,ROW($2:$100))),接下來分解一下：

LOOKUP函數+字符串提取函數+ROW()

1、ROW($2:$100)=(1,2,……100)，ROW()行函數，沒啥可說的。為啥是100？只是為了足夠大，其實超過被查文本字符數即可；

2、-LEFT(B12,ROW($2:$100))，=-LEFT(B12,(1,2……100)),即將B12從左邊開始按照1、2……數量取字符，結果為-LEFT("1","18","18.","18.9","18.9萬","18.9萬元"，……"18.9萬元"），LEFT()添加負號後，純數值文本轉為負數，含有文本字符的文本轉換為"#value!"；

3、-LOOKUP(1,-LEFT(B12,ROW($2:$100)))，第一參數是1，所以在有LEFT生成的數組中忽略錯誤值，提取最後一個數值，最後外圍再加一個符號，轉換為正值，即題目所需。

該函數取得的結果是數值型，可以直接加和等處理。

好了，今天的混合文本提取數值的內容就介紹到這裡，最後給大家留已到文章開頭提到的題目，大家可以用LOOKUP+字符串提取+ROW()套路函數思考一下：下圖中如何提取車牌尾號？答案下期發文公布。

如何提起車牌尾號？

晚安