【Excel】SUBSTITUTE関数で文字列を削除する方法｜メールアドレスや住所の一部を取り出す

Excelで名簿や住所録、顧客データを整理していると、セルの中に入っている文字列の一部だけを取り出したい場面があります。

たとえば、メールアドレスから＠以降のドメインだけを取り出したい場合や、住所から都道府県名を除いて市区町村以下だけを表示したい場合です。

このような作業を1件ずつ手入力で直すと、件数が増えたときに時間がかかります。入力ミスも起こりやすく、あとから並べ替えや集計をするときにも不便です。

そこで役立つのが、ExcelのSUBSTITUTE関数や、RIGHT関数、FIND関数、TEXTAFTER関数です。

SUBSTITUTE関数は、指定した文字列を別の文字列に置き換える関数です。置き換える文字を空白にすれば、不要な文字列を削除するように使えます。

この記事では、SUBSTITUTE関数で文字列を削除する基本、メールアドレスから＠以降を取り出す方法、住所から市区町村以下を取り出す方法、関数の使い分けをまとめます。

---

SUBSTITUTE関数で文字列を削除する基本

SUBSTITUTE関数は、セル内の指定した文字列を別の文字列に置き換える関数です。

基本の形は次のとおりです。

=SUBSTITUTE(文字列,検索文字列,置換文字列)

たとえば、セル内の「東京都」を削除したい場合は、「東京都」を空白に置き換えます。

=SUBSTITUTE(B3,"東京都","")

第3引数に "" を指定すると、検索した文字列を何もない状態に置き換えるため、結果として削除したように見えます。

指定する内容	意味
文字列	処理したいセルや文字列
検索文字列	消したい文字列
置換文字列	置き換え後の文字列
“”	空白に置き換える指定

SUBSTITUTE関数は、住所、商品コード、管理番号、メールアドレスなど、決まった文字列を取り除きたいときに使いやすい関数です。

メールアドレスから＠以降のドメインを取り出す方法

まずは、メールアドレスから＠以降のドメインを取り出す方法です。

たとえば、A3セルに次のメールアドレスが入っているとします。

example.user@gmail.com

ここから gmail.com だけを取り出したい場合、使いやすい方法はデータの形によって変わります。

メールアドレスだけの列ならRIGHT・LEN・FINDを使う

メールアドレスだけが1つのセルに入っている場合は、RIGHT関数、LEN関数、FIND関数を組み合わせる方法が使いやすいです。

A3セルのメールアドレスから、＠より右側のドメインを取り出す式は次のとおりです。

=RIGHT(A3,LEN(A3)-FIND("@",A3))

この式では、FIND関数で「@」の位置を探し、LEN関数で全体の文字数を数え、RIGHT関数で右側の文字だけを取り出しています。

関数	役割
FIND(“@”,A3)	メールアドレス内で「@」が何文字目にあるかを調べる
LEN(A3)	A3セルの文字数を数える
RIGHT(A3,文字数)	右側から指定した文字数を取り出す

example.user@gmail.com の場合、＠より右側にある gmail.com が取り出されます。

TEXTAFTER関数が使える場合は短い式で取り出せる

Microsoft 365など、TEXTAFTER関数が使えるExcel環境であれば、次の式でも＠以降のドメインを取り出せます。

=TEXTAFTER(A3,"@")

TEXTAFTER関数は、指定した文字より後ろの文字列を取り出す関数です。

今回のように「@より後ろだけを取り出したい」場合は、式を短くできます。

ただし、Excelのバージョンや利用環境によってはTEXTAFTER関数が使えない場合があります。その場合は、RIGHT・LEN・FINDを組み合わせた式を使うと対応しやすくなります。

SUBSTITUTE関数でドメインを取り出す方法

ユーザー名が別セルに入っている表では、SUBSTITUTE関数でもドメインを取り出せます。

たとえば、A3セルにメールアドレス、B3セルにユーザー名が入っている場合は、次の式を使います。

=SUBSTITUTE(A3,B3&"@","")

この式では、B3セルのユーザー名に「@」をつなげた文字列を作り、その部分を空白に置き換えています。

たとえば、A3セルが example.user@gmail.com、B3セルが example.user の場合、example.user@ が削除され、gmail.com だけが残ります。

ただし、この方法はユーザー名の列が別にある表に向いています。メールアドレスだけの一覧では、RIGHT・LEN・FINDを使う方がシンプルです。

＠を含めて取り出したい場合

ドメイン名として使う場合は、通常 gmail.com のように＠を含めずに取り出します。

一方で、表示上 @gmail.com のように＠を含めたい場合は、MID関数を使う方法があります。

=MID(A3,FIND("@",A3),LEN(A3))

この式では、＠の位置から後ろの文字列を取り出すため、結果に＠が含まれます。

住所から市区町村以下を取り出す方法

次に、住所から都道府県名を除いて、市区町村以下を取り出す方法です。

たとえば、次のような住所データがあるとします。

• 東京都新宿区西新宿1-2-3
• 大阪府大阪市北区梅田1-2-3
• 神奈川県横浜市中区〇〇町1-2-3

ここから都道府県名を除き、次のように市区町村以下だけを表示したい場合があります。

元の住所	取り出したい住所
東京都新宿区西新宿1-2-3	新宿区西新宿1-2-3
大阪府大阪市北区梅田1-2-3	大阪市北区梅田1-2-3
神奈川県横浜市中区〇〇町1-2-3	横浜市中区〇〇町1-2-3

都道府県名の列がある場合はSUBSTITUTE関数が使いやすい

住所データとは別に都道府県名の列がある場合は、SUBSTITUTE関数を使うと簡単です。

たとえば、B3セルにフル住所、C3セルに都道府県名が入っている場合は、次の式を使います。

=SUBSTITUTE(B3,C3,"")

この式は、B3セルの住所の中から、C3セルに入力されている都道府県名を探し、空白に置き換えます。

結果として、都道府県名だけが削除され、市区町村以下の住所が残ります。

都道府県名の列がない場合はMID・IF関数を使う

住所の列だけがあり、都道府県名を別セルに分けていない場合は、MID関数とIF関数を組み合わせる方法があります。

B3セルの住所から市区町村以下を取り出す式は次のとおりです。

=IF(MID(B3,4,1)="県",MID(B3,5,LEN(B3)),MID(B3,4,LEN(B3)))

この式では、住所の4文字目が「県」かどうかを確認しています。

東京都、大阪府、北海道などは3文字です。一方で、神奈川県、和歌山県、鹿児島県のように4文字の県名もあります。

そのため、4文字目が「県」かどうかを見て、取り出し開始位置を切り替えています。

ただし、この式は住所の先頭に都道府県名が入っていることを前提にしています。都道府県名が入っていない住所や、先頭にスペースがある住所が混ざっている場合は、結果がずれることがあります。

SUBSTITUTE関数とRIGHT・FIND・TEXTAFTERの使い分け

メールアドレスや住所の一部を取り出す方法は、1つではありません。

実務では、元データの形に合わせて使う式を選ぶことが大切です。

やりたいこと	使いやすい式	向いているデータ
メールアドレスだけの列からドメインを取り出す	=RIGHT(A3,LEN(A3)-FIND(“@”,A3))	メールアドレスが1セルに入っている表
TEXTAFTER関数で短く書く	=TEXTAFTER(A3,”@”)	TEXTAFTER関数が使えるExcel環境
ユーザー名を削除してドメインを残す	=SUBSTITUTE(A3,B3&”@”,””)	ユーザー名が別セルにある表
都道府県名を削除して住所を残す	=SUBSTITUTE(B3,C3,””)	都道府県名の列がある住所録
住所だけの列から市区町村以下を取り出す	=IF(MID(B3,4,1)=”県”,MID(B3,5,LEN(B3)),MID(B3,4,LEN(B3)))	住所の先頭に都道府県名が入っている表

SUBSTITUTE関数は「不要な文字列がはっきり分かっている場合」に向いています。

一方で、メールアドレスのように「@より右側を取り出したい」という場合は、RIGHT・FIND・TEXTAFTERの方が自然に書けることもあります。

うまく取り出せないときの確認ポイント

数式を入力してもうまく取り出せない場合は、元データの形式がそろっていない可能性があります。

特に、メールアドレスや住所録は手入力やコピー貼り付けが混ざりやすいため、次の点を確認してみてください。

メールアドレスの＠が全角になっていないか

式の中で半角の @ を探している場合、メールアドレス側の記号が全角の ＠ になっていると正しく処理できません。

通常のメールアドレスでは半角の @ を使います。コピーしたデータや手入力が混ざっている表では、全角になっていないか確認しましょう。

住所の先頭に余分なスペースが入っていないか

住所の先頭にスペースが入っていると、MID関数で取り出す位置がずれることがあります。

見た目では分かりにくい半角スペースや全角スペースが入っている場合もあるため、住所データを整理する前に余分な空白を確認しておくと安心です。

SUBSTITUTE関数は完全一致した文字列だけを置き換える

SUBSTITUTE関数は、指定した検索文字列と完全に一致した部分を置き換えます。

たとえば、C3セルに「東京都 」のように余分なスペースが入っていると、B3セル内の「東京都」と一致せず、削除できない場合があります。

うまく削除できないときは、次の点を確認してください。

エラー表示を避けたい場合はIFERRORを使う

メールアドレスの中に @ がない行が混ざっていると、FIND関数を使った式ではエラーになることがあります。

エラー表示を空白にしたい場合は、IFERROR関数を組み合わせます。

=IFERROR(RIGHT(A3,LEN(A3)-FIND("@",A3)),"")

この式では、ドメインを取り出せる場合は結果を表示し、エラーになる場合は空白を表示します。

顧客リストや問い合わせ一覧のように、データの入力状態が完全にそろっていない表では、IFERRORを組み合わせておくと見た目を整えやすくなります。

SUBSTITUTE関数で文字列を削除する方法まとめ

Excelでメールアドレスや住所の一部を取り出す場合は、元データのどこを基準にするかを考えることが大切です。

• SUBSTITUTE関数は、指定した文字列を別の文字列に置き換える関数
• 置換後の文字列に空白を指定すると、不要な文字列を削除できる
• 都道府県名の列がある住所録では、SUBSTITUTE関数で市区町村以下を残しやすい
• メールアドレスだけの列からドメインを取り出すなら、RIGHT・LEN・FINDの組み合わせが使いやすい
• TEXTAFTER関数が使える環境なら、短い式で＠以降を取り出せる
• 住所だけの列では、MID・IF関数で都道府県名の文字数を判定する方法がある

同じ「文字列を取り出す」作業でも、メールアドレスと住所では使いやすい式が異なります。

メールアドレスは「@の位置を基準にする」、住所は「都道府県名を削除する」と考えると、式を選びやすくなります。

まずは、元データの形を確認し、SUBSTITUTE関数、RIGHT・FIND関数、TEXTAFTER関数を使い分けてみてください。