精准香港透码总部

Excel 用函數在Excel中從文本字符串提取數字

時間:2012-07-16 02:16來源:Office教程學習網 精准香港透码总部 www.lloyp.icu編輯:麥田守望者

精准香港透码总部 www.lloyp.icu Excel輸入數據過程中,經常出現在單元格中輸入這樣的字符串:GH0012JI、ACVB908、華升12-58JK、五香12.56元、0001#、010258等。在進行數據處理時,又需要把其中的數字0012、908、12-58、12.56、0001提取出來。

如何通過使用Excel的工作表函數,提取出字符串中的數字?

一、問題分析

對于已經輸入單元格中的字符串,每一個字符在字符串中都有自己固定的位置,這個固定位置都可以用序列數(1、2、3、……)來表示,用這些序列數可以構成一個可用的常數數組。

以字符串“五香12.56元”為例:序列數1、2、3、4、5、6、7、8分別對應著字符串“五香12.56元”中字符“五”、“香”、“1”、“2”、“.”、“5”、“6”、“元”。由序列數組成一個保存在內存中的新數組{1;2;3;4;5;6;7;8}(用列的形式保存),對應字符串中的字符構成的數組{“五”;“香”;“1”;“2”;“.”;“5”;“6”;“元”}。因此解決問題可以從數組著手思考。

二、思路框架

問題的關鍵是,如何用序列數重點描述出字符串中的數字部分的起始位置和終止位置,從而用MID函數從指定位置開始提取出指定個數的字符(數字)。

不難看出,兩個保存在內存中的新數組:

{“五”;“香”;“ 1”;“2”;“.”;“5”;“6”;“元”}

{1;2;3;4;5;6;7;8}

數組具有相同大小的數據范圍,而后一個數組中的每一個數值可以準確地描述出字符串中字符位置。

字符與序列數的對應關系如下表所示:

字符 字符位置

五 —— 1

香 —— 2

1 —— 3

2 —— 4

. —— 5

5 —— 6

6 —— 7

元 —— 8

所以解決問題的基本框架是:

用MID函數從字符串的第一個數字位置起提取到最后一個數字止的字符個數。即{=MID(字符串,第一個數字位置,最后一個字符位置-第一個字符位置+1}。其中“+1”是補上最后一個數字位置減去第一個數字位置而減少的一個數字位。

三、解決方案及步驟

假定字符串輸入在A2單元格。

⑴確定A2中字符串的長度。

即用LEN函數計算出A2中字符串中字符的個數,這個字符個數值就是字符串中最后一個字符在字符串中的位置:=LEN(A2)。

⑵確認字符串中的每一個字符位置序列數組成的新數組。

用INDIRECT函數返回一個由文本字符串指定的引用:

=INDIRECT("1:"&LEN($A2))

用返回行數的函數ROW確定文本引用INDIRECT("1:"&LEN($A2))構成的新數組:{=ROW(INDIRECT("1:"&LEN($A2)))}

⑶用按指定位置開始返回指定個數字符的函數MID返回由新數組{=ROW(INDIRECT("1:"&LEN($A2)))}確定位置的每一個字符,并將文本轉化成數值型數據:

{=--MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)}

注意:

函數MID返回的字符是文本,將文本轉化為數值型數據,可以用函數VALUE,也可以同等功能地用符號“- -”或“+0”或“-0”簡化表達,這里用“- -”表示。

⑷函數ISNUMBER判別MID函數提取出來的字符是不是數字,是數字返回TRUE,不是數字返回FALSE。

具體公式是:

{=ISNUMBER(--MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1))}

⑸邏輯函數IF根據用函數ISNUMBER檢測MID函數提取出來的字符是否數值的真假,返回數字字符在字符串中的位置,如果不是數字則返回空白字符。

具體公式是:

{=IF(ISNUMBER(--MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)),ROW(INDIRECT("1:"&LEN($A2))),"")}

⑹用MIN函數返回數字位置數組成數組中的最小數。

具體公式是:

{=MIN(IF(ISNUMBER(--MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)),ROW(INDIRECT("1:"&LEN($A2))),""))}

⑺用MAX函數返回數字位置數組中的最大數。

具體公式是:

{=MAX(IF(ISNUMBER(--MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)),ROW(INDIRECT("1:"&LEN($A2))),""))}

⑻確認字符串中第一個數字的起始位置:

{=MIN(IF(ISNUMBER(--MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)),ROW(INDIRECT("1:"&LEN($A2))),""))}

⑼確認字符串中第一個數字與最后一個數字之間的字符個數:

{=MAX(IF(ISNUMBER(--MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)),ROW(INDIRECT("1:"&LEN($A2))),""))- MIN(IF(ISNUMBER(--MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)),ROW(INDIRECT("1:"&LEN($A2))),""))+1}

注意:

公式中的“+1”,是對字符串中最后一個數字位數減去第一個數字位數,造成第一個數字與最后一個數字之間的字符個數少1的補充。

⑽用函數MID在A1中按指定位置開始提取指定個數的字符(數字)。

綜上所述,第⑻步的公式為MID函數的第2個參數,第⑼步的公式為MID函數的第3個參數。組合后提取A1中數字的具體公式如下。

在B2單元格編輯公式:

=MID($A2,MIN(IF(ISNUMBER(--MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)),ROW(INDIRECT("1:"&LEN($A2))),"")),MAX(IF(ISNUMBER(--MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)),ROW(INDIRECT("1:"&LEN($A2))),""))-MIN(IF(ISNUMBER(--MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)),ROW(INDIRECT("1:"&LEN($A2))),""))+1)

用三鍵確認公式輸入,即用組合鍵Ctrl+Shift+Enter進行公式確認。

本公式不適用的文本字符串類型:形如WE1234GH098PIU等。

四、適當簡化公式

基于文本數字轉化為數值型數字表達方式——用函數VALUE、符號“--”和“+0”或“-0”效果完全一致,所以具體的提取文本中數字的公式可以適當簡化為:

{=MID($A2,MIN(IF(ISNUMBER(MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)+0),ROW(INDIRECT("1:"&LEN($A2))))),MAX(IF(ISNUMBER(MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)+0),ROW(INDIRECT("1:"&LEN($A2)))))-MIN(IF(ISNUMBER(MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)+0),ROW(INDIRECT("1:"&LEN($A2)))))+1)}

五、編后語

特別指出,對于提取文本中的數字,本公式不是最簡方法,同時也不一定是最佳方案。

通過對字符串中數字的提取操作,試圖用Excel的工作表函數直接來完成原始的數字提取工作,因此編輯的公式冗長;庖丁解牛的解決方案試圖說明對文本中數字提取的想法和函數、數組原始的理解及使用,從而描述清楚整個公式構成框架。

基于對Excel知識及函數和數組掌握的水平及語言敘述表達的能力,可能存在很多不足或錯誤,算是拋出來的一塊磚吧,誠望有識者斧正。
 

------分隔線----------------------------
標簽(Tag):excel excel2007 excel2010 excel2003 excel技巧 excel教程 excel實例教程 excel2010技巧
------分隔線----------------------------
推薦內容
猜你感興趣