トップ > 関数 > 文字列 > FIND/SUBSTITUTE

メールアドレスからドメインを取り出す/ハイフンを消す

FIND(ファインド)/SUBSTITUTE(サブスティチュート)関数

メールアドレスから @ の後ろ(ドメイン)だけを取り出したい。ただし @ の位置はアドレスごとにバラバラで、LEFT や MID の「何文字目から」では指定できません。FIND は「指定した文字が何文字目にあるか」を探す関数で、MID と組み合わせると位置が不定の切り出しができます。SUBSTITUTE は「指定した文字を別の文字に置き換える」関数で、ハイフン除去のような整形の定番です。

✅ これができます:ドメイン別集計や、ハイフン無しの電話番号リストなど、形式がバラついたデータの整形ができるようになります

完成形(実行前 → 実行後)

実行前
実行前
実行後
実行後

数式を入れる前(左)は変換欄が空欄。入れた後(右)は、ドメイン(example.co.jp)が取り出され、電話番号はハイフン無しの数字だけになります

これをコピペすれば動きます

ドメインを取り出す数式(C2 に入れる。FIND で @ の位置を探し、MID で後ろを取る)

=MID($A2,FIND("@",$A2)+1,100)

電話番号のハイフンを消す数式(D2 に入れる)

=SUBSTITUTE($B2,"-","")

変えるのはここだけ

変える場所を示した図
数式の「変える場所」を番号で示した図。番号は下の表と対応しています
番号変える場所意味書き方・例
1対象のメールアドレス
$A2
ドメインを取り出したいセルURL からパス部分を取り出すなど、区切りの後ろが欲しい場面全般に応用できる
2探す文字(区切り)
"@"
FIND が位置を探す文字。+1 は「区切りの次の文字から取る」という意味「-」で区切られたコードなら "-" に変える。大文字と小文字は別物として探す点に注意
3対象の電話番号
$B2
ハイフンを消したいセル郵便番号や口座番号など、区切りを消したい列全般に使える
4消す文字と置換後
"-"
SUBSTITUTE の「探す文字」。3番目の ""(空っぽ)にすると、その文字が消えるスペースを消すなら " "、全角ハイフンなど見た目が似た別文字には注意

組み込み手順

  1. サンプル表を用意します。A列にメールアドレス、B列に電話番号を並べます
  2. C2 にドメイン抽出の数式を貼り、@ の後ろだけが取り出されれば成功です
  3. D2 にハイフン除去の数式を貼り、数字だけの電話番号になることを確認します
  4. 両方とも、下の行までコピーすれば全行に適用されます
  5. 自分の表で使うときは、「変えるのはここだけ」の①〜④を自分のデータに差し替えます

このページのサンプル表は、A列にメールアドレス(tanaka@example.co.jp/sato@example.co.jp)、B列に電話番号(090-1234-5678/080-9876-5432)が入っています。ドメインは両方とも example.co.jp です。

Excelとスプレッドシートの違い

すべてのバージョンの Excel で使えます。FIND は大文字・小文字を区別します。区別したくない場合は SEARCH という姉妹関数があり、ワイルドカードも使えます。

Googleスプレッドシートでも FIND/SUBSTITUTE がそのまま使えます。数式・書き方は Excel と同じです。

カスタマイズ例

ドメインではなく、@ の前(ユーザー名)を取り出したい

LEFT と組み合わせます。=LEFT($A2,FIND("@",$A2)-1) とすると、@ の手前までが取れます(-1 は区切り文字自体を除くため)。

2つ目以降のハイフンだけを消したい

SUBSTITUTE の4番目の引数に「何番目のものか」を指定できます。=SUBSTITUTE($B2,"-","",1) なら、最初のハイフンだけが消えます。

全角を半角にそろえたい

置換ではなく、ASC 関数で全角英数字を半角に変換できます。=ASC($A2) の形です。名簿の整形でよくセットになります。

つまずきどころ

関連する関数・レシピ

次はこれ:ROUND系(税込計算の端数を、四捨五入・切り上げ・切り捨て)
文字列の次は数値の丸めです。お金の計算で必ず出てくる端数処理から

よくある質問

Q. FIND と SEARCH の違いは?

A. FIND は大文字・小文字を区別し、SEARCH は区別しません。また SEARCH は * や ? のワイルドカードが使えます。

Q. 置換ではなく削除したいだけなのに、なぜ置換の関数を使うの?

A. 「空っぽの文字列に置き換える=消える」という考え方です。文字列の削除は、置換の特殊な形として扱われます。

Q. Mac でも使える?

A. 使えます。Mac版 Excel と、ブラウザ版の Googleスプレッドシートで動作します。

PR(広告)

もっと体系的に学びたい人へ
独学で体系化するなら、講座で一気に学ぶのが近道です。

Udemy:Excelショートカット講座を見る Udemy:Googleスプレッドシート入門講座を見る