住所を都道府県・市町村・番地に分割するには、都道府県をLEFT+MID関数、番地を半角を抽出する数式で抽出した後、都道府県と番地の文字数以外の残りの文字数を使ってMID+LEN関数で市区町村を抽出する。
その他の基準で分割抽出
LEFT関数、MID関数、LEN関数、RIGHT関数、LENB関数、ASC関数
例題1|住所の「都道府県」を抽出する
- 「都道府県」を求めるセル(B3セル)を選択し、「=LEFT(」と入力する。
- [文字列]…「住所」のセルを選択する。
- [文字数]…「都道府県」の文字数を求める数式「(MID(A3, 4, 1)=“県”)+3」を入力して、「Enter」キーで数式を確定する。
- 数式を必要な分だけ複写する。
【数式】=LEFT(A3, (MID(A3, 4, 1)=”県”)+3)
LEFT、MID
数式の解説
「(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
例題2|住所の「番地」を抽出する
- 「番地」を求めるセル(D3セル)を選択し、「=RIGHT(」と入力する。
- [文字列]…「住所」のセルを選択する。
- [文字数]…「番地」の文字数を求める数式「LEN(A3)*2-LENB(ASC(A3))」を入力して、「Enter」キーで数式を確定する。
- 数式を必要な分だけ複写する。
【数式】=RIGHT(A3, LEN(A3)*2-LENB(ASC(A3)))
RIGHT、LEN、LENB、ASC
数式の解説
「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
例題3|住所の「市区町村」を抽出する
- 「市区町村」を求めるセル(C3セル)を選択し、「=MID(」と入力する。
- [文字列]…「住所」のセルを選択する。
- [開始位置]…「市区町村」の最初の位置を求める数式「LEN(B3)+1」を入力する。
- [文字数]…「市区町村」の文字数を求める数式「LEN(A3)-LEN(B3&D3)」を入力して、「Enter」キーで数式を確定する。
- 数式を必要な分だけ複写する。
【数式】=MID(A3, LEN(B3)+1, LEN(A3)-LEN(B3&D3))
MID、LEN
数式の解説
「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
応用1|
住所の番地が「●丁目」や「●番地」など文字付きの場合は、手順のように半角の文字数をもとに抽出できないので、RIGHT+LEN+MIN+FIND+ASC関数の数式を作成して抽出する。
- 「番地」を求めるセル(D3セル)を選択し、「=RIGHT(」と入力する。
- 「住所」のセル、「番地」の文字数を求める数式「ASC(LEN(A3))-MIN(FIND({0,1,2,3,4,5,6,7,8,9}, ASC(A3)&1234567890)-1)」を入力して、「Enter」キーで数式を確定する。
- 数式を必要なだけ複写すると、それぞれの「住所」から「番地」が抽出される。
【数式】=RIGHT(A3, ASC(LEN(A3))-MIN(FIND({0,1,2,3,4,5,6,7,8,9}, ASC(A3)&1234567890)-1))
RIGHT、ASC、LEN、MIN、FIND、ASC
Excelデータダウンロード
以下のリンクをクリックするか右クリックし、Excelデータをダウンロードください
Excel-application1.xlsx
コメント