[エクセル上級] n番目の文字列を検索

      2018/04/07

1

文字列から、特定の文字列を検索し、n番目に出現する位置を返す関数です。

SPONSORED LINK

文字列から文字列を検索

文字列から特定の文字列の位置を調べる場合、Excelでは FIND関数 で最初の文字列の位置を調べることはできますが、2番目,3番目の文字列の位置を調べる方法がありません。

FIND関数 の場合

FIND関数 の引数は、

  • [検索文字列](探したい文字列)
  • [対象](どの文字列から探すか)
  • [開始位置]([対象]のどの位置から探すか)

3

開始位置を設定すれば良さそうに見えますが、例えば3番目の出現位置を調べる場合、2番目の出現位置が分からないと開始位置は設定できません。同様に、2番目の出現位置を調べるときは、最初の出現位置が分からないといけません。
「3番目」程度ならいいですが、「15番目」とかになると途方もありませんね。
なので、FIND関数 単独では最初の出現位置しか分かりません。

2

SUBSTITUTE関数

そこで、「SUBSTITUTE関数」を使います。

SUBSTITUTE関数 の目的は「文字列の置換」です。文字列から特定の文字列を検索し、その文字列を別の文字列に置換します。

SUBSTITUTE関数 の引数には[置換対象]というオプション引数があり、ここに数字を指定すると n 番目に出現する該当文字列を置換してくれます。

SUBSTITUTE

ここでは、仮にアスタリスク「*」に置換してみます。

4

3番目の「/」を「*」に置換した結果

3番目だけが「*」に置き換わりました。

6

SPONSORED LINK

文字列から「*」を検索

SUBSTITUTE関数 のおかげで、3番目の「/」だけが「*」になりましたので、あとは「*」を検索するだけです。

FIND関数 で「*」の位置を調べます。

7

SUBSTITUTE関数 を FIND関数 に埋め込む

検索を1セルで済ませるために、SUBSTITUTE関数 での置換結果を FIND関数 の検索対象文字列に指定します。

8

9

置換する文字列の変更

ここまでで n番目の「/」は検索できましたが、元の文字列に「*」が含まれていると正しく検索できません。

そこで置換する文字列を変更します。できるだけ被らない文字がいいので、ここでは制御文字という特殊な文字を指定することにします。

CHAR関数 では文字コードASCIIの文字を数値指定で出力できます。今回は使われることの無さそうな「伝送制御拡張」( =CHAR(16) )を使うことにしました。

10

これでどんな文字列がきても大丈夫ですね!

オマケ:関数のデバッグをしやすくするには

先ほど使った制御文字は基本的に印字されない文字なので、うまく検索できなくなったときの原因探しが難しくなります。

ですので、例えば「途中に改行が入ることはないから、改行文字( =CHAR(10) )を使う」や「改行が入ることはあっても、連続することはないから、改行文字2連続( =CHAR(10)&CHAR(10) )を使う」などと工夫することで、問題があったときの調査がしやすくなると思います。

itjo レスポンシブ 本文下

 - Excel
 - , , , , ,