根據前一篇文章:

Excel-查詢指定字串所在的儲存格位址(MATCH,ADDRESS,FIND,OFFSET)

延伸的公式設計(使用FIND,SEARCH,SUSTITUTE),其公式執行概念大同小異。(參考下圖)

Excel-查詢指定字串所在的儲存格位置(FIND,SEARCH,SUSTITUTE)

 

【公式設計與解析】

選取儲存格B1:B21,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:字串。

 

《FIND函數》

儲存格C2:{=IFERROR(SMALL(IFERROR((FIND($F$2,字串)>0)*ROW(字串),""),
ROW(1:1))-1,"")}

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

複製儲存格C2,貼至儲存格C2:C21。

 

《SEARCH函數》

儲存格D2:{=IFERROR(SMALL(IFERROR((SEARCH($F$2,字串)>0)*ROW(字串),""),
ROW(1:1))-1,"")}

使用 SEARCH 函數的關念和 FIND 函數完全相同。

 

《SUBSTITUTE函數》

儲存格E2:{=IFERROR(SMALL(IF(SUBSTITUTE(字串,$F$2,"")<>字串,
ROW(字串),""),ROW(1:1))-1,"")}

使用 SUBSTITUTE 函數並不是以尋找的關念來操作,而是將儲存格中的內容置換掉所指定字元,若結果不相同者,則表示該字串有包含指定字元。

 

相同公式,適用於不管指定幾個字元,但不適用於空白。若是沒有符合的字串,則會顯示空白。

Excel-查詢指定字串所在的儲存格位置(FIND,SEARCH,SUSTITUTE)

arrow
arrow
    文章標籤
    Excel FIND SEARCH SUSTITUTE
    全站熱搜

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