全角と半角を別々に抽出するにはフラッシュフィルで出来るが、氏名とメールアドレスは正しく抽出できない場合がある。上手く抽出できないときは、全角/半角の文字数を求めて、その文字数を基準に抽出しよう。
その他の基準で分割抽出
LEFT関数、RIGHT関数、LENB関数、LEN関数
例題1|テキストから全角の「氏名」を抽出する
- 「氏名」を求めるセル(D3セル)を選択し、「=LEFT(」と入力する。
- [文字列]…抽出する「氏名」のセル(A3セル)を選択する。
[文字数]…抽出する文字数を求める数式「LENB(A3)-LEN(A3)」を入力して、「Enter」キーで数式を確定する。 - 数式を必要なだけ複写する。
【数式】=LEFT(A3,LENB(A3)-LEN(A3))
LEFT、LENB、LEN
数式の解説
「LENB(A3)-LEN(A3)」の数式は、全角の文字数を求める。求められた全角の文字数をLEFT関数の引数の[文字数]に指定することで、「木村洋子aaa@xxxxx.ne.jp」から全角の「木村洋子」が抽出される。
Excelデータダウンロード
以下のリンクをクリックするか右クリックし、Excelデータをダウンロードください
Excel-sample1.xlsx
例題2|
- 「メールアドレス」を求めるセル(E3セル)を選択し、「=LEFT(」と入力する。
- [文字列]…抽出する「氏名」のセル(A3セル)を選択する。
- [文字数]…抽出する文字数を求める数式「LEN(A3)*2-LENB(A3)」を入力して、「Enter」キーで数式を確定する。
- 数式を必要なだけ複写する。
【数式】=RIGHT(A3,LEN(A3)*2-LENB(A3))
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)))
HYPERLINK、RIGHT、LEN、LENB
Excelデータダウンロード
以下のリンクをクリックするか右クリックし、Excelデータをダウンロードください
Excel-application1.xlsx
コメント