s006｜テキストから住所を都道府県・市町村に分割する【LEFT関数、MID関数、LEN関数、RIGHT関数、LENB関数、ASC関数】｜Excel関数によるデータ抽出方法

住所を都道府県・市町村・番地に分割するには、都道府県をLEFT+MID関数、番地を半角を抽出する数式で抽出した後、都道府県と番地の文字数以外の残りの文字数を使ってMID+LEN関数で市区町村を抽出する。

例題１｜住所の「都道府県」を抽出する

1. 「都道府県」を求めるセル（B3セル）を選択し、「=LEFT(」と入力する。
2. ［文字列］…「住所」のセルを選択する。
3. ［文字数］…「都道府県」の文字数を求める数式「(MID(A3, 4, 1)=“県”)+3」を入力して、「Enter」キーで数式を確定する。
4. 数式を必要な分だけ複写する。

【数式】=LEFT(A3, (MID(A3, 4, 1)=”県”)+3)

数式の解説

「(MID(A3, 4, 1)=“県”」の数式は、A3セルの「住所」の左端より4文字目から1文字取り出した文字列が「県」の場合は「TRUE(1)」、異なる場合は「FALSE(0)」を求める。つまり、「=LEFT(A3, (MID(A3, 4, 1)=“県”)+3)」の数式を作成すると、「県」を含む「住所」は4文字、含まない「住所」は3文字分、「住所」の左端から抽出される。結果、「住所」から「都道府県」が抽出される。

［新方式］スピル で表現！

今まで ： =LEFT(A3, (MID(A3, 4, 1)=”県”)+3)
　　　　　　↓
スピル ： =LEFT(A3:A5, (MID(A3:A5, 4, 1)=”県”)+3)

Excelデータダウンロード

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

例題２｜住所の「番地」を抽出する

1. 「番地」を求めるセル（D3セル）を選択し、「=RIGHT(」と入力する。
2. ［文字列］…「住所」のセルを選択する。
3. ［文字数］…「番地」の文字数を求める数式「LEN(A3)*2-LENB(ASC(A3))」を入力して、「Enter」キーで数式を確定する。
4. 数式を必要な分だけ複写する。
   
   【数式】=RIGHT(A3, LEN(A3)*2-LENB(ASC(A3)))

数式の解説

「LEN(A3)*2-LENB(ASC(A3))」の数式は、A3セルの「住所」から半角の文字数、つまり「番地」の文字数を求める。この文字数をRIGHT関数の引数の［文字数］に指定して、「=RIGHT(A3, LEN(A3)*2-LENB(ASC(A3)))」と数式を作成すると、「住所」の右端から「番地」が抽出される。

［新方式］ スピル で表現！

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

Excelデータダウンロード

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

例題３｜住所の「市区町村」を抽出する

1. 「市区町村」を求めるセル（C3セル）を選択し、「=MID(」と入力する。
2. ［文字列］…「住所」のセルを選択する。
3. ［開始位置］…「市区町村」の最初の位置を求める数式「LEN(B3)+1」を入力する。
4. ［文字数］…「市区町村」の文字数を求める数式「LEN(A3)-LEN(B3&D3)」を入力して、「Enter」キーで数式を確定する。
5. 数式を必要な分だけ複写する。

【数式】=MID(A3, LEN(B3)+1, LEN(A3)-LEN(B3&D3))

数式の解説

「LEN(B3)+1, LEN(A3)-LEN(B3&D3)」の数式は、A3セルの「住所」の「市区町村」の文字数を求める。この文字数をMID関数の引数の［文字数］に指定して、「=MID(A3, LEN(B3)+1, LEN(A3)-LEN(B3&D3))」と数式を作成すると、B3セルの「都道府県」の文字数+1の位置から「市区町村」の文字数分が抽出され、結果、「住所」から「市区町村」が抽出される。

［新方式］スピル で表現！

今まで ： =MID(A3, LEN(B3)+1, LEN(A3)-LEN(B3&D3))
　　　　　　↓
スピル ： =MID(A3:A5, LEN(B3:B5)+1, LEN(A3:A5)-LEN(B3:B5&D3:D5))

Excelデータダウンロード

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

応用１｜

住所の番地が「●丁目」や「●番地」など文字付きの場合は、手順のように半角の文字数をもとに抽出できないので、RIGHT+LEN+MIN+FIND+ASC関数の数式を作成して抽出する。

1. 「番地」を求めるセル（D3セル）を選択し、「=RIGHT(」と入力する。
2. 「住所」のセル、「番地」の文字数を求める数式「ASC(LEN(A3))-MIN(FIND({0,1,2,3,4,5,6,7,8,9}, ASC(A3)&1234567890)-1)」を入力して、「Enter」キーで数式を確定する。
3. 数式を必要なだけ複写すると、それぞれの「住所」から「番地」が抽出される。

【数式】=RIGHT(A3, ASC(LEN(A3))-MIN(FIND({0,1,2,3,4,5,6,7,8,9}, ASC(A3)&1234567890)-1))

Excelデータダウンロード

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