2015年5月22日 星期五

Excel-取出儲存格中文數字字串中的數字(陣列公式)

網友問到:在 Excel 的資料表中有個文數字的集合,如何取出數字部分來加總呢?參考下圖,所有文數字混合均是文字在前、數字在後,或是沒有包含文字、只有數字。

 

【公式設計】

儲存格B2:{=MID(A2,MIN(IF(ISNUMBER(MID(A2,ROW($1:$20),1)*1),
ROW($1:$20),99)),99)*1}

這是陣列公式,輸入完成請按 Ctrl+Shift+Enter 鍵,Excel 自動產生「{}」。

以儲存格A3為例,其內容為:BB46。

MID(A2,ROW($1:$20),1):其中ROW($1:$21)在陣列公式中代表 1, 2, 3, ... , 20。

image

將儲存格B2中的文數字一個字一個字取出:

image

MID(A2,ROW($1:$20),1)*1:如果是空字串「""」,在執行「*1」時會產生錯誤訊息「#VALUE!」,

ISNUMBER(MID(A2,ROW($1:$20),1)*1):透過 ISNUMBER 函數來判斷每一個字元是否為數字,傳回 TRUE/FALSE 陣列。

image

IF(ISNUMBER(MID(A2,ROW($1:$20),1)*1),ROW($1:$20),99):上式中的每一個字如果是數字則傳回第幾個字(數字),如果是文字則傳回 99。(每個儲存格中數字字元數不可超過 99)

MIN(IF(ISNUMBER(MID(A2,ROW($1:$20),1)*1),ROW($1:$20),99)):透過 MIN 函數找出最小值,這個數值即為第 1 個數字出現的位置。

MID(A2,MIN(IF(ISNUMBER(MID(A2,ROW($1:$20),1)*1),ROW($1:$20),99)),
99)*1:透過 MID 函數取出數字部分,即為所求。

沒有留言:

張貼留言

好康東東