(Excel)FIND関数で複数の位置・n番目の位置・最後の位置を取得する

 ExcelのFIND関数は、検索文字(列)が出現する最初の位置を返します。
 これを応用して検索文字(列)が複数回出現する場合にすべての位置を取得する方法を示します。
 また、n番目に出現する位置や、最後に出現する位置も取得できますので併せて示します。

  • FIND関数の第3引数(開始位置)にSEQUENCE関数を使用することで、検索文字(列)が複数ある場合でもすべての位置を取得できます。

手順

複数の出現位置をすべて取得する

 次の画像では、B3セルに記録された文字列に含まれているハイフンの位置をすべて取得しています。

f:id:accs2014:20200523152523p:plain:w750

 この結果は、C3セルに次のように記すことで実現しています。

=TRANSPOSE(UNIQUE(IFERROR(FIND("-",B3,SEQUENCE(LEN(B3))),"")))

 FIND関数を使ってB3セル文字列からハイフンの位置を取得しているのですが、第3引数を「SEQUENCE(LEN(B3))」とすることにより「1文字目から検索を始めた結果」「2文字目から検索を始めた結果」…「最後の文字から検索を始めた結果」を一括して取得しています。結果的にすべての位置を取得できるのですが、同じ位置が複数回現れますのでUNIQUE関数で重複を除いて出力しています。TRANSPOSE関数を除けば結果は縦に並びます。
 なお、画像ではG列(5番目の出現位置を表示するセル)まで罫線をひいていますが、ハイフンが6個以上出現してもH列以降にきちんと出力されます。
 また、B3セルの最後が検索文字(ハイフン)でない場合は、数値の後に空文字が返ります。上記の画像ではG3セルに空文字が返っています。

 また、次の画像では、TEXTJOIN関数を使ってすべての出現位置を結合して1つの文字列にしています。

f:id:accs2014:20200523152528p:plain:w750

 C3セルには次のように入力しています。

=TEXTJOIN(",",TRUE,UNIQUE(IFERROR(FIND("-",B3,SEQUENCE(LEN(B3))),"")))

n番目の出現位置を取得する

 次の画像では、INDEX関数を用いることでハイフンの出現位置のうち3番目のものの位置を取得しています。

f:id:accs2014:20200523152534p:plain:w750

 C3セルには次のように入力しています。

=INDEX(UNIQUE(IFERROR(FIND("-",B3,SEQUENCE(LEN(B3))),"")),3)

 基本的な考え方は最初の例と同じです。
 最後の「3」をnに変更すればn番目の出現位置を取得できます。


最後の出現位置を取得する

 次の画像では、MAX関数を用いることでハイフンの出現位置のうち最後のものの位置を取得しています。

f:id:accs2014:20200523155800p:plain:w750

 C3セルには次のように入力しています。

=MAX(IFERROR(FIND("-",B3,SEQUENCE(LEN(B3))),""))

 上記の例と異なりUNIQUE関数が不要ですので若干短くなります。