[エクセル上級] n番目の文字列を検索
2018/04/07
文字列から、特定の文字列を検索し、n番目に出現する位置を返す関数です。
目次
文字列から文字列を検索
文字列から特定の文字列の位置を調べる場合、Excelでは FIND関数 で最初の文字列の位置を調べることはできますが、2番目，3番目の文字列の位置を調べる方法がありません。
FIND関数 の場合
FIND関数 の引数は、
- ［検索文字列］（探したい文字列）
- ［対象］（どの文字列から探すか）
- ［開始位置］（[対象]のどの位置から探すか）
開始位置を設定すれば良さそうに見えますが、例えば3番目の出現位置を調べる場合、2番目の出現位置が分からないと開始位置は設定できません。同様に、2番目の出現位置を調べるときは、最初の出現位置が分からないといけません。
「3番目」程度ならいいですが、「15番目」とかになると途方もありませんね。
なので、FIND関数 単独では最初の出現位置しか分かりません。
SUBSTITUTE関数
そこで、「SUBSTITUTE関数」を使います。
SUBSTITUTE関数 の目的は「文字列の置換」です。文字列から特定の文字列を検索し、その文字列を別の文字列に置換します。
SUBSTITUTE関数 の引数には［置換対象］というオプション引数があり、ここに数字を指定すると n 番目に出現する該当文字列を置換してくれます。
ここでは、仮にアスタリスク「*」に置換してみます。
3番目の「/」を「*」に置換した結果
3番目だけが「*」に置き換わりました。
文字列から「*」を検索
SUBSTITUTE関数 のおかげで、3番目の「/」だけが「*」になりましたので、あとは「*」を検索するだけです。
FIND関数 で「*」の位置を調べます。
SUBSTITUTE関数 を FIND関数 に埋め込む
検索を1セルで済ませるために、SUBSTITUTE関数 での置換結果を FIND関数 の検索対象文字列に指定します。
置換する文字列の変更
ここまでで n番目の「/」は検索できましたが、元の文字列に「*」が含まれていると正しく検索できません。
そこで置換する文字列を変更します。できるだけ被らない文字がいいので、ここでは制御文字という特殊な文字を指定することにします。
CHAR関数 では文字コードASCIIの文字を数値指定で出力できます。今回は使われることの無さそうな「伝送制御拡張」（ =CHAR(16) ）を使うことにしました。
これでどんな文字列がきても大丈夫ですね！
オマケ：関数のデバッグをしやすくするには
先ほど使った制御文字は基本的に印字されない文字なので、うまく検索できなくなったときの原因探しが難しくなります。
ですので、例えば「途中に改行が入ることはないから、改行文字（ =CHAR(10) ）を使う」や「改行が入ることはあっても、連続することはないから、改行文字2連続（ =CHAR(10)&CHAR(10) ）を使う」などと工夫することで、問題があったときの調査がしやすくなると思います。