送人玫瑰,，手有余香,，請將文章分享給更多朋友

動手操作是熟練掌握EXCEL的最快捷途徑！

【置頂公眾號】或者【設(shè)為星標(biāo)】及時接收更新不迷路

小伙伴們好,，今天要和大家分享一則老生常談的問題,。在同一個單元格內(nèi)，有文本,，有數(shù)字，現(xiàn)在需要對數(shù)字進(jìn)行求和,。

在以前的帖子中我專門向大家介紹并分析了兩種不同的方法,，以及它們各自的優(yōu)缺點及應(yīng)用場合。朋友們還記得嗎,？不記得了也沒有關(guān)系,，在今天的帖子中我們還會提及到的！

今天的這個帖子是一個匯總,，當(dāng)然,，我也不可能匯總所有的可能性。我會盡可能給大家分享更多的公式及他們的思路,，方便大家借鑒和使用,。

好了，話不多說,，上菜,！

我們用SUBSTITUTE函數(shù)進(jìn)行字符替換。

在單元格B2中輸入公式“=SUMPRODUCT(--TEXT(MID(TEXT(MID(SUBSTITUTE((SUBSTITUTE(A2,",，","次")&"次"),"次",REPT(" ",9)),ROW($1:$50),9),),2,8),"0.000;-0.000;0;!0"))”,，三鍵回車并向下拖曳即可。

思路：

• 用SUBSTITUTE函數(shù)進(jìn)行字符替換,，最終得的結(jié)果是"銷售部20次采購部25次人事部10次"

• 利用SUBSTITUTE經(jīng)典應(yīng)用公式,，得到這樣一個結(jié)果{"銷售部20    ";"售部20     ";"部20      ";"20       ";"0        ";"         ";"        采";"       采購";"      采購部";"     采購部2";"    采購部25";"   采購部25 ";"  采購部25  ";" 采購部25   ";"采購部25    ";"購部25     ";"部25      ";"25       ";"5        ";"         ";"        人";"       人事";"      人事部";"     人事部1";"    人事部10";"   人事部10 ";"  人事部10  ";" 人事部10   ";"人事部10    ";"事部10     ";"部10      ";"10       ";"0        ";"         ";"        ";"       ";"      ";"     ";"    ";"   ";"  ";" ";"";"";"";"";"";"";"";""}

• 接下來，TEXT函數(shù)將所有數(shù)值部分強制轉(zhuǎn)換為空值{"銷售部20    ";"售部20     ";"部20      ";"";"";"         ";"        采";"       采購";"      采購部";"     采購部2";"    采購部25";"   采購部25 ";"  采購部25  ";" 采購部25   ";"采購部25    ";"購部25     ";"部25      ";"";"";"         ";"        人";"       人事";"      人事部";"     人事部1";"    人事部10";"   人事部10 ";"  人事部10  ";" 人事部10   ";"人事部10    ";"事部10     ";"部10      ";"";"";"         ";"        ";"       ";"      ";"     ";"    ";"   ";"  ";" ";"";"";"";"";"";"";"";""}

• MID函數(shù)從每個字符串的第二位開始,，提取長度為8的字符串,。這樣做的原因是，經(jīng)過上面一系列的步驟后，一定存在于“一個文本字符加上一串?dāng)?shù)字字符再加上一定數(shù)量的空格”這樣的字符串,。這步提取的結(jié)果就是“數(shù)字+空格”

• 接下來再次利用TEXT函數(shù)將文本,、負(fù)數(shù)等強制轉(zhuǎn)換為0

• 最后用SUMPRODUCT函數(shù)求和

這個公式同樣也是使用SUBSTITUTE函數(shù)，但有所不同的是,，在確定數(shù)字部分的位置時,，它的思路去令人拍案叫絕！

在單元格B2中輸入公式“=SUMPRODUCT(IFERROR(--MIDB(TRIM(MID(SUBSTITUTE(A2,",，",REPT(" ",99)),ROW($1:$3)*100-99,99)),SEARCHB("?",TRIM(MID(SUBSTITUTE(A2,",，",REPT(" ",99)),ROW($1:$3)*100-99,99))),10),0))”，三鍵回車并向下拖曳即可,。

思路：

• TIRM(MID(SUBSTITUTE()))這這一串,，都是利用了經(jīng)典應(yīng)用將源文件中的三部分分別提取出來，結(jié)果為{"銷售部20";"采購部25";"人事部10"}

• SEARCHB("?",TRIM(MID(SUBSTITUTE(A2,",，",REPT(" ",99)),ROW($1:$3)*100-99,99)))部分,，則是確定了第一個單字節(jié)字符的位置，結(jié)果為{7;7;7}

• 下面則利用MIDB函數(shù)依次從第7位開始,，提取長度為10 的字符串,，結(jié)果為{"20";"25";"10"}

• 接下來減負(fù)運算將文本型數(shù)字轉(zhuǎn)換為數(shù)值型數(shù)字，IFFERROR函數(shù)屏蔽錯誤后,，SUMPRODUCT求和

這里使用SEARCHB函數(shù)的用法,，確定了第一個數(shù)字的位置。請朋友們記住這個用法,。

總體上,，下面這個公式的思路和上面的幾例都大同小異。利用SUBSTITUTE函數(shù)進(jìn)行文本的替換,，利用MID函數(shù)提取文本,，TEXT函數(shù)取出數(shù)字部分后在進(jìn)行依次MID函數(shù)提取和TEXT函數(shù)的處理，最后SUMPRODUCT函數(shù)求和,。

只不過,，這則公式看起來更加簡短。

這一個和上面有異曲同工的妙處,！

這個公式里使用了“*1”來將本型數(shù)字轉(zhuǎn)換為數(shù)字型數(shù)字,，和上個例子中的減負(fù)運算道理是一樣的。

這一個是筆者喜歡的公式之一,。

在單元格B2中輸入公式“=SUM(TEXT(LEFT(TEXT(MID(A2&"a",COLUMN(2:2),ROW($2:$16)),),ROW($1:$15)),"0%;;0;!0")*ISERR(-MID(A2,COLUMN(2:2)-1,2)))”,，三鍵回車并向下拖曳即可。

這個公式相對有一些復(fù)雜,，朋友們記住并學(xué)會套用就可以了,。以上的幾個例子中,，都是用了TEXT函數(shù)來進(jìn)行數(shù)據(jù)的強制轉(zhuǎn)換。這個方法大家也要記住并學(xué)會靈活使用,。

這一則也是我非常喜歡用的公式之一,。

在單元格B2中輸入公式“=SUM(IFERROR(--RIGHT(LEFT(A2,ROW($1:$50)-1),FREQUENCY(ROW(A:A),ISERROR(-MID(A2,ROW($1:$50),1))*ROW($1:$50))-1),0))”，三鍵回車并向下拖曳即可,。

關(guān)于最后兩則公式的詳細(xì)介紹,，請參看帖子華山論劍：數(shù)字提取技術(shù)誰更強，應(yīng)用之家?guī)湍忝?/a>

寫了這么多,，目的還是希望大家在遇到問題時能根據(jù)實際情況選擇最適合的那一款公式解決問題,！

我就知道你“在看”