(Excel)FIND関数で右から検索する方法

 ExcelのFIND関数を別の関数と組み合わせて「含まれる最も右の文字が何文字目にあるか」を取得してみます。
 また、「右から数えて何文字目か」を取得する例も示します。

  • FIND関数とLEN/SUBSTITUTE関数を組み合わせて「最も右にある文字の位置」や「右から数えた位置」を取得することができます。
  • TEXTBEFORE関数やTEXTAFTER関数を使えば同じ結果をより簡単に得ることができます。

最も右にある文字の位置を取得する方法

FIND関数とLEN/SUBSTITUTE関数を併用する方法

 次の画像ではC列にFIND関数を使った式を入力し、B列の文字列中の「-」の位置を取得しています。
 ただし取得しているのは「-」のうち最も右にあるものの位置(左から何文字目か)です
 文字列中に「-」が含まれない場合に#VALUE!エラーとなるのはFIND関数を普通に使った場合と同じです。

 C3セル(下方にコピー)

=FIND("★",SUBSTITUTE(B3,"-","★",LEN(B3)-LEN(SUBSTITUTE(B3,"-",""))))

 長いですが、基本的な関数で構成されているので古いバージョンのExcelでも使えるのがメリットです。
 考え方は次のようになります。

  1. SUBSTITUTE関数で「-」を削除してから元の文字列と長さを比べることにより、文字列中の「-」の数(説明上、この数をnとします)をカウントする。
  2. 文字列中の「-」のうちn番目(つまり最後のもの)だけを「★」に置き換える。
  3. FIND関数で「★」の位置を求める。

 元の文字列に「-」からの置き換え先文字(ここでは「★」)が含まれていると誤った結果になりますので、置き換え先文字は適宜選択する必要があります。


 なお、FIND関数の第2引数部分だけを実行すると次のようになります。
 「LEN(B3)-LEN(SUBSTITUTE(B3,"-",""))」の部分で「-」が何個あるかカウント(=n)し、それをSUBSTITUTE関数の第3引数とすることでn番目の「-」だけを「★」に置き換えています。
 あとはFIND関数を足せば、最も右にあった「-」の位置を取得できることがわかります。

 C3セル(下方にコピー)

=SUBSTITUTE(B3,"-","★",LEN(B3)-LEN(SUBSTITUTE(B3,"-","")))


TEXTBEFORE関数を使う代替案

 次の画像ではTEXTBEFORE関数を使って上記と同じ結果を得ています(ただし「-」を含まない1文字以上の文字列に対しては#N/A!エラーになるのが僅かな相違点です)。

 C3セル(下方にコピー)

=LEN(TEXTBEFORE(B3,"-",-1))+1

 「TEXTBEFORE(B3,"-",-1)」の部分により文字列中の最後の「-」の1文字前までを取得できます(第3引数「-1」は「最後のもの」の意味です)。この文字数をLEN関数で数えて最後に1を足せば求める値になる、というわけです。


 なお、TEXTBEFORE関数の部分だけを実行すると次のようになります。
 指定した文字(最後の「-」)までは取得しないので、長さを数えた後に1を足す必要があります。
 そもそもTEXTBEFORE関数やTEXTSPLIT関数が使えるバージョンならFINDを使って位置を求める場面がなさそうな気もしますが、参考まで。

「右から数えて何文字目か」を取得する方法

FIND関数とLEN/SUBSTITUTE関数を併用する方法

 ここでもC列にFIND関数を使った式を入力し、B列の文字列中の「-」の位置を取得しています。
 ただし取得しているのは「-」のうち最も右にあるものが、右から何文字目の位置にあるかです

 C3セル(下方にコピー)

=LEN(B3)-FIND("★",SUBSTITUTE(B3,"-","★",LEN(B3)-LEN(SUBSTITUTE(B3,"-",""))))+1

 FIND関数の部分は最初の例(左から何番目か数える)と同じです。これを文字列全体の長さ(LEN(B3))から引いて最後に1を足しています。
 これで「右から何文字目か」が求められます。

TEXTAFTER関数を使う代替案

 次の画像ではTEXTAFTER関数を使って上記と同じ結果を得ています(ただし「-」を含まない1文字以上の文字列に対しては#N/A!エラーになります)。

 C3セル(下方にコピー)

=LEN(TEXTAFTER(B3,"-",-1))+1

 「TEXTAFTER(B3,"-",-1)」の部分により文字列中の最後の「-」の次の文字以降を取得できます(第3引数「-1」は「最後のもの」の意味)。この文字数をLEN関数で数えて最後に1を足せば求める値になる、というわけです。


 なお、TEXTAFTER関数の部分だけを実行した結果は次のとおりとなります。