Excelの関数だけで「正しく」ファイル名を取得する

Excelの関数

普段、私は仕事で使う資料の作成に、Excelを利用することが多いです。もちろん、工数を削減したいのでExcelの関数をガンガン使っています。
またExcelのファイル名は、先頭に「20200419_」など日付を付けることもあるので、関数によりExcelのファイル名を取得できれば、処理が楽になります。
そこでExcelの関数でファイル名を取得するのは簡単だろうと思いネットで検索しましたが、数多くヒットするものは「正しく」ファイル名を取得できていないことに気付きました。
今回は、Excelの関数だけで「正しく」ファイル名を取得する方法を紹介します。

CELL関数

ファイル名を取得するための基本はCELL関数です。次のように指定すると、

=CELL("filename")

このように出力されます。

C:\work\[ワークシート.xlsx]Sheet1

「ファイルのフルパス」「ファイル名」「シート名」ですね。このうち、ファイル名は角括弧で囲まれているので、多くのサイトではこの角括弧の内側を取得するような紹介が多いです。
では、ファイル名に角括弧が含まれているとどのように出力されるのでしょうか？ファイル名「[bracket]ワークシート.xlsx」内でCELL関数を実行すると、次のように出力されました。

C:\work\[(bracket)ワークシート.xlsx]Sheet1

ファイル名に角括弧が入っている場合、丸括弧に置換されています。正しいファイル名ではないですが、CELL関数の仕様ですので、これは受け入れざるを得ません。

ではフォルダ名に角括弧が入っている場合はどうでしょう。

C:\work\[bracket]\[ワークシート.xlsx]Sheet1

結果は、上記のようにフォルダ名の角括弧はそのまま出力されます。
そうなんです。フォルダの角括弧はそのままですので、多くのサイトで紹介されている角括弧の内側を取得する方法を用いると、最初の角括弧を検索してしまうため、フォルダ名が表示されてしまうのです。

なおシート名にはそもそも角括弧を入れることはできないので、こちらは気にしなくても大丈夫ですね。

「正しく」ファイル名を取得する

結論だけ先に書いておきます。急いでいる方は、下記をそのままコピペしてください。

=MID(RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND(CHAR(9),SUBSTITUTE(CELL("filename"),"\",CHAR(9),LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"\",""))))),2,FIND("]", RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND(CHAR(9),SUBSTITUTE(CELL("filename"),"\",CHAR(9),LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"\","")))))) - 2)

かなり複雑ですね。。。では一つ一つ説明します。次の位置にあるExcelファイルのファイル名を取得してみましょう。

C:\work\[bracket]\[20200419_ワークシート.xlsx]Sheet1

一気に全ての関数を書いてしまうと、何をしているのか全く分からない状態になってしまうので、細かく分解して紹介します。

まずはファイル名を含むフルパス＋シート名の取得です。シートのA1に次のように入力しました。

=CELL("filename")

上記により、次の値が得られます。

C:\work\[bracket]\[20200419_ワークシート.xlsx]Sheet1

まずは上記から、ファイル名＋シート名の部分を抜き出します。それには最後に出現する「\」の位置を検索し、それ以降の文字を取得します。
まずは「\」の個数を数えましょう。シートのA2に次のように入力しました。

=LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))

A1も文字列の長さと、「\」を空文字にしたときの文字列の長さの差分で「\」の個数をカウントしています。

最後に出現する「\」の位置は次のようになります。シートのA3に次のように入力しました。

=FIND(CHAR(9),SUBSTITUTE(A1,"\",CHAR(9),A2))

最後の「\」を通常使用されることのない文字「CHAR(9)」に変換し、その位置を取得しています。
では、最後に出現する「\」以降の文字列を取得しましょう。シートのA4に次のように入力しました。

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

ここから角括弧の中身を取り出します。シートのA5に次のように入力しました。

=MID(A4,2,FIND("]", A4) - 2)

これでファイル名を取得できました。
これらを組み合わせると次のようになります。

=MID(RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND(CHAR(9),SUBSTITUTE(CELL("filename"),"\",CHAR(9),LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"\",""))))),2,FIND("]", RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND(CHAR(9),SUBSTITUTE(CELL("filename"),"\",CHAR(9),LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"\","")))))) - 2)

ファイル名先頭の日付文字列を取得する

さて、もともと私が取得したかったのは、ファイル名先頭にある日付文字列です。この場合は、先ほどの閉じ角括弧「]」を「_」に変更するだけで取得できます。

=MID(RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND(CHAR(9),SUBSTITUTE(CELL("filename"),"\",CHAR(9),LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"\",""))))),2,FIND("_", RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND(CHAR(9),SUBSTITUTE(CELL("filename"),"\",CHAR(9),LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"\","")))))) - 2)

まとめ

Excelの関数は非常に便利ですが、関数の機能が不足していて小手先のテクニックを駆使して利用する必要があります。それにより関数が非常に長くなってしまい、後から確認しても理解不能なものになってしまいます。
「正しく」ファイル名を取得するときも同様です。取り急ぎ、上記で紹介した関数を利用してみてください。

TKS2ではExcelの関数はもちろんのことVBAマクロも対応可能です。 もしお困りのときはぜひお声がけください。