関数の使い方

エクセル関数で特定の文字の後ろを取り出す

エクセルSUBSTITUTE関数､REPLACE関数で、特定の文字の後ろを抽出する方法を紹介しています。SUBSTITUTE関数とREPLACE関数は、文字を他の文字に置き換えることができる関数ですが、他の関数と組み合わせると、ある特定の文字の後ろだけ取り出すことができます。例えば、ある記号の後ろだけを取り出したい時など、色々な場面で使えます。

 

 

SUBSTITUTE関数で特定の文字の後ろを抽出

SUBSTITUTE関数は、「置き換えする文字」と、それを「置き換える文字」を指定して文字の置換を行っています。

SUBSTITUTE関数で特定の文字の後ろを抽出するポイントは、「置き換えする文字」の指定です。「置き換えする文字」をLEFT関数とFIND関数を使って指定することで、特定の文字の後ろを抽出することを可能にしています。

特定の文字の後ろを抽出

A列の「型番」の左端から「/」の前までを削除して「/」の後を抽出します。文字列の中に特定の文字が1つしかない時、この方法で特定の文字の後ろが抽出できます。

B2に式を入力しましょう。

B2　=SUBSTITUTE(A2,LEFT(A2,FIND(“/”,A2,1)),””)

「AF1025」が返されます。

B2の式は、SUBSTITUTE関数で「検索文字列」を「LEFT(A2,FIND(“/”,A2,1))」で指定しています。

「FIND(“/”,A2,1)」で「/」が「A2」の左端から何番目かを返しています。「7」番目です。

「7」番目という数は、LEFT関数で「A2」の左端から取り出す文字数です。

「LEFT(A2,FIND(“/”,A2,1))」で「201705/」を返し、SUBSTITUTE関数で「201705/」を「””」に置換えています。

「B2」の式を「B3:B5」にコピーしましょう。

「/」の後ろの文字が抽出されました！

ｎ番目の特定の文字の後ろを抽出

A列には「コード 」が入力されています。「コード 」の文字列には「-」が「3つ」入っています。

SUBSTITUTE関数を使って「3番目」に現れる「-」の後ろを抽出してみましょう。

ｎ番目の特定の文字の後ろを抽出するポイントは、「ｎ番目」に現れる「特定の文字」を「他の文字」に置換えることです。

B2に式を入力しましょう。

B2　=SUBSTITUTE(A2,LEFT(A2,FIND(“*”,SUBSTITUTE(A2,”-“,”*”,3))),””)

「P0013」が返されました。

「B2」の式は、SUBSTITUTE関数で「検索文字列」を「LEFT(A2,FIND(“*”,SUBSTITUTE(A2,”-“,”*”,3)))」で指定しています。

これが「置換える文字」になります。この「置換える文字」を「空白」にして「3番目」に現れる「-」の後ろを抽出しています。

SUBSTITUTE(A2,”-“,”*”,3)

この式は「3番目」に現れる「-」を「*」に置換えています。「1021-3198-8318*P0013」を返しています。

FIND(“*”,SUBSTITUTE(A2,”-“,”*”,3))

この式で「*」が左端から「何番目」かを返しています。「15」です。

LEFT(A2,FIND(“*”,SUBSTITUTE(A2,”-“,”*”,3)))

この式は、LEFT関数で左から「15文字」を取り出しています。文字数の「15」は「FIND(“*”,SUBSTITUTE(A2,”-“,”*”,3)) 」の結果です。

SUBSTITUTE関数で左端から「15文字」「1021-3198-8318*」が、「空白」に置換えられて抽出されます。

「B2」の式を「B3:B5」にコピーしましょう。

「3番目」に現れる「-」の後ろをが抽出されました！

 

 

REPLACE関数で特定の文字の後ろを抽出

REPLACE関数は、置き換える文字の「開始位置」と「文字数」を指定して置き換を行います。特定の文字の後ろを抽出するには、この「文字数」をFIND関数を使って取り出しています。

特定の文字の後ろを抽出

REPLACE関数で「A列の「型番」の左端から「/」の前までの文字を「空白」に置き換えて「/」の後を抽出します。

B2に式を入力しましょう。

B2　=REPLACE(A2,1,FIND(“/”,A2),””)

「AF1025」が返されます。

「B2」の式は、REPLACE関数で「文字数」を「FIND(“/”,A2)」で指定しています。

「FIND(“/”,A2)」は「/」が「A2」の左端から何番目かを返しています。「7」番目です。

B2」の左端から「7」文字を「空白」に置換えています。

「B2」の式を「B3:B5」にコピーしましょう。

「/」の後ろの文字が抽出されました！

 

 

ｎ番目の特定の文字の後ろを抽出

A列には「コード 」が入力されています。「コード 」の文字列には「-」が「3つ」入っています。

REPLACE関数を使って「3番目」に現れる「-」の後ろを抽出してみましょう。ｎ番目の特定の文字の後ろを抽出するポイントは、「ｎ番目」に現れる「特定の文字」を「他の文字」に置換えることです。

B2に式を入力しましょう。

B2　=REPLACE(A2,1,FIND(“*”,SUBSTITUTE(A2,”-“,”*”,3)),””)

「P0013」が返されます。

「B2」の式は、REPLACE関数で「文字数」を「LEFT(A2,FIND(“*”,SUBSTITUTE(A2,”-“,”*”,3)))」で指定しています。

これが「置換える文字」になります。この「置換える文字」を「空白」にして「3番目」に現れる「-」の後ろを抽出しています。

SUBSTITUTE(A2,”-“,”*”,3)

この式は「3番目」に現れる「-」を「*」に置換えています。「1021-3198-8318*P0013」を返しています。

FIND(“*”,SUBSTITUTE(A2,”-“,”*”,3))

この式で「*」が左端から「何番目」かを返しています。「15」です。

この「15」がREPLACE関数で「置換える文字」の「文字数」になります。

REPLACE関数で開始位置が「1」で、左端から「15文字」「1021-3198-8318*」が「空白」に置換えられます。

「B2」の式を「B3:B5」にコピーしましょう。

「3番目」に現れる「-」の後ろを抽出されました！

 

 

関数の説明

SUBSTITUTE関数

文字列の中にある指定された文字列を他の文字列に置き換えます。 文字列中の特定の文字を指定して置換えるときに使います。

SUBSTITUTE関数の書式と引数

SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象])

文字列置換える文字を含む文字列を指定します。文字列が入力されているセル参照を指定することもできます。
検索文字列置換する文字列を指定します。
置換文字列検索文字列を検索して置き換える文字列を指定します。
置換対象検索文字列に含まれているどの文字列を置換文字列と置き換えるかを、何番目で指定します。置換対象を指定した場合は、検索文字列中の指定した置換対象文字列だけが置き換えられます。 指定しない場合は、検索文字列中のすべての文字列が置換文字列に置き換えられます。

SUBSTITUTE関数の使い方

数式結果
=SUBSTITUTE(A2,”2017″,”2018″)「A2」に「2017年MRTファイル」が入力されています。「2017」を「2018」に置き換えます。結果は「2018年MRTファイル」を返します。

 

REPLACE関数

REPLACE関数は開始位置を指定して、指定された文字数だけ別の文字に置き換えます。特定の文字を取り出したい時に、いらない文字を空白に置き換えて抽出することもできます。非常に便利な関数です。

REPLACE関数の書式と引数

REPLACE(文字列,開始位置,文字数,置換文字列)

文字列置き換えを行う文字列を指定します。
開始位置置き換える文字の先頭文字の位置を、置き換えを行う文字列の左端から数えた数、数値で指定します。
文字数置き換える文字数を指定します。
置換文字列置き換える文字列を指定します。置き換えの対象となる文字列が、指定した文字列に変更されます。

REPLACE関数の使い方

数式結果
 =REPLACE(A2,4,1,”-“) 「A2」に「173*0034」が入力されています。「*」を「-」に置き換えます。結果は「173-0034」を返します。

 

LEFT関数

文字列の左から、指定した数の文字を抽出します。

LEFT関数の書式と引数

LEFT(文字列, [文字数])

文字列取り出す文字を含む文字列を指定します。
文字数省略できます。取り出す文字数を指定します。省略すると１が設定されます。

LEFT関数の使い方

数式結果
 =LEFT(A2,5) 「A2」に「MN125Q3025」が入力されています。左端から「5文字」取り出します。結果は「MN125」を返します。

 

 

FIND関数

指定された文字列を検索して、その文字列が最初に現れる位置を左端から数えて、その位置を番号で返します。

FIND関数の書式と引数

FIND(検索文字列,対象,開始位置)

検索文字列検索する文字列を指定します。
対象検索文字列を含む文字列を指定します。
開始位置検索開始位置を指定します。省略すると、開始位置は1です。

FIND関数の使い方

A列に「年度毎比率」が入力されています。文字列の中の「%」の位置を調べます。

数式結果
=FIND(“/”,A3,1)A2に「00125/32587」が入力されています。「/」の左端からの位置を調べます。結果は「6」を返します。

エクセルには文字を抽出する関数が他にも用意されています。

SUBSTITUTE関数とREPLACE関数は、他の関数と組み合わせると、特定の文字の後ろを取り出せますが、エクセル関数には他にも、文字を抽出できる関数があります。

代表的な関数に、LEFT関数、MID関数、RIGHT関数があります。他の「文字列操作」の関数と組み合わせるて、色々な抽出ができます。

こちらの記事もご覧ください。⇒エクセル関数で特定の文字列を抽出する方法!色々な文字列を抽出できます。

RECOMMENDこちらの記事も人気です。