S004｜複数の同じ区切り文字のうち、指定する番目の区切り文字で分割する【TEXTAFTER関数、LEFT関数、FIND関数、SUBSTITUTE関数】｜Excel関数によるデータ抽出方法

TEXTBEFORE、TEXTAFTER関数は、同じ区切り文字が複数ある場合でも、区切り文字がある番目を指定して抽出することが出来る。Excel2021/2019/2016ならLEFT、RIGHT関数の引数［文字数］に番目が指定できるFIND+SUBSTITUTE関数を組み合わせて抽出しよう。

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

1. 「氏名」を求めるセル（F3セル）を選択し、「=TEXTBEFORE(」と入力する。
2. ［文字列］…抽出する「氏名」のセル範囲を選択する。
3. ［区切り文字］…半角スペース「” “」を入力する。
4. ［区切り位置］…2番目なので「2」と入力する。
5. ［一致モード］…省略。
6. ［末尾一致］…省略。
7. ［規定値］…省略して、「Enter」キーで数式を確定する。

【数式】=TEXTBEFORE(A3:A5, ” “, 2)

数式の解説

「=TEXTBEFORE(A3:A5, “ ”,2)」の数式は、A3セル～A5セルの2番目の半角スペース「” “」より前にある「氏名」「生年月日」を抽出する。

Excelデータダウンロード

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

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

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

【数式】=LEFT(A3,FIND(“●”,SUBSTITUTE(A3,” “,”●”,2))-1)

数式の解説

「FIND(“●”,SUBSTITUTE(A3,“ ”,“●”,2))-1」の数式は、半角スペース「” “」を「●」に置き換えて2番目にある「●」の位置より1文字前までの位置を求める。つまり、2つ目の「半角スペース」より1文字前までの位置となり、「=LEFT(A3,FIND(”●“,SUBSTITUTE(A3,” “,”●“,2))-1)」の数式を作成することで、2つ目の半角スペース「” “」までの「氏名」「生年月日」が抽出される。

【新方式】スピル で表現！

今まで ： =LEFT(A3,FIND(“●”,SUBSTITUTE(A3,” “,”●”,2))-1)
　　　　　　↓
スピル ： =LEFT(A3:A5,FIND(“●”,SUBSTITUTE(A3:A5,” “,”●”,2))-1)

Excelデータダウンロード

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

応用１｜2番目や2番目と6番目だけの区切り文字を基準に別々のセルに抽出する

●番目の区切り文字までをすべて抽出するのではなく、2番目や2番目と6番目だけの区切り文字を基準に別々のセルに抽出したいときは、TEXTSPLIT関数を配列から列を取り出すことができるCHOOSECOLS関数に組み合わせて数式を作成しましょう

○2番目の半角スペース「” “」を基準に「氏名」を抽出する

【数式】=CHOOSECOLS(TEXTSPLIT(A1,” “),2)

Excelデータダウンロード

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

応用２｜2番目や2番目と6番目だけの区切り文字を基準に別々のセルに抽出する

○2番目と6番目の半角スペース「” “」を基準に「氏名」「メールアドレス」を抽出する

【数式】=CHOOSECOLS(TEXTSPLIT(A1,” “),{2,4})

Excelデータダウンロード

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