【Excel】関数で文字列の右から(後ろから)指定範囲を抽出する

2016.06.06
プロモーションが含まれています

【Excel】関数で後ろから(右から)指定範囲を抽出する方法

Excel関数で、文字列の右から(後ろから)対象文字を探して、指定範囲を抽出する方法

Excel関連記事

関連記事をもっとみる

右から(後ろから)の抽出はRIGHT関数でできる

文字列の右から(後ろから)の抽出は、RIGHT関数を使います。

RIGHT(“sample@yahoo.co.jp”,11)　→　yahoo.co.jp
このままだとそれぞれのセルで正しくない位置で抽出されてしまい柔軟に対応できないので、区切り位置を関数で抽出し、可変にしてあげます。

特定文字を検索して、RIGHT関数で右から切り取り

特定文字が一か所しか出現しない場合は、LEN関数とFIND関数とRIGHT関数を組み合わせることで特定文字から右が抽出できます。

まずは、FIND関数で特定位置の検索をします。
この例では、A1にsample@yahoo.co.jpの値があるとし、検索文字列を「@」となっているものとします。

 

① FIND(“@”,A1,1)　→　7
② LEN(A1)　→　18

①で検索文字列である「@」の出現位置をFIND関数で検索します。この場合、7文字目に「@」が見つかります。
ですが、FIND関数は左から検索するので、そのままではRIGHT関数が使えません。

次に、対象文字列の文字数をLEN関数で、カウントします。
①で計算されたのは、「@」より前の文字数なので、「@」より後ろの文字数を計算するには、全体の文字数から(②)、検索文字列の出現位置①を引けば、計算できます。

その値をRIGHT関数で利用すると、右から切り取りできます。

RIGHT(A1,LEN(A1)-FIND(“@”,A1,1))

特定文字が一か所しか出現しない場合は、これだけで抽出できます。

 

特定文字を検索して、REPLACE関数で置換

特定文字が一か所しか出現しない場合は、REPLACE関数とFIND関数を組み合わせることで、左から特定文字までを、空白で置換（削除）することにより、右を抽出できます。

まずは、FIND関数で特定位置の検索をします。これは上の例と同じです。

出現位置がわかったら、REPLACE関数で空白に置換します。

REPLACE(A1,1,FIND(“@”,A1,1),””)
この方法でも、右から抽出できます。

ショートカットキーで右から切り取る

例えば、下のような名前のリストがあるとします。
この中から名前だけを抽出したい場合は、ショートカットキーで簡単に名前だけ抽出できます。

まず、抽出結果を出力したい列の一番上のセルに「山田　太郎」さんの名前である、「太郎」を入力しておきます。

次に、「太郎」と入力されたセルを選択して、「Ctrl」+「E」キーを押すと…

このように、自動でひとつめのデータから法則性を見つけ出して、入力してくれます！
上の表は全角スペースで区切っていますが、半角スペースが混じっていても正しく抽出できました！

名前やメールアドレスの抽出なら、これでパッとできますね！

「Ctrl」+「E」キーで「フラッシュフィル」！

特定文字が複数出現する場合に、右から切り取る

特定文字が複数出現する場合には、工夫が必要です。

1. 特定文字の出現回数を数える

指定範囲を抽出したいなら特定文字の出現回数を数えましょう。

上で解説した通り、LEN関数を使うと文字列の文字数を出してくれます。

①LEN(“sample@yahoo.co.jp”)　→　18
この文字列の文字数を使って計算していきます。
例えば「.」が区切り文字だとしたら「.」をSUBSTITUTE関数で消すと、消えた文字数　=　区切り文字の数といえます。
②LEN(SUBSTITUTE(“sample@yahoo.co.jp”,”.”,””))　→　16
①から②を引くと2なので、対象文字列の中に区切り文字は、2回出現することになります。
区切り文字が1文字以上の場合は注意が必要です。
この例では、A1にあい<br>かきく<br>さしの値があるとし、検索文字列を「<br>」となっているものとします。
① LEN(A1)　→　15
② LEN(SUBSTITUTE(A1,”<br>”,””))　→　7
③ (LEN(A1)-LEN(SUBSTITUTE(A1,”<br>”,””)))/4　→　2

例えば「<br>」が区切り文字だとしたら「<br>」をSUBSTITUTE関数で消す計算、①から②を引くと8となりますが、区切り文字の「<br>」が4文字なので、計算した値を4で割ります。

そうすることで、対象文字列の中に区切り文字は、2回出現することになります。
この出現回数をSUBSTITUTE関数で使っていきます。

2. 取り出したい箇所に区切り文字に目印をつける

上の関数で区切り文字の出現回数がわかったので、一番最後の区切り文字が何個目になるかもわかりました。
こうなれば、区切り文字が複数あってもSUBSTITUTE関数で、最後の区切り文字に目印をつけられます。

その前に、SUBSTITUTE関数のおさらいをしておきましょう。
SUBSTITUTE関数の第4引数で、何番目に出現する検索文字列を置換するかを指定できます。
（指定しない場合は、全部置換されます）

上の関数で計算した、区切り文字の出現回数を指定すれば、最後の出現位置がわかるということです。

=SUBSTITUTE(文字列,検索文字列,置換文字列,何番目の検索文字列)

普段使わないような目印がいいので、ここでは「@」とします。
「1.区切り文字を数える」の関数と組み合わせます。

A1にあい<br>かきく<br>さしの値が、B1に上の関数の値(2)があるとします。

SUBSTITUTE(A1,”<br>”,”@”,B1)　→　あい<br>かきく@さし

3.目印を頼りに切り取り(RIGHT関数)

目印をつけられたら、後は上の「特定文字を検索して、RIGHT関数で右から切り取り」でも「特定文字を検索して、REPLACE関数で置換」どちらを使っても大丈夫です。

・RIGHT(C1,LEN(C1)-FIND(“@”,C1,1))
・REPLACE(C1,1,FIND(“@”,C1,1),””)

コメント

タイトルとURLをコピーしました