概要
文字列の中で「指定した文字がK番目に出てくる位置」を取得したいときに便利なのが、FIND関数＋SUBSTITUTE関数の組み合わせです。通常のFIND関数は最初に見つかった位置しか返せませんが、SUBSTITUTEで「K番目の出現文字だけを別の文字に置き換える」ことで、その位置をFINDで特定できます。
基本の使い方
書式
=FIND("検索文字",SUBSTITUTE(対象文字列,"検索文字",置換文字,K))
- SUBSTITUTEで「検索文字」をK番目だけ置換文字に置き換える
- FINDでその置換文字の位置を検索する
- 結果として「検索文字のK番目の出現位置」が返る
例
=FIND("a",SUBSTITUTE("banana","a","@",2))
// "banana"の2番目の"a"の位置 → 4
具体例
「banana」の2番目の「a」の位置
=FIND("a",SUBSTITUTE("banana","a","@",2))
結果は「4」。つまり2番目の「a」は4文字目にあります。
「apple-orange-pear」の2番目の「-」の位置
=FIND("-",SUBSTITUTE("apple-orange-pear","-","@",2))
結果は「11」。2番目の「-」は11文字目にあります。
セル参照で使う
=FIND("-",SUBSTITUTE(A2,"-","@",3))
A2セルの文字列に含まれる「-」の3番目の位置を返します。
応用テンプレート
K番目の出現位置を汎用化
=FIND("検索文字",SUBSTITUTE(A1,"検索文字","@",K))
Kをセル参照にすれば柔軟に指定可能です。
出現位置が存在しない場合のエラー対策
=IFERROR(FIND("検索文字",SUBSTITUTE(A1,"検索文字","@",K)),"なし")
K番目が存在しない場合は「なし」と表示。
出現位置を使って文字列を抽出
=LEFT(A1,FIND("-",SUBSTITUTE(A1,"-","@",2))-1)
2番目の「-」以前の文字列を抽出できます。
よくあるつまずきと対策
SUBSTITUTEの第4引数が重要
第4引数で「何番目を置換するか」を指定します。これを忘れるとすべて置換されてしまいます。
FINDは大文字小文字を区別
「A」と「a」は別文字として扱われます。区別したくない場合はUPPERやLOWERで統一しましょう。
存在しない場合はエラー
K番目が存在しないと#VALUE!エラーになります。IFERRORで補うと安心です。
例題
問題1: A2セルの文字列「banana」から2番目の「a」の位置をB2に表示してください。
解答例:
=FIND("a",SUBSTITUTE(A2,"a","@",2))
問題2: C2セルの文字列「apple-orange-pear」から2番目の「-」の位置をD2に表示してください。
解答例:
=FIND("-",SUBSTITUTE(C2,"-","@",2))
問題3: E2セルの文字列に含まれる「x」の3番目の位置をF2に表示してください。
解答例:
=FIND("x",SUBSTITUTE(E2,"x","@",3))
問題4: G2セルの文字列で「-」のK番目の位置をH2に表示し、KはI2セルに入力された値を参照してください。
解答例:
=FIND("-",SUBSTITUTE(G2,"-","@",I2))
問題5: J2セルの文字列で「a」の5番目の位置をK2に表示し、存在しない場合は「なし」と表示してください。
解答例:
=IFERROR(FIND("a",SUBSTITUTE(J2,"a","@",5)),"なし")
まとめ
FIND単体では最初の出現位置しか返せませんが、SUBSTITUTEと組み合わせることで「K番目の出現位置」を取得できます。ポイントはSUBSTITUTEの第4引数で「何番目を置換するか」を指定すること。これを理解すれば、文字列処理の幅が大きく広がり、複雑なデータ整形もスマートにこなせるようになります。