1つのセルに複数の情報が入ったデータから必要な部分を抜き出す
Webフォームや外部システムからデータを取り込むと、名前やメールアドレス、電話番号など、複数の情報が1つのセルにまとめて入っていることがあります。
カンマなど決まった文字で区切られていれば簡単に分割できますが、複数の情報が1つのセルにまとまっていると、単純に列を分けるだけでは必要な部分を取り出せないこともあります。
このような場合は、「メールアドレス:」「電話番号:」などの共通する文字を目印にすると、MID関数やFIND関数を使って必要な部分だけを抜き出せます。
今回は、Webフォームから取り込んだデータを例に、メールアドレスと電話番号を取り出す方法を、練習用ファイルを使って一緒にやってみましょう。
カンマなどで区切られているデータは簡単に分割できる
練習用ファイルの「カンマ区切り」シートA列に「名前・電話番号・メールアドレス」がカンマで区切られたデータが入っています。
このように共通する区切り文字がある場合は、Excelの［区切り位置］を使って簡単に分割できます。
一方、今回扱うのは、単純な区切り位置では分割しにくいデータです。
1つのセルに複数の情報が入ったデータを確認する
練習用ファイルのA2セルには、Webフォームから取り込んだことを想定したデータが入力されています。
このデータには「名前」「メールアドレス」「電話番号」「内容」など複数の情報が含まれていますが、すべて1つのセルに入っています。
1セル1情報が守られていない、一番面倒なやつね。
今回は、この中からメールアドレスと電話番号だけを抜き出して、それぞれ別のセルに表示させていきましょう。
このようなデータを整理するときは、まずすべてのデータに共通しているKey（目印）を探すのがポイントです。
今回のデータでは、「お名前:」「メールアドレス:」「電話番号:」「内容:」という項目名が共通して使われていますね。
セル内のデータは
メールアドレス 「メールアドレス:」
↓
メールアドレス
↓
「電話番号:」
という並びになっています。
つまり、「メールアドレス:」と「電話番号:」をKeyにしてそれぞれの位置を調べれば、その間にあるメールアドレスだけを抜き出せます。
共通するKeyが見つからない場合は、元データに共通する目印を作れないか考えるのも、Excelでデータを整理するときの基本です。
必要な情報を抜き出す
必要な情報を抜き出すには、MID・FIND・LEN関数を組み合わせます。今回はメールアドレスを例に、数式を順番に組み立てていきましょう。
FIND関数でKeyとなる文字の位置を調べ、LEN関数でKeyの文字数を取得します。取得した位置と文字数をMID関数に組み込むことで、2つのKeyの間にある必要な情報だけを抜き出せます。
今回はメールアドレスを例にすると
「メールアドレス:」の位置を調べる。
「メールアドレス:」の文字数を加えて、抜き出しを始める位置を求める
「電話番号:」の位置を調べる
2つの位置から、抜き出す文字数を求める
メールアドレスを抜き出す
という流れです。
必要な情報を抜き出す数式を作成する
それでは、実際に数式を入力しながら一緒に確認していきましょう。
メールアドレスを抜き出すため、B2セルにMID関数を入力します。
MID関数の第1引数は「文字列」なので、元のデータが入っているA2セルを指定します。
MID関数の第1引数を指定したらカンマ , を打って第2引数に進みます。
MID関数の第2引数は「開始位置」なので本来は数値を指定しますが、今回は抜き出しを始める位置が固定ではないため、抜き出し位置を検索するために、ここでFIND関数を入れます。
FIND関数の第1引数は「検索文字列」なので、今回は「”メールアドレス:”」とします。
文字列なので、ダブルクォーテーション “ で挟むのを忘れないよう注意しましょう
FIND関数の第1引数を入れたらカンマ , を打って第2引数に進みます。
FIND関数の第2引数は「対象」なので、元データの入っているA2と入力します。
FIND関数の第2引数を入力したらカッコを閉じましょう。
ここまでで、「メールアドレス:」の位置を調べるFIND関数が完成しました。
ただ、この位置から抜き出すと「メールアドレス:」まで含まれてしまいます。
そこでLEN関数を使って「メールアドレス:」の文字数を調べ、FIND関数で求めた位置に足します。
FIND関数を閉じたカッコの後に ＋ を入れてLEN関数を入れます。
LEN関数は文字数をカウントしてくれる関数なので、引数に「”メールアドレス:”」と入れます。
「メールアドレス:」は8文字なので「+8」としても同じ結果になりますが、今回は数式を見たときに何を足しているのか分かりやすいようにLEN関数を使っています。
また、別の項目を抜き出す場合も、LEN関数内の文字列を変更するだけなので、使い回す時にも変更箇所が分かりやすく、LEN関数の使用がおすすめです。
LEN関数の引数を入れたらカッコを閉じます。
ここまでがMID関数の第2引数、抜き出しの「開始位置」になります。
MID関数の第2引数を指定できたので、カンマ , を打って第3引数に進みましょう。
MID関数の第3引数は「文字数」なので、本来は数値を入力しますが、今回は抜き出す文字数がデータによって変わるため、次のKeyとなる「電話番号:」の位置をFIND関数で調べます。
FIND関数の第1引数に、今度は「”電話番号:”」と入れたらカンマ , を打って第2引数は同じくA2としカッコを閉じましょう。
これで「電話番号:」の位置が分かったので、ここから先ほど求めた抜き出しの開始位置を引いて、メールアドレスの文字数を求めます。
開始位置の出し方は先ほどの
FIND("メールアドレス:",A2)+LEN("メールアドレス:")でしたね。
これで「電話番号:」の位置から抜き出しの開始位置までの文字数を求められました。
ここまでがMID関数の第3引数、抜き出す「文字数」になります。
最後にMID関数のカッコを閉じて最後に Enterを押して完成です。
A2セルからメールアドレスだけが抜き取られました。
電話番号も同様に、Keyとなる文字列を変更してあげると、電話番号だけ抜き取ることが可能です。
今回使用した関数の基本的な使い方
今回使用したMID・FIND・LEN関数の基本的な使い方については、こちらの記事で詳しく紹介しています。
まとめ
1つのセルに複数の情報が入っている場合でも、すべてのデータに共通するKey（目印）があれば、必要な部分だけを抜き出すことができます。
今回は「メールアドレス:」と「電話番号:」をKeyにして位置を調べ、MID・FIND・LEN関数を組み合わせてメールアドレスを抜き出しました。
同じ考え方でKeyとなる文字列を変更すれば、ほかの情報を抜き出す場合にも応用できます。
Webフォームや外部システムから取り込んだデータを整理するときは、まず共通するKeyとして使える文字列がないか探してみるのがポイントです。
共通するKeyがない場合は、元データに目印となる文字列を作れないか考えながら、実際のデータに合わせて活用してみてください。
