後ろから(右から）検索

関数だけで、後ろから検索してファイル名をフルパスから抽出する方法を紹介します。

2013EXCEL関数技BEST58回　後ろから検索

後ろから(右から）検索　

フルパスからファイル名を取得したいなどというケースで、Excelで関数を使ってどうすればできるかを考えます。
管理人の場合は、楽なことが大好きなので、ふだんは便利なフリーソフトのLinameを使っています。
Excelでやるとしたら、ユーザー定義関数を使いたいところですが、どうしても関数だけでやりたいというユーザーの為に、紹介しましょう。
（サンプルファイルは、こちらから 2013関数技58回サンプルデータ）

後ろから検索してファイル名をフルパスから抽出

ファイル名をフルパスから抽出します。

右から何文字というように、文字数が決まっているならすぐにRIGHT関数が使えますが、フィアル名の文字数は決まっていません。
うしろから文字列を取り出すために、それぞれ固有のファイル名の文字数をカウントして、RIGHT関数に、文字数を渡してやる必要があるのです。

=RIGHT(B2,[取り出したい文字数])
=RIGHT(B2,[B2全体の文字数-不要な部分の文字数])  

B2全体の文字数は、 =LEN(B2) なので

=RIGHT(B2,[取り出したい文字数])
=RIGHT(B2,LEN(B2)-[不要な部分の文字数])  

不要な部分の文字数を出す式を考えればいいということがわかりますね。

不要な部分の文字数を出す

初めは少し遠回りのようです。これも、定番のやり方なのですが、文字列にある区切りを見つけます。

LEN関数で文字列の文字数を出したものから、SUBSTITUTE関数で区切りを""に変換して無くしてしまったものを引きます。

後ろから取り出したい文字数は

後ろから取り出したい文字数は、何個かを出します。
区切りが何個かは、わかっていますから、その最後の区切りがポイントに成ります。

区切りは同じものが３つあるので、３番目に現れた区切りを普段使わない内部コードに対応する文字 に変換しましょう。
今回使うのは、CHAR(9)やCHAR(13)などです。
下図のFIND関数の書式の対象を、SUBSTITUTE 関数を使って、3番目の区切りをCHAR(9)やCHAR(13)変換しておきます。

SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象])
　　↓
SUBSTITUTE(B2,検索文字列,CHAR(9),3番目）

3番目の区切りをCHAR(9)に変えて、CHAR(9)をFIND関数で見つけると、FIND関数は位置を返すので、何文字目にあるか（文字数）がわかります。

ここまでは、何文字かを出す式

ここまでは何文字か（不要な部分の文字数）は、

=FIND(CHAR(9),SUBSTITUTE関数の式)

全体は、このようになります。

=RIGHT(B2,LEN(B2)-FIND(CHAR(9),SUBSTITUTE関数の式))  

数式をコピーしてファイル名をフルパスから抽出することができました。