Excel 関数だけでフォルダパスとファイル名をそれぞれ抽出する

Excel 関数だけを使って、ファイルのフルパス文字列からフォルダパスとファイル名をそれぞれ抽出してみる。

以下は A1 セルに「C:\Hoge\Fuga\FooBar.txt」という文字列が記載されている場合の例である。

まずはフォルダパス部分を抽出する。

' フォルダパスを取得する：最後に登場する「\」より手前の文字列を取得する
=LEFT(A1, FIND("*", SUBSTITUTE(A1, "\", "*", LEN(A1) - LEN(SUBSTITUTE(A1, "\", ""))), 1) - 1)

' →「C:\Hoge\Fuga」が取得できる
' 関数の最後の「- 1」がなければ「C:\Hoge\Fuga\」になる

次に、フルパスからファイル名だけを抽出する。

' ファイル名を取得する：最後に登場する「\」から先の文字列を取得する
=RIGHT(A1, LEN(A1) - FIND("*", SUBSTITUTE(A1, "\", "*", LEN(A1) - LEN(SUBSTITUTE(A1, "\", "")))))

' →「FooBar.txt」が取得できる

コメントでも書いているが、この2つの関数は、指定の文字列 (= A1 セル) の最初や最後に登場する、指定の文字 (=「\」) を判別し、その前後を取得しようとしている。そのため、2つの関数の中で使っている処理は一部同じものだ。

' FIND 関数部分が同じ
           =LEFT(A1, FIND("*", SUBSTITUTE(A1, "\", "*", LEN(A1) - LEN(SUBSTITUTE(A1, "\", ""))) , 1) - 1)
=RIGHT(A1, LEN(A1) - FIND("*", SUBSTITUTE(A1, "\", "*", LEN(A1) - LEN(SUBSTITUTE(A1, "\", ""))) ))

中で何をやっているかというと、指定の文字列が最後に登場する場所を見つけるために、指定の文字列を別の文字 (ここでは「*」) に置換し、その数を数えている。あとはこの数を元に、指定の文字列の長さ (LEN()) からその数だけ位置をズラしたりしているのだ。

仕組みが分かれば、関数内にそれぞれ2回ずつ登場している「\」記号を変更することで違う使い方ができる。置換後の文字列アスタリスク「*」が指定の文字列と競合しそうであれば、こちらも変えてもらえばいい。