ファイル名とフォルダ名を得る関数@Excel
👨「ここにファイルの場所*1を一覧で書きだしたファイルがある.ここから,フォルダ名とファイル名を切り分けてほしい」
仕事中,たまにこういう依頼が飛び込んでくることがあります.
これ,単純に見えて意外と面倒くさいので,
👩「VBAでなんとかしましたぁ🌟」
こうなるのがよくあるパターンなのですが,今回はあえてExcel関数で攻略してみましょう.
Excel関数のみで,ファイルパスからファイル名とフォルダ名を得る
まずは結論から――こうなります.
順を追って見ていきます.
最後のパス区切り文字"\"が,何番目の"\"なのかを調べる.
「パスの区切り文字"\"をすべて空文字列に置換した文字列の長さと,パスの全文字列の差を求める」.
これで,最後の区切り文字"\"が,そのパスの中で何番目の"\"なのかを知ることができます.=LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))
例では3番目の"\"が最後のパス区切り文字であると判定されました.
それを適当な文字列に置換する.
「適当な文字列」と言っても,本当に適当ではだめですw
ファイル名・フォルダ名に使われることがあり得ない文字列,たとえば"?"や"*"に置換するのがベストでしょう.=SUBSTITUTE(A1,"\","?",C1)
例では3番目の"\"を"?"に置換し,"C:\Windows\System32?mspaint.exe"という文字列を得ました.
その置換された文字列が前方から何文字目にあるのかを調べる.
これは簡単ですね.普通に
FIND
関数を使いましょう.=FIND("?",C2)
例では,"C:\Windows\System32?mspaint.exe"の中で"?"という文字列,すなわち最後の区切り文字"\"だった文字列は,前方から20文字目にあると判定されました.
答えその1:フォルダ名を得る
文字列の左端から,上で判定された位置の1文字前までを取得すれば,それがフォルダ名になります.
=LEFT(A1,C3-1)
例では,"C:\Windows\System32?mspaint.exe"の先頭から19文字までを取得し,C:\Windows\System32という文字列を得ました.これがフォルダ名ですね.
答えその2:ファイル名を得る
文字列の右端から,上で判定された位置の次の文字までを取得すれば,それがファイル名になります.
=RIGHT(A1,LEN(A1)-C3)
例では,"C:\Windows\System32?mspaint.exe"という全長31文字の文字列から,3で得た20を減じた数すなわち11文字を,文字列の右端から取得します.これがファイル名になります.
まとめ
上記を1つの関数にまとめてみましょう.
なお,ここではA1セルにフルパスが書かれているものとします.
フルパスからフォルダ名を得る
これはまとめるとこうなります.
=LEFT(A1,FIND("?",SUBSTITUTE(A1,"\","?",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))-1)
長w
フルパスからフォルダ名を得る
これはまとめるとこうなります.
=RIGHT(A1,LEN(A1)-FIND("?",SUBSTITUTE(A1,"\","?",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))
前に増して長いですねw
結論として
👩「関数を使ってもできなくはないけど,VBAを使った方が楽」
まぁこうなりますけど,VBAを使えない人に教えるならこうなりますね.
*1:ファイルパス