Excelでメールアドレスの@の前(ローカル部)と後ろ(ドメイン)を分離取得する方法
本エントリーの目次
Excelで、メールアドレスの@の前の部分と後ろの部分を分離(分割)・取得したい！
氏名や住所、使用しているメールアドレスなどの各種情報を、Excelファイルで管理・保管することがあります。
そしてメールアドレスについては、単一のセルにメールアドレスの先頭から末尾(最後の部分)までをすべて入力し、保存しているケースが多いでしょう。
ところが業務の都合などにより、メールアドレスの@の前の部分と後ろの部分を分割。
それぞれを個別に取得したいと思うことがあります。
そこで今回は、Excelでメールアドレスの@の前の部分と後ろの部分を分割・取得する方法をご紹介します！
Excelで、メールアドレスの@の前の部分と後ろの部分を分割・取得する方法
通常、メールアドレスの@の前の部分はローカル部やローカルパートと呼ばれています。
また@の後ろの部分はドメインを表しています。
そのためメールアドレスは、『ローカル部(ローカルパート)@ドメイン』という形式となります。
Excelで、メールアドレスの@の前の部分と後ろの部分。
つまりローカル部とドメインを分割・取得したい場合には、以下のような方法で対応可能です。
『区切り位置』機能を使って分割する方法
メールアドレスのローカル部とドメインは、『区切り位置』機能を使うことで簡単に分割できます。
たとえば以下のようなデータが入力されているExcelファイルがあったとしましょう。
A列に入力されているメールアドレスのローカル部をB列に。
ドメインをC列に分割して取得したい場合には、まずは対象のデータ範囲を以下のように選択してください。
次に、画面上部の『データ』タブ上にある『データツール』グループ内の『区切り位置』ボタンをクリックします。
『区切り位置指定ウィザード – 1 / 3』が表示されるので、『元のデータの形式』部で『カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ』設定を選択してから、『次へ』ボタンをクリックします。
『区切り位置指定ウィザード – 2 / 3』では、『区切り文字』設定部で『その他』にチェックを入れてから『@』文字を入力し、『次へ』ボタンをクリックします。(『その他』以外のチェックはすべて外す。)
『区切り位置指定ウィザード – 3 / 3』の『表示先』設定部では、区切り位置にしたがって分割したデータの表示位置を設定してください。
今回の例では、分割したデータはB2セルを起点に分割・表示させたいため、『表示先』設定部をクリックしてからB2セルをクリックします。
※B2セルをクリックすると、『表示先』設定部には自動的に=$B$2という設定が入力されます。
設定が終わったら画面右下の『完了』ボタンをクリックすることで、以下のように分割処理が実行されます。
尚、初期設定状態の『区切り位置指定ウィザード 』は、分割前の情報が保存されているセルの内容を、分割後の左端のデータ(今回の例ではローカル部)で上書きする設定となっています。
そこでデータが上書きされるのを避けるために、先にご紹介した手順では『区切り位置指定ウィザード – 3 / 3』で『表示先』の設定を変更しています。
ですが上書きされても問題がない場合には、『表示先』の設定変更は不要です。
数式を使って分割する方法
Excelの数式を使って、メールアドレスのローカル部とドメインを分割することも可能です。
『区切り位置』機能を使って分割する方法で使用した以下サンプルデータを使って、その方法をご紹介しましょう。
数式を使って分割するケースでは、LEFT、RIGHT、FIND、LEN関数を使う方法と、MID、FIND、LENを使う方法で対応可能です。
それぞれについて、以下のような数式をB2セルとC2セルに入力してください。
LEFT、RIGHT、FIND、LEN関数を使う方法：
1 2 3 4 5 | B2セルに入力する数式 =LEFT(A2,FIND("@",A2)-1) C2セルに入力する数式 =RIGHT(A2,LEN(A2)-FIND("@",A2)) |
MID、FIND、LENを使う方法：
1 2 3 4 5 | B2セルに入力する数式 =MID(A2,1,FIND("@",A2)-1) C2セルに入力する数式 =MID(A2,FIND("@",A2)+1,LEN(A2)-FIND("@",A2)) |
どちらも、メールアドレスの@の前の部分と後ろの部分のみを取得する数式となっているため、お好きな方を利用ください。
B2セルとC2セルへの数式入力が完了したら、B2:C2セル範囲の数式を下方にオートフィル操作でコピーしましょう。
この操作により、数式を使ったローカル部とドメインの分割が行われます。
メールアドレス中の『@』文字の数が1ではないものに、エラー表示をする方法
冒頭にも書いたとおり、メールアドレスは『ローカル部(ローカルパート)@ドメイン』という形式となります。
そのため、アドレス中の『@』文字の数が1ではないメールアドレスは、エラー表示を実施。
そしてここまでにご紹介した『区切り位置』機能を使って分割する方法や数式を使って分割する方法を利用する前に、エラーデータを修正する。
または当該データを対象外として、分割処理を実施するなどの対応を行うと良い※でしょう。
※メールアドレス中の『@』文字の数が1ではないケースでは、数式の実行結果がエラーとなってしまったり、3列以上にデータが分割されてしまうなどの問題が発生します。
先ほど例示したメールアドレスの一覧データに、3件のエラーデータを追加した以下のようなサンプルデータがあったとしましょう。
このデータのB列に、エラー表示を行う列を追加します。
そしてB2セルに、以下のような数式を入力してください。
1 | =IF(LEN(A2)-LEN(SUBSTITUTE(A2,"@",""))=1,"","有") |
次にB2セルの数式を下方にオートフィルでコピーすると、メールアドレス中の『@』文字の数が1ではないデータに対して、エラー表示を行う列に『有』表記が行われるようになります。
以上、参考になさってくださーい！