Excelで関数のみでフルパスからファイル名を抽出する

はじめに

先日、仕事でフルパスからファイル名だけを抽出する作業があったのですが、専用の関数ってないんですね。Split関数など使えると思っていたのですが、なかったのがちょっと意外でした。VBAなら使えるんですけどね。代わりの方法を調べましたので備忘録を兼ねて記録を残しておきます。

方法

=MID(A1,FIND("★",SUBSTITUTE(A1,"\","★",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1))

解説

上記の関数は大きく４つの処理があります。

\の個数を調べる
フルパスの最後の\を★に置換する
★の位置を調べる
★以降の文字列を取得

１の処理

フルパス（C:\folder1\folder2\folder3\test.txt）から\の個数を調べる。
１．フルパスの文字数を数える⇒LEN(A1)　結果は35
２．フルパスから\を削除する⇒SUBSTITUTE(A1,"\","")
　　SUBSTITUTEは文字列置換の関数で、結果は「C:folder1folder2folder3test.txt」となる。
３．２の文字数を数える。LEN(SUBSTITUTE(A1,"\","")　結果は31
４．\の個数を求める。LEN(A1)-LEN(SUBSTITUTE(A1,"\","")…①　結果は4

２の処理

フルパス（C:\folder1\folder2\folder3\test.txt）の最後の\を★に置換する。
★はわかりやすくするために記号を使っています。その他の任意の文字でOKです。
SUBSTITUTE(A1,"\","★",①)
①の結果が4なので4番目の\を★にする。結果はC:\folder1\folder2\folder3★test.txt
正しく関数を書くと、SUBSTITUTE(A1,"\","★",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))…②

３の処理

★の位置を調べる
FIND("★",②)　結果は27

正しく関数を書くと、
FIND("★",SUBSTITUTE(A1,"\","★",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))…③

４の処理

★以降の文字列を取得
MID(A1,③+1,LEN(A1))　結果はtest.txt
③の結果は27だがこのままだと★も含まれるのでその次の文字ということで+1している。その次のパラメータは抽出する文字数を指定する。本来はファイル名の文字数を計算すべきだが関数が長くなるので、LEN(A1)としている。
正しく関数を書くと、
MID(A1,FIND("★",SUBSTITUTE(A1,"\","★",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1))