FIND関数で後ろ・右から文字を探して位置を取得する

「excel関数 find 後ろから」
「エクセル find 右から」
といった検索で、このサイト・インストラクターのネタ帳へのアクセスがあります。

FIND関数を使って、文字列の後ろ（右）から、ある文字を探して、何文字目にあるのか調べる方法を、探していた方による検索でしょう。

例えば、
　　a-b-c-d
　　aa-bb-cc
　　aaa-bbb
のように、途中の出現回数や文字数が決まっていないときに、最後の「-」（ハイフン）が何文字目にあるかを取得するために、どのような数式にすればいいのかを探していたのだろうと推測しています。

FIND関数で後ろ・右から文字を探して位置を取得する数式

最初に、結論の数式をご紹介します。

A2セルに入力されている文字列に「●」が含まれていないときに、上記の数式で、A2セルに入力されている文字列の最後の「-」が、何文字目かを取得できます。

FIND関数で後ろ・右から文字を探して位置を取得する考え方

大切なのは、この数式が何をやっているかを理解することです。

上記の
「=FIND("●", SUBSTITUTE(A2, "-", "●", LEN(A2) - LEN(SUBSTITUTE(A2, "-", ""))))」
という数式は、以下の考え方で作られています。

FIND関数で後ろ・右から文字を探して位置を取得する数式を実際に確認しましょう

実際にExcel上で数式を入力してみましょう。

A2セルに元の文字列が入力されているときに、B2セルとC2セルに途中の状態を計算して、D2セルに最後の「-」の位置を計算してみましょう。

まず、B2セルに「-」が、全部でいくつ存在するかを調べる数式
「=LEN(A2) - LEN(SUBSTITUTE(A2, "-", ""))」
を入力します。

SUBSTITUTE関数で、「-」を削除した文字数「LEN(SUBSTITUTE(A2, "-", "")」を、元の文字数「LEN(A2)」から引き算すれば、A2セルに「-」が何文字存在しているかを調べられます。これが
「=LEN(A2) - LEN(SUBSTITUTE(A2, "-", ""))」
の意味です。

つづいて、上記の数式でB2セルに計算されている「-」の数を使って、A2セルの文字列の最後の「-」を「●」に置換する数式
「=SUBSTITUTE(A2, "-", "●", B2)」
をC2セルに入力します。

SUBSTITUTE関数の第4引数を指定すると、いくつ目に登場する文字を置換するのか、指定できます。

上記の数式では第4引数にB2セルが指定されているので、最後に登場する「-」だけが「●」に置換されます。

最後に「●」の位置をFIND関数で調べる数式
「=FIND("●", C2)」
をD2セルに入力します。

これでD2セルに、A2セルに入力されている文字列のうち、最後の「-」が何文字目なのかを取得できます。

FIND関数で後ろ・右から文字を探して位置を取得する数式を一つにまとめる

途中の計算を入れておく作業用セルを残しておけるのなら、上記のままでもOKですが、上記の数式を一つにまとめるなら以下の手順です。

最後に入力した「●」の位置を調べている数式
「=FIND("●", C2)」
で、C2となっている部分に、C2セルに入力した数式
「SUBSTITUTE(A2, "-", "●", B2)」
を入れて、
「=FIND("●", SUBSTITUTE(A2, "-", "●", B2))」
とします。

つづいて、この
「=FIND("●", SUBSTITUTE(A2, "-", "●", B2))」
で、B2となっている部分に、B2セルに入力した数式
「LEN(A2) - LEN(SUBSTITUTE(A2, "-", ""))」
を入れて、
「=FIND("●", SUBSTITUTE(A2, "-", "●", LEN(A2) - LEN(SUBSTITUTE(A2, "-", ""))))」
とします。

これが、この記事の最初にご紹介した数式です。

▼このサイト「インストラクターのネタ帳」を運営する伊藤潔人の、初めての著書『いちばんやさしいExcel VBAの教本』の Chapter 8 は、VBAの経験があるみなさんにも、興味深く読んでいただけると考えています。