常有網友提到的 Excel 中的問題:取出儲存格中字串中包含的數字,該如何處理?

如下圖,一個儲存格中的內容,數字可能出現的位置:左邊、中間、右邊,如何取出其中的數字。而該公式也適用空字串、全無數字、全是數字的儲存格。

Excel-取出字串中的數字(ROW,COLUMN,陣列公式)


【公式設計與解析】

儲存格B1:{=MAX(IFERROR(MID(A2,ROW($1:$10),COLUMN($A:$J))*1,0))}

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

複製儲存格B1,貼至儲存格B1:B9。

(1) ROW($1:$10)

在陣列公式中產生 1 ~ 10的數字。ROW(1:1)=1、ROW(2:2)=2、…、ROW(10:10)=10。

(2) COLUMN($A:$J)

在陣列公式中產生 1 ~ 10的數字。COLUMN(A:A)=1、COLUMN(B:B)=2、…、COLUMN(J:J)=10。

(3) MID(A2,ROW($1:$10),COLUMN($A:$J))

在陣列公式中,利用 MID 函數取出所有可能的字串。例如:『AK6』 會取出 A、K、6、AK、K6、AK6。(MID 函數的作用乃是由第 n 個字取出 m 個字)

(4) MID(A2,ROW($1:$10),COLUMN($A:$J))*1

公式中「*1」的用意,是將取出的數字字串轉換為數值,但是非數字的字串會傳回錯誤訊息。

(5) IFERROR(MID(A2,ROW($1:$10),COLUMN($A:$J))*1,0)

利用 IFERROR 函數將第(4)式的傳回值的錯誤訊息以『0』取代。

(6) MAX(IFERROR(MID(A2,ROW($1:$10),COLUMN($A:$J))*1,0))

利用 MAX 函數,以取出第(5)式傳回的所有數值的最大值。該最大值即為字串中的數字。

文章標籤
創作者介紹

學不完.教不停.用不盡

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