Excelで文字列の後ろを取り出す

例えば

C:\foo\bar\baz\hogehoge.txt

という文字列から"hogehoge.txt"を取り出したいとき、Javaなら変数sに入っているとすれば

s.substring(s.lastIndexOf('\\')+1);

でOK。これと同じようにExcelのあるセルに入っている文字列の最後の\より後ろを取り出そうとして予想外に手間取る。そもそもExcelのワークシート関数にはlastIndexOf()に相当するものがない。VBAならInStrRev()/InStrRevB()で代替できる。しかしこのためにマクロを書くのは嫌だ。なのでワークシート関数で作成を試みる。その結果できたのがこれ(セルA1が処理対象とする)。

=RIGHT(A1,LEN(A1)-FIND("/",SUBSTITUTE(A1,"\","/",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

作った当人もあとで理解不能になりそうなので仕組みをメモしておく。

まず最初に一番内側から。

SUBSTITUTE(A1,"\","")

SUBSTITUTEは文字列中の指定された文字をほかの文字に置き換える。

SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象])

文字列

対象文字列(セル指定OK)

検索文字列

置き換えられる文字列

置換文字列

置換後文字列

置換対象(省略可)

対象文字列中、何番目の検索文字列を置き換えるか？の指定。省略時はすべての検索文字列が置換される。

上記の場合、第4引数がないので、セルA1に含まれる"\"がすべて""へ置換されるので次のようになる。

1	2	3	4	5	6	7	8	9	10	1	2	3	4	5	6	7	8	9	20	1	2	3	4	5	6	7
C	:	\	f	o	o	\	b	a	r	\	b	a	z	\	h	o	g	e	h	o	g	e	.	t	x	t

↓

1	2	3	4	5	6	7	8	9	10	1	2	3	4	5	6	7	8	9	20	1	2	3
C	:	f	o	o	b	a	r	b	a	z	h	o	g	e	h	o	g	e	.	t	x	t

これのLEN()を求めると23。よって

=RIGHT(A1,LEN(A1)-FIND("/",SUBSTITUTE(A1,"\","/",LEN(A1)-23)))

となる。ポイントは次の

LEN(A1)-23

の部分。答えは4(=27-23)。これはセルA1に含まれる"\"の個数を表す。これが次のSUBSTITUTEの第4引数になる。

SUBSTITUTE(A1,"\","/",4)

これでセルA1の4番目の"\"が"/"へ置換される。

1	2	3	4	5	6	7	8	9	10	1	2	3	4	5	6	7	8	9	20	1	2	3	4	5	6	7
C	:	\	f	o	o	\	b	a	r	\	b	a	z	/	h	o	g	e	h	o	g	e	.	t	x	t

ここでは"/"へ置換しているが、他の文字とかぶらなければなんでもいい。そしてこの"/"の位置をFINDで求める。答えは15なので、結果

=RIGHT(A1,LEN(A1)-15))

となり

=RIGHT(A1,12)

これで"hogehoge.txt"が取り出せた。終わり。