用函數(shù)在Excel中從文本字符串提取數(shù)字

1、用函數(shù)在 Excel 中從文本字符串提取數(shù)字Excel輸入數(shù)據(jù)過程中，經(jīng)常出現(xiàn)在單元格中輸入這樣的字符串：GH0012JI、ACVB908華升12-58JK、五香12.56元、0001#、010258等。在進(jìn)行數(shù)據(jù)處理時(shí)，又需要把其中的數(shù)字 0012、 908、 12-58、 12.56、 0001 提取出來。如何通過使用 Excel 的工作表函數(shù)，提取出字符串中的數(shù)字？一、問題分析 對(duì)于已經(jīng)輸入單元格中的字符串， 每一個(gè)字符在字符串中都有自己固定的位置， 這個(gè)固定位 置都可以用序列數(shù)（ 1、 2、 3、 , ）來表示，用這些序列數(shù)可以構(gòu)成一個(gè)可用的常數(shù)數(shù)組。以字符串“五香 12.56 元”為

2、例：序列數(shù) 1、 2、 3、 4、 5、 6、 7、 8分別對(duì)應(yīng)著字符串“五 香 12.56 元”中字符“五”、“香”、“ 1”、“2”、“.”、“5”、“6”、“元”。由 序列數(shù)組成一個(gè)保存在內(nèi)存中的新數(shù)組1 ；2；3；4；5；6；7；8（用列的形式保存），對(duì)應(yīng)字符串中的字符構(gòu)成的數(shù)組 “五”； “香”；“1”；“2”；“.”；“5”；“6”；“元”。 因此解決問題可以從數(shù)組著手思考。二、思路框架問題的關(guān)鍵是， 如何用序列數(shù)重點(diǎn)描述出字符串中的數(shù)字部分的起始位置和終止位置，從而用MID函數(shù)從指定位置開始提取出指定個(gè)數(shù)的字符（數(shù)字）。不難看出，兩個(gè)保存在內(nèi)存中的新數(shù)組：“五”；“香”；“ 1

3、”；“ 2”；“ . ”；“ 5”；“ 6”；“元”1 ； 2；3；4；5； 6；7；8數(shù)組具有相同大小的數(shù)據(jù)范圍， 而后一個(gè)數(shù)組中的每一個(gè)數(shù)值可以準(zhǔn)確地描述出字符串中字 符位置。字符與序列數(shù)的對(duì)應(yīng)關(guān)系如下表所示：字符字符位置五 1香 21 32 4 . 55 66 7元 8所以解決問題的基本框架是：用MID函數(shù)從字符串的第一個(gè)數(shù)字位置起提取到最后一個(gè)數(shù)字止的字符個(gè)數(shù)。即=MID (字符串， 第一個(gè)數(shù)字位置， 最后一個(gè)字符位置 -第一個(gè)字符位置 +1 。其中“+1”是補(bǔ)上最后一 個(gè)數(shù)字位置減去第一個(gè)數(shù)字位置而減少的一個(gè)數(shù)字位。三、解決方案及步驟假定字符串輸入在 A2 單元格。確定 A2 中字符

4、串的長度。即用LEN函數(shù)計(jì)算出A2中字符串中字符的個(gè)數(shù)，這個(gè)字符個(gè)數(shù)值就是字符串中最后一個(gè)字 符在字符串中的位置： =LEN( A2)。確認(rèn)字符串中的每一個(gè)字符位置序列數(shù)組成的新數(shù)組。用INDIRECT函數(shù)返回一個(gè)由文本字符串指定的引用:=INDIRECT("1:"&LEN($A2)用返回行數(shù)的函數(shù)ROW確定文本引用INDIRECT("1:"&LEN($A2)構(gòu)成的新數(shù)組：=ROW(INDIRECT("1:"&LEN($A2) )用按指定位置開始返回指定個(gè)數(shù)字符的函數(shù) MID返回由新數(shù)組=ROW (INDIRE

5、CT("1:"&LEN($A2) )確定位置的每一個(gè)字符，并將文本轉(zhuǎn)化成數(shù)值型數(shù)據(jù)：=-MID($A2,ROW(INDIRECT("1:"&LEN($A2) ，1)函數(shù)MID返回的字符是文本，將文本轉(zhuǎn)化為數(shù)值型數(shù)據(jù)，可以用函數(shù)VALUE也可以同等功能地用符號(hào)“ - - ”或“ +0”或“ -0”簡化表達(dá)，這里用“ - - ”表示。函數(shù)ISNUMBER別MID函數(shù)提取出來的字符是不是數(shù)字，是數(shù)字返回TRUE不是數(shù)字返回 FALSE。具體公式是：=ISNUMBER(-MID($A2,ROW(INDIRECT("1:"&am

6、p;LEN($A2),1)邏輯函數(shù)IF根據(jù)用函數(shù)ISNUMBE檢測(cè)MID函數(shù)提取出來的字符是否數(shù)值的真假，返回?cái)?shù)字字符在字符串中的位置，如果不是數(shù)字則返回空白字符。具體公式是：=IF(ISNUMBER(-MID($A2,ROW(INDIRECT("1:"&LEN($A2),1),ROW(INDIRECT("1:"&LEN( $A2),"")用MIN函數(shù)返回?cái)?shù)字位置數(shù)組成數(shù)組中的最小數(shù)。具體公式是：=MIN(IF(ISNUMBER(-MID($A2,ROW(INDIRECT("1:"&LEN(

7、$A2),1),ROW(INDIRECT("1:"&LEN($A2),"") ) 用MAX函數(shù)返回?cái)?shù)字位置數(shù)組中的最大數(shù)。具體公式是：=MAX( IF(ISNUMBER(-MID($A2,ROW(INDIRECT("1:"&LEN($A2),1),ROW(INDIRECT("1:"&LEN($A2),"")確認(rèn)字符串中第一個(gè)數(shù)字的起始位置：=MIN( IF(ISNUMBER(-MID($A2,ROW(INDIRECT("1:"&LEN($A2)

8、,1),ROW(INDIRECT("1:"&LEN( $A2),"")確認(rèn)字符串中第一個(gè)數(shù)字與最后一個(gè)數(shù)字之間的字符個(gè)數(shù)：=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("

9、1:"&LEN( $A2),"") )+1公式中的“ +1”， 是對(duì)字符串中最后一個(gè)數(shù)字位數(shù)減去第一個(gè)數(shù)字位數(shù)，造成第一個(gè)數(shù)字與最后一個(gè)數(shù)字之間的字符個(gè)數(shù)少 1 的補(bǔ)充。用函數(shù)MID在A1中按指定位置開始提取指定個(gè)數(shù)的字符（數(shù)字）。綜上所述，第步的公式為 MID函數(shù)的第2個(gè)參數(shù)，第步的公式為 MID函數(shù)的第3個(gè)參數(shù)。 組合后提取 A1 中數(shù)字的具體公式如下。在 B2 單元格編輯公式：=MID($A2,MIN(IF(ISNUMBER(-MID($A2,ROW(INDIRECT("1:"&LEN($A2),1),ROW(INDIRE

10、CT("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)用三鍵確

11、認(rèn)公式輸入，即用組合鍵 Ctrl+Shift+Enter 進(jìn)行公式確認(rèn)。本公式不適用的文本字符串類型：形如WE1234GH098PII等。四、適當(dāng)簡化公式基于文本數(shù)字轉(zhuǎn)化為數(shù)值型數(shù)字表達(dá)方式一一用函數(shù)VALUE符號(hào)“-”和“+0”或“ -0”效果完全一致，所以具體的提取文本中數(shù)字的公式可以適當(dāng)簡化為：=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)五、編后語特別指出，對(duì)于提取文本中的數(shù)字，本公式不是最簡方法，同時(shí)也不一