EXCEL中如何從混合文本中提取出數字

在利用EXCEL時，有時從外部導入的數據，或者輸入時不規范，將數字與文本夾雜輸入在一個單位格中，給后續的統計運算帶來未便，為此需要將文字與數字分隔，本篇介紹幾種將數字提掏出來的方式。

東西/原料

電腦
EXCEL軟件

方式/步調

1
起首要闡發數據內容，按照分歧的類型，利用分歧的方式。
2
闡發這個表格，可以發現，除了數字外，就是漢字，在雙字節字符中，每個漢字算兩個字節，操縱此特點，可以算出有幾多個漢字、幾多個數字，單字節字符數用LEN函數：=LEN(A2)。
3
雙字節字節數用LENB函數，注重下兩者的區分，單字節指字符個數，雙字節指字節個數：=LENB(A2)。
4
二者相減，即可獲得文字的個數：=LENB(A2)-LEN(A2)。
5
再用總字符數減去文字個數，即獲得要提掏出來的數字個數：=LEN(A2)-(LENB(A2)-LEN(A2))，去失落括號，歸并起來，公式為：=LEN(A2)*2-LENB(A2)。
6
知道了數字個數，再知道數字起頭的位置，就可以用MID函數將數字提掏出來了，可是此刻不知道數字是從什么位置起頭的，可以利用SEARCHB雙字節查找函數+通配符"?"來查詢單字節呈現的位置：=SEARCHB("?",A2)。
7
如許提取公式就有了，提取函數也要用雙字節函數：=MIDB(A2,SEARCHB("?",A2),LEN(A2)*2-LENB(A2))，若是用單字節函數也可以，但公式稍長些：=MID(A2,INT(SEARCHB("?",A2)/2)+1,LEN(A2)*2-LENB(A2))。
8
提取成果是文本格局，若是要轉為常規的數字格局，可以在公式前加上--，或者*1，來轉換：=--MIDB(A2,SEARCHB("?",A2),LEN(A2)*2-LENB(A2))，轉換格局后，可以進行下一步的統計運算。
9
若是文字中有空格，直接提取就會出去，需要對本來的內容進行去空格替代后，再提取：=--MIDB(SUBSTITUTE(A2," ",""),SEARCHB("?",SUBSTITUTE(A2," ","")),LEN(SUBSTITUTE(A2," ",""))*2-LENB(SUBSTITUTE(A2," ","")))。
10
可是上面的方式，只能用于除了數字就是文字的內容提取，若是除此之外，還有其它的字母、符號等單字節字符，就會犯錯。
11
對于這種不心猿意馬型的，可以用數組公式進行提取，從每個字符起頭，掏出1~若干個字符（好比10個），再加上運算符號進行判定，掏出的是不是數值，再用MAX函數從中掏出是數值的最大都字：=MAX(IFERROR(--MID(A2,ROW($1:$30),COLUMN($A:$J)),))，在輸入數組公式時，需要同時按住Ctrl+Shift+Enter竣事公式編纂，公式會主動生當作一對“｛｝”。
12
當然，也可以不消上面的數組公式，操縱LOOKUP函數的標的目的后兼容性，來提掏出數字：=-LOOKUP(1,-MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),ROW($1:$10)))，此公式先判定數字呈現的最小位置，再從此位置起頭依次掏出若干個字符，最后用LOOKUP函數掏出最長數字串的阿誰數字，即達目標，關于此函數的用法，可以參看“EXCEL中LOOKUP函數的高級應用”。
4中函數的高級應用
13
固然操縱公式，可以從各類分歧的夾雜文本中找出紀律提掏出數字，可是仍是應該養當作杰出的習慣，在建造表格時，就將其分在分歧的列中，有助于后續統計。