Excel|XLOOKUPの複数条件によるデータ抽出 

s005|テキストから全角と半角を別々に抽出する|Excelのデータ抽出方法

全角と半角を別々に抽出するにはフラッシュフィルで出来るが、氏名とメールアドレスは正しく抽出できない場合がある。上手く抽出できないときは、全角/半角の文字数を求めて、その文字数を基準に抽出しよう。

目的

その他の基準で分割抽出

使用する関数

LEFT関数、RIGHT関数、LENB関数、LEN関数

目次

例題1|テキストから全角の「氏名」を抽出する

  1. 「氏名」を求めるセル(D3セル)を選択し、「=LEFT(」と入力する。
  2. [文字列]…抽出する「氏名」のセル(A3セル)を選択する。
    [文字数]…抽出する文字数を求める数式「LENB(A3)-LEN(A3)」を入力して、「Enter」キーで数式を確定する。
  3. 数式を必要なだけ複写する。

【数式】=LEFT(A3,LENB(A3)-LEN(A3))

使用するExcel関数

LEFT、LENB、LEN

数式の解説

「LENB(A3)-LEN(A3)」の数式は、全角の文字数を求める。求められた全角の文字数をLEFT関数の引数の[文字数]に指定することで、「木村洋子aaa@xxxxx.ne.jp」から全角の「木村洋子」が抽出される。

Excelデータダウンロード

以下のリンクをクリックするか右クリックし、Excelデータをダウンロードください
Excel-sample1.xlsx

例題2|

  1. 「メールアドレス」を求めるセル(E3セル)を選択し、「=LEFT(」と入力する。
  2. [文字列]…抽出する「氏名」のセル(A3セル)を選択する。
  3. [文字数]…抽出する文字数を求める数式「LEN(A3)*2-LENB(A3)」を入力して、「Enter」キーで数式を確定する。
  4. 数式を必要なだけ複写する。

【数式】=RIGHT(A3,LEN(A3)*2-LENB(A3))

使用するExcel関数

RIGHT、LEN、LENB

数式の解説

「LEN(A3)*2-LENB(A3)」の数式は、半角の文字数を求める。求められた半角の文字数をRIGHT関数の引数の[文字数]に指定することで、「木村洋子aaa@xxxxx.ne.jp」から半角の「 aaa@xxxxx.ne.jp」が抽出される。

[新方式]スピル で表現!

今まで : =RIGHT(A3,LEN(A3)*2-LENB(A3))
      ↓
スピル : =RIGHT(A3:A5,LEN(A3:A5)*2-LENB(A3:A5))

Excelデータダウンロード

以下のリンクをクリックするか右クリックし、Excelデータをダウンロードください
Excel-sample2.xlsx

応用1| 「2つ目」の「半角スペース」までの「氏名と生年月日」を抽出する

抽出したメールアドレスをクリックしたらメール作成画面が開けるようにするには、抽出したメールアドレスにHYPERLINK関数でハイパーリンクを付けて、以下のように数式を作成する。なお、必ず抽出したメールアドレスの前には❶「mailto:」を結合しましょう

【数式】=HYPERLINK(“mailto:”&RIGHT(A3,LEN(A3)*2-LENB(A3)))

使用するExcel関数

HYPERLINK、RIGHT、LEN、LENB

Excelデータダウンロード

以下のリンクをクリックするか右クリックし、Excelデータをダウンロードください
Excel-application1.xlsx

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

この記事を書いた人

コメント

コメントする

目次