Excelで大量の住所データを整理する場合、住所から「都道府県」「番地までの住所」「建物名や施設名」などを分割したいことがあります。しかし、住所には「東京都」「大阪府」「北海道」などの違いや、建物名がない一戸建て住所などさまざまな形式があるため、単純なMID関数やFIND関数だけではエラーになることがあります。
この記事では、数千件の住所データでもオートフィルで処理できるように、都道府県の種類や建物名の有無に対応したExcel関数を使った住所分割方法を解説します。
住所データを3つに分割するときの考え方
住所を「県」「県以降の番地まで」「建物名」に分ける場合、ポイントになるのは建物名との区切りに使われている全角スペースです。
例えば、以下のような住所の場合を考えます。
愛知県名古屋市中村区名駅四丁目7番1号 ミッドランドスクエア商業棟5階
この場合、全角スペースより前が番地までの住所、後ろが建物名になります。
一方で、一戸建てなど建物名がない住所ではスペースが存在しないため、FIND関数だけを使うとエラーになります。そのため、IFERROR関数などで条件分岐を入れることが重要です。
県名を除いた住所を取得する関数
都道府県部分を除いた住所を取得するには、「県」という文字だけでなく、「都」「道」「府」にも対応する必要があります。
以下の関数を利用すると、都道府県の長さの違いに対応できます。
=MID(Sheet1!A1,IFERROR(FIND("都",Sheet1!A1),IFERROR(FIND("道",Sheet1!A1),IFERROR(FIND("府",Sheet1!A1),FIND("県",Sheet1!A1))))+1,LEN(Sheet1!A1))
ただし、この式は住所全体を取得するため、番地までと建物名をさらに分ける場合は次の方法を使います。
番地までの住所を抽出する関数
県以降から、建物名との区切りである空白までを取得する場合は、空白がない住所にも対応させる必要があります。
以下の式では、空白がある場合は空白まで、空白がない場合は住所末尾まで取得します。
=MID(Sheet1!A1,FIND("@",SUBSTITUTE(LEFT(Sheet1!A1,FIND("@",SUBSTITUTE(Sheet1!A1,"都","@",1)&SUBSTITUTE(Sheet1!A1,"道","@",1)&SUBSTITUTE(Sheet1!A1,"府","@",1)&SUBSTITUTE(Sheet1!A1,"県","@",1)),1)," ","@",1))+1,IFERROR(FIND(" ",Sheet1!A1)-FIND("県",Sheet1!A1)-1,LEN(Sheet1!A1)))
ただし、Excelのバージョンによっては複雑になるため、より実用的にはLET関数を利用する方法がおすすめです。
Microsoft 365やExcel 2021以降の場合
新しいExcelではLET関数を使うことで、読みやすく管理しやすい式にできます。
=LET(住所,Sheet1!A1,空白,FIND(" ",住所&" "),MID(住所,SEARCH("都",住所)+1,空白-SEARCH("都",住所)-1))
LET関数を使うと、同じ住所セルを何度も参照する必要がなくなり、数千件のデータ処理でも管理しやすくなります。
建物名や施設名だけを抽出する関数
建物名は全角スペース以降を取得すればよいため、IFERROR関数を組み合わせることで空白なし住所にも対応できます。
以下の式を使用します。
=IFERROR(MID(Sheet1!A1,FIND(" ",Sheet1!A1)+1,LEN(Sheet1!A1)),"")
この式では、全角スペースがある場合だけ建物名を取得し、ない場合は空欄になります。
例えば「愛知県名古屋市中村区名駅四丁目7番1号 ミッドランドスクエア商業棟5階」の場合は「ミッドランドスクエア商業棟5階」が取得されます。
Excel 365ならTEXTBEFORE・TEXTAFTERも便利
Microsoft 365など新しいExcelを利用している場合は、TEXTBEFORE関数とTEXTAFTER関数を使うと、住所分割がより簡単になります。
建物名を取得する場合は以下の式になります。
=IFERROR(TEXTAFTER(A1," "),"")
番地までの住所を取得する場合は以下の式です。
=TEXTBEFORE(A1&" "," ")
これらの関数は従来のFIND関数やMID関数よりも直感的で、大量データの加工に向いています。
数千件の住所データを処理するときの注意点
大量の住所データを処理する場合、最初に数十件だけで関数の結果を確認することが重要です。
特に注意する住所形式は以下のようなものです。
- 東京都・北海道など都道府県名の文字数が違う住所
- 建物名が付いている住所
- 一戸建てで空白がない住所
- 番地表記が特殊な住所
住所データは入力元によって表記揺れが発生するため、関数だけでは完全な住所解析が難しい場合があります。必要に応じてPower Queryなどのデータ加工機能を利用する方法もあります。
まとめ
Excelで住所から県名、番地までの住所、建物名を抽出する場合は、単純にFIND関数だけを使うと「都道府県の違い」や「空白がない住所」でエラーになります。
IFERROR関数で条件分岐を追加したり、Microsoft 365ならTEXTBEFORE・TEXTAFTER関数を利用したりすることで、多くの住所形式に対応できます。
数千件の住所データを処理する場合は、実際のデータ形式を確認したうえで関数を設定し、オートフィルで一括処理すると効率的に整理できます。
コメント