CELL関数とFIND/MID関数を併用する方法が考えられますが、ここでは新しい関数を使ってより正確に抽出できる方法を試します。
- CELL関数とTEXTAFTER/TEXTBEFORE関数を併用することでファイル名を取得できます。
- TEXTSPLIT/CHOOSECOLS関数を併用する方法も考えられます。
手順
TEXTAFTER/TEXTBEFORE関数を併用する方法
画像は「テストファイル.xlsx」という名前のExcelファイルです（最上部の緑色の部分で確認できます）。
B3セルに式を入力して「テストファイル.xlsx」という文字列を取得しています。
B3セル
=TEXTBEFORE(TEXTAFTER(CELL("filename",B3),"[",-1),"]",-1) 「CELL("filename",B3)」によりパス・ファイル名・シート名からなる文字列を取得できますが、そのうちファイル名は「[」と「]」に挟まれた位置に存在します（下記備考参照）。
そこで、TEXTAFTER関数を使って文字列中の「[」より後の文字列を取得し、さらにその中の「]」より前の文字列をTEXTBEFORE関数を使って取得しています。これによりファイル名だけを取得することができます。
注意点として、CELL関数の第2引数を省略して「CELL("filename")」とすると、他のファイルを同時に開いてそちらのファイルでCELL関数が実行されたときに、こちらのCELL関数も一緒に書き換えられてしまうことがあります。つまり他のファイル名を表示してしまう可能性があるということです。ただし第2引数はB3に限らずどのセルでも問題ありません。
また、フォルダ名には「[」や「]」を使うことができるので、TEXTBEFORE/TEXTAFTER関数の第3引数（指定した文字列に該当するもののうち何番目を基準にするか）を「-1」として、最後のものを基準とするよう指定する必要があります。こうしないとフォルダ名を取得してしまうなど誤った結果になる恐れがあります（これについても下記備考を参照ください）。
TEXTSPLIT/CHOOSECOLS関数を併用する方法
ここでもB3セルに式を入力して「テストファイル.xlsx」という文字列を取得しています。
B3セル
=CHOOSECOLS(TEXTSPLIT(CELL("filename",B3),{"[","]"}),-2) CELL関数で取得した文字列について、TEXTSPLITを使い「[」と「]」の2つの文字を区切り文字として分割しています。
そしてCHOOSECOLS関数を使い、分割したもののうち後ろから2番目のもの（-2）を取得しています。これでファイル名が取得できます。
上記のようにフォルダ名に「[」や「]」が含まれる可能性があるので、前から2番目ではなく後ろから2番目を取得するようにしています。
備考
次の画像のように、フォルダ名には「[」や「]」の文字を使うことができます。
このフォルダにあるファイル内で「=CELL("filename",B2)」という式を実行すると、ファイル名の左右の「[」「]」だけでなくフォルダ名の「[」「]」も一緒に取得します。
上記の2つの例における引数の工夫は、フォルダ名に含まれる「[」や「]」の影響を避けるためのものです。
ちなみに「[」や「]」はシート名には使えず、ファイル名に使った場合はCELL関数で取得する際にカッコに変換されます。
