s005｜テキストから全角と半角を別々に抽出する【LEFT関数、RIGHT関数、LENB関数、LEN関数】｜Excel関数によるデータ抽出方法

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

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

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

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

数式の解説

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

Excelデータダウンロード

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

例題２｜

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

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

数式の解説

「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  

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

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

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

Excelデータダウンロード

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