拡張子・拡張子を除いたファイル名を得る関数＠Excel

👨「ここにファイルの場所*1を一覧で書きだしたファイルがある．ここから，ファイルの拡張子と，ファイルの拡張子を除いた名前を切り分けてほしい」

仕事中，たまにたまにこういう依頼が飛び込んでくることがあります．
これも，ファイル名とフォルダに切り分ける作業同様，単純に見えて意外と面倒くさいので，やはり

👩「やっぱりVBAでなんとかしましたぁ🌟」

こうなるのがやはりよくあるパターンなのですが，今回もあえてExcel関数で攻略してみましょう．
例によってまずは結論から――こうなります．

f:id:misora05:20190301070434p:plain

以下，順を追って見ていきます．

なお，A1セルにファイルのフルパスが記述されているものとします．

最後の"."が先頭から何文字目にあるかを調べる．

これは前回やりましたね．

=FIND("?",SUBSTITUTE(A1,".","?",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))

例では28文字目に最後の"."があると判断されました．

なお，フォルダ名に"."があったり，二重拡張子が付いている例などもあるため，単に「先頭からみて最初の"."を探す」という方法は誤りです．注意．
右端から，先の方法で探された"."の次の文字までを取得する．

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

例ではC:\Windows\System32\mspaint.exeという全長31文字の文字列のから，1で得た28を減じた数すなわち3文字を取得すれば，それが拡張子となります．
答えその1：上記2つを1つにまとめてみよう．

=RIGHT(A1,LEN(A1)-FIND("?",SUBSTITUTE(A1,".","?",LEN(A1)-LEN(SUBSTITUTE(A1,".","")))))

長いですが，順を追ってみれば単純かと思います．

なお，A1セルにファイルのフルパスが記述されているものとします．

最後の"\"，および"."がそれぞれ先頭から何文字目にあるかを調べる．

前者は以下の関数で求められます．
また，後者は前項の1で既に求めているため，省略します．

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

例では20文字目に最後の"\"があると判断されました．
これで求めた位置の次の文字から，前項1で求めた位置の直前の文字までを取得する．

これにはMID関数を使用します．

=MID(A1,C6+1,C1-C6-1)

例ではC:\Windows\System32\mspaint.exeという全長31文字の文字列の21文字目から，28 - 20 - 1 = 7文字を取得すれば，それが拡張子を除いたファイル名となります．
答えその2：上記2つを1つにまとめてみよう．

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

長い！ちょっとこれは長すぎる！