在 Excel 中的儲存格內容常會是數字和非數字混合,如何能找出某個儲存格中第一個非數字的位置呢?(參考下圖)

Excel-找出儲存格中第一個非數字的位置(ISNUMBER,MATCH,陣列公式)

 

【公式設計與解析】

先假設儲存格內容不會超過 20 個字元組成。

儲存格B2:{=MATCH(FALSE,ISNUMBER(VALUE(MID(A2,ROW($1:$20),1))),0)}

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

(1) MID(A2,ROW($1:$20),1))

在陣列公式中,利用 ROW(1:20) 來獲得 {1,2,3,4,5,..,20},再使用 MID 函數取出儲存格中的每一個字元。

(2) VALUE(MID(A2,ROW($1:$20),1))

將第(1)式傳回的每一個字元利用 VALUE 函數轉換為數字,但若是字元非數字,則會產生錯誤訊息。本例傳回:{1,2,3,#VALUE!,#VALUE!,#VALUE!, … ,#VALUE!}。

(3) ISNUMBER(VALUE(MID(A2,ROW($1:$20),1)))

利用 ISNUMBER 函數判斷第(2)式的結果是否為數字,本例傳回:{TRUE,TRUE,TRUE,FALSE,FALSE,FALSE, … ,FALSE}。

最後利用 MATCH 函數找出第一個 FALSE 的位置,即為所求,本例傳回「4」。

你也可以使用以下的公式得到相同結果:

儲存格B2:{=MATCH(TRUE,ISERROR(VALUE(MID(A2,ROW($1:$20),1))),0)}

 

而如果你的儲存格內容,其字元長度不確定是否會低於20個字,則改用以下公式。

儲存格B2:{=MATCH(TRUE,ISERROR(VALUE(MID(A2,ROW(INDIRECT("1:"&
LEN(A2))),1))),0)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

ROW(INDIRECT("1:"&LEN(A2))):利用 LEN(A2) 取得儲存格的字元數,再透過 INDIRECT 函數將「"1:"&LEN(A2)」的結果轉換為實際位址。

本例傳回:ROW(INDIRECT("1:6"))→ROW(1:6),指第1列至第6列。

arrow
arrow
    全站熱搜

    vincent 發表在 痞客邦 留言(1) 人氣()