【Excel】フラッシュフィルとTEXT関数で1つの列からメールアドレスを一括作成する方法

フラッシュフィルを使ったメールアドレスの作成

「1つの列に入力されたデータを、別の形式や複数の列に変換して抽出するにはどうすればよいのか？」——これはExcelユーザーから非常によく寄せられる質問です。

まずはフラッシュフィル機能を使う方法から見ていきましょう。フラッシュフィルは、Microsoft Excel 2013以降のバージョンに搭載された便利な機能です。

以下の例では、A列にスタッフの名前（ファーストネーム）と姓（ラストネーム）のリストが入力されています。フラッシュフィルが動作するには、必ず元となるソース列が必要です。ここでは、スタッフ名が入力されているA列がソース列にあたります。フラッシュフィルは、メールアドレスのような定型的な繰り返しデータを生成する場面でよく活用されます。また、メールアドレスの生成にはテキスト関数も多用されます。テキスト関数には、複雑なものからシンプルなものまでさまざまな種類があります。本記事では、フラッシュフィルによるメールアドレス生成の例、テキスト関数による生成の例に加えて、読者の方からいただいたテキスト関数の提案もご紹介します。

手順：フラッシュフィルでメールアドレスを入力する

1) B列にスタッフ全員のメールアドレスを入力したいとします。社内IT部門では、スタッフの名前と姓を会社のドメイン名と組み合わせて、メールアドレスを生成するルールになっています。

2) フラッシュフィルを使うには、まずセルB5に期待する結果の見本を1つ入力します。フラッシュフィルはこの入力をテンプレートとして認識し、残りの列を自動的に埋めてくれます。ここでは次のように入力します。

admin@wsxdn.com

入力後、Ctrl + Enterキーを押します。

3) Ctrl + Enterを押すと、Excelが自動的にテキストをハイパーリンク形式に変換します。

4) 次に、メールアドレスを入力したい範囲B5:B15全体を選択します。その状態で、フラッシュフィルのショートカットキーであるCtrl + Eを押すか、または「データ」タブにある「フラッシュフィル」をクリックします。

5) 選択範囲の残りのセルに、必要なメールアドレスが一括で入力されます。

TEXT関数を使ったメールアドレスの作成

古いバージョンのExcelをお使いでも諦める必要はありません。TEXT関数を組み合わせれば、同じことが実現できます。ただし、このケースではフラッシュフィルよりも少し手順が複雑になります。

1) まずA列のテキスト内容を確認し、どのテキスト関数を使えばよいかを判断します。

2) データは「名前＋半角スペース＋姓」という構成になっていることがわかります。

3) それでは、この数式をゼロから組み立てていきましょう。

最初に、SEARCH関数を使ってスペースの位置を調べます。セルB5に次のように入力し、Ctrl + Enterキーを押します。

=SEARCH(" ", A5, 1)

4) 結果として「5」が返されました。つまりEmma Smithの場合、スペースは5番目の文字ということです。数式をダブルクリックして下方向へコピーすると、他のスタッフのスペース位置も確認できます。たとえばNicole Robertsの場合、スペースは7番目の文字にあります。

5) 次にLEFT関数を使って名前（ファーストネーム）を抽出します。スペースの位置がわかれば、その左側の文字をすべて取り出すことで名前だけを取得できます。

LEFT関数には「対象のテキスト文字列」と「左から抽出する文字数」を指定します。テキスト文字列にはセルA5を指定し、抽出する文字数はSEARCH関数の結果から1を引いて求めます。

=LEFT(A5, SEARCH(" ", A5, 1) - 1)

1を引いているのは、スペース自体を結果に含めないようにするためです。

6) Ctrl + Enterを押してから数式をダブルクリックして下方向へコピーすると、全員の名前が抽出されます。

7) Ctrl + Cを2回押してクリップボードを開き、この数式をコピーしておきます。

8) これで数式が保存できました。B列の内容をクリアして、今度は姓の抽出に取り掛かります。

9) スペースの位置はすでにわかっています。名前の抽出にLEFT関数を使ったので、姓の抽出にはRIGHT関数が使えます。姓を抽出するための数式を組み立てていきましょう。

10) RIGHT関数には「右から抽出する文字数」を指定する必要があります。スペースの位置はSEARCH関数で判明しているので、LEN関数で文字列全体の文字数を求め、そこからSEARCH関数の結果（スペースまでの文字数）を引けば、スペース以降の残り文字数が算出できます。

=LEN(A5) - SEARCH(" ", A5, 1)

この数式で、スペースの後ろに残っている文字数がわかります。

11) あとは、この数式とRIGHT関数を組み合わせれば、右端からスペースまでの文字（＝姓）を抽出できます。

次の数式を入力します。

=RIGHT(A5, LEN(A5) - SEARCH(" ", A5, 1))

13) 数式をクリップボードにコピーしたら、セルの内容をクリアします。

14) 各ステップを分解してロジックを確認し、それぞれのパーツをクリップボードに保存してきたので、ここで全部を1つの大きな数式にまとめます。

B列をクリアしたら、セルB5に次の数式を入力します。

=LEFT(A5, SEARCH(" ", A5, 1) - 1)&RIGHT(A5, LEN(A5) - SEARCH(" ", A5, 1))&"@mycompany.com"

15) 数式をダブルクリックして下方向へコピーすれば、列の残りのセルにもメールアドレスが一括で入力されます。

読者から寄せられた、もっと簡単な数式の提案

同じ結果を、はるかにシンプルな数式で実現できるという貴重な提案をいただきました。

Rahul Singhさんからは、CONCATENATE関数とSUBSTITUTE関数を組み合わせた次の数式をご提案いただきました。セルB5に入力します。

=CONCATENATE(SUBSTITUTE(A5," ",""),"@mycompany.com")

Karthikさんからは、記事内の最初のテキスト数式と同じ結果が得られる、こちらも非常にシンプルな数式をご提案いただきました。

=SUBSTITUTE(A5," ","")&"@mycompany.com"

SUBSTITUTE関数でスペースを削除してしまうという発想ですね。氏名の間にスペースが1つだけ入っているデータであれば、これらの数式の方が格段に手軽です。

まとめ

フラッシュフィルは、Microsoft Excel 2013以降で使える便利な新機能です。オートフィルの強化版とも言える機能で、作業時間を大幅に短縮できます。一方、古いバージョンのExcelを使っている方や、フラッシュフィルがうまく機能しないケースでは、TEXT関数の組み合わせが頼りになります。数式の作成はやや複雑ですが、組み立て方さえ理解すれば、どんなに複雑なデータ変換にも対応できます。皆さんはデータの抽出や整形に、フラッシュフィルとTEXT関数のどちらを使いたいですか？　その理由もぜひコメントでお聞かせください。

関連記事

• Excelでフラッシュフィルを無効化する方法（2つの簡単な手順）
• 【解決済み】Excelでフラッシュフィルが動作しないときの対処法（5つの原因と解決策）
• Excelでフラッシュフィルがパターンを認識しない（4つの原因と修正方法）