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

後ろから(右から)検索する方法 RIGHT関数、LEN関数

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

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

ファイル名をフルパスから抽出します。
ファイル名をフルパスから抽出
隣の列にファイル名だけ抽出しました。
ファイル名を隣の列に抽出
右から何文字というように、文字数が決まっているならすぐにRIGHT関数が使えますが、フィアル名の文字数は決まっていません。
うしろから文字列を取り出すために、それぞれ固有のファイル名の文字数をカウントして、RIGHT関数に、文字数を渡してやる必要があるのです。
RIGHT関数
=RIGHT(B2,[取り出したい文字数])
=RIGHT(B2,[B2全体の文字数-不要な部分の文字数])

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

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

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

初めは少し遠回りのようです。これも、定番のやり方なのですが、文字列にある区切りを見つけます。
後ろから検索してファイル名をフルパスから抽出2
LEN関数で文字列の文字数を出したものから、SUBSTITUTE関数で区切りを""に変換して無くしてしまったものを引きます。
後ろから検索してファイル名をフルパスから抽出3
スポンサーリンク
スポンサーリンク

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

後ろから取り出したい文字数は、何個かを出します。
区切りが何個かは、わかっていますから、その最後の区切りがポイントに成ります。
後ろから取り出したい文字数
区切りは同じものが3つあるので、3番目に現れた区切りを普段使わない内部コードに対応する文字 に変換しましょう。
今回使うのは、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関数

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

ここまでは何文字か(不要な部分の文字数)は、
=FIND(CHAR(9),SUBSTITUTE関数の式)
全体は、このようになります。
=RIGHT(B2,LEN(B2)-FIND(CHAR(9),SUBSTITUTE関数の式))

後ろから検索してファイル名をフルパスから抽出4
数式をコピーしてファイル名をフルパスから抽出することができました。
後ろから検索してファイル名をフルパスから抽出5
今日の講義は以上です。お疲れ様でした。
スポンサーリンク
スポンサーリンク