住所から都道府県名を分離する数式
HOME > PCワンポイント活用術 > エクセル(Excel)の活用術 > コピペで使える数式【住所から都道府県名を分離する数式】
Excel Version: Excel2013 Excel2010 Excel2007 Excel2003 Excel2002 Excel2000 Excel97 Excel95
Update:2012-8-29
 この数式は、都道府県名の付いた住所から都道府県名を分離、都道府県名と市区町村以下の住所を得る数式です。

● 数式
 >下表中の式、“=LEFT(TRIM(A2),IF(LEFT(……”及び“=RIGHT(TRIM(A2),LEN(TRIM(A2))-LEN(B2))”の2つをコピペしてお使いください。
 >“A2”は都道府県名付の住所が入っているセル番号です。実際に住所が入っているセル番号に置き換えてご使用ください。
 また、“B2”は分離された都道府県名が入るセル番号です。実際に都道府県名が入るセル番号に置き換えてご使用ください。
 >住所の前後にスペース(空白文字)が入っている場合は、削除されます。
 >都道府県名が付いていない住所だった場合は、都道府県名は空白、市区町村名以下はそのまま表示されます。

A B C
1 都道府県名付住所 都道府県名 市区町村以下の住所
2 埼玉県さいたま市岩槻区加倉4-31-18 埼玉県 さいたま市岩槻区加倉4-31-18
  (上セルの式)→ =LEFT(TRIM(A2),IF(LEFT(TRIM(A2),3)="東京都",3,0)+IF(LEFT(TRIM(A2),3)="北海道",3,0)+IF(OR(LEFT(TRIM(A2),3)="京都府",LEFT(TRIM(A2),3)="大阪府"),3,0)+IF(ISERROR(FIND("県",TRIM(A2),3)),0,IF(OR(LEFT(TRIM(A2),4)="神奈川県",LEFT(TRIM(A2),4)="和歌山県",LEFT(TRIM(A2),4)="鹿児島県"),4,IF(MID(TRIM(A2),2,3)="諸県郡",0,3)))) =RIGHT(TRIM(A2),LEN(TRIM(A2))-LEN(B2))

又は

=MID(TRIM(A2),LEN(B2)+1,LEN(TRIM(A2)))

↑ページトップへ

● 数式を組み立てられるようになりたい人のための解説

  B B列の式 解説
2 埼玉県 =LEFT(A2,3) 1.都道府県名を取り出す式を作る
(1)基本はこの式。住所(A2)からLEFT関数を使って 3文字を取り出す。
つまり、都道府県名の有無からその文字数を決めることがポイントになります。
3 0 =IF(LEFT(A2,3)="東京都",3,0) (2)最初の3文字が“東京都”か判定し、“東京都”の場合は3(字)、違う場合は0(字)とする。
4 0 =IF(LEFT(A2,3)="北海道",3,0) (3)最初の3文字が“北海道”か判定し、“北海道”の場合は3(字)、違う場合は0(字)とする。
5 0 =IF(OR(LEFT(A2,3)="京都府",LEFT(A2,3)="大阪府"),3,0) (4)最初の3文字が“京都府”か“大阪府”かを判定し、どちらかの場合は3(字)、違う場合は0(字)とする。
6 3 =IF(OR(LEFT(A2,4)="神奈川県",LEFT(A2,4)="和歌山県",LEFT(A2,4)="鹿児島県"),4,3)
(5-1)県の検出はちょっと手間がかかります。
まず、県名が4文字のチェックを考えます。4文字の県名は“神奈川県”“和歌山県”“鹿児島県”の3県です。該当する場合は4(字)、違う場合は3(字)です。
6 3 =IF(OR(LEFT(A2,4)="神奈川県",LEFT(A2,4)="和歌山県",LEFT(A2,4)="鹿児島県"),4,IF(OR(MID(A2,2,3)="諸県郡",FIND("県",A2,3)<>3),0,3))
(5-2)最初の3文字目が“県”になるケースの判定を追加します。
宮崎県東諸県郡・北諸県郡・西諸県郡で都道府県名が付いていない場合に3字目が“県”になりますので特別にチェックして 3(字)が確定、違う場合は0(字)とする。
6 3 =IF(ISERROR(FIND("県",A2,3)),0,IF(OR(LEFT(A2,4)="神奈川県",LEFT(A2,4)="和歌山県",LEFT(A2,4)="鹿児島県"),4,IF(OR(MID(A2,2,3)="諸県郡",FIND("県",A2,3)<>3),0,3))) (5-3)“県”の文字が含まれない場合にエラーが発生するので、“県”無しのエラーチェックを最初に追加します。=0(字)
7 埼玉県 =LEFT(A2,(B3+B4+B5+B6)) (6)都道府県名の文字数チェックでは、該当しない場合はすべて0(ゼロ)になるので、4つのパターンの文字数を合計して文字数が確定します。
確定した文字数の式 (B3+B4+B5+B6) でB2セルの式の文字数(3)を置き換えると、都道府県すべてに対応できるようになります。
8 埼玉県 =LEFT(A2,IF(LEFT(A2,3)="東京都",3,0)+IF(LEFT(A2,3)="北海道",3,0)+IF(OR(LEFT(A2,3)="京都府",LEFT(A2,3)="大阪府"),3,0)+IF(ISERROR(FIND("県",A2,3)),0,IF(OR(LEFT(A2,4)="神奈川県",LEFT(A2,4)="和歌山県",LEFT(A2,4)="鹿児島県"),4,IF(OR(MID(A2,2,3)="諸県郡",FIND("県",A2,3)<>3),0,3)))) (7)(6)の式のB3+B4+B5+B6セル番号をそれぞれ(2)(3)(4)(5-3)の式で置き換えて、一本の式にまとめます。
9 埼玉県 =LEFT(TRIM(A2),IF(LEFT(TRIM(A2),3)="東京都",3,0)+IF(LEFT(TRIM(A2),3)="北海道",3,0)+IF(OR(LEFT(TRIM(A2),3)="京都府",LEFT(TRIM(A2),3)="大阪府"),3,0)+IF(ISERROR(FIND("県",TRIM(A2),3)),0,IF(OR(LEFT(TRIM(A2),4)="神奈川県",LEFT(TRIM(A2),4)="和歌山県",LEFT(TRIM(A2),4)="鹿児島県"),4,IF(OR(MID(TRIM(A2),2,3)="諸県郡",FIND("県",TRIM(A2),3)<>3),0,3)))) (8)(7)の式では都道府県名の前にスペース(空白文字)が入っていると、正しく都道府県名を検出できないので、A2セルのデータから空白をTRIM関数を使って削除してから使うようにします。

  C C列の式 解説
2 さいたま市岩槻区加倉4-31-18 =RIGHT(A2,17) 2.市区町村名以下を取り出す式を作る
(9)基本はこの式。住所(A2)からRIGHT関数を使って都道府県名を除いた右側の文字数分(17文字)を取り出す。
取り出す文字数を決めるのがポイントになります。
3 20 =LEN(A2) (10)LEN関数を使うと文字数を数えられる(A2セルの住所は全部で20字)。
4 さいたま市岩槻区加倉4-31-18 =RIGHT(A2,LEN(A2)-LEN(B2)) (11)住所(A2)の文字数から都道府県名(B2)の文字数を引き算すると取り出す文字数が得られるので、LEN関数でそれぞれ文字数を検出して計算する式に変更する。
5 さいたま市岩槻区加倉4-31-18 =RIGHT(TRIM(A2),LEN(TRIM(A2))-LEN(B2)) (12)(11)の式では都道府県名の前にスペース(空白文字)が入っていると、正しく都道府県名を検出できず、結果として市区町村以下の住所も正しく分離できなくなるので、A2セルのデータから空白をTRIM関数を使って削除してから使うようにします。

◆住所の都道府県名と市区町村以下を分離するのではなく、都道府県名を削除したい場合は『住所から都道府県名を削除する数式』をご覧ください。
 『コピペで使える数式:住所から都道府県名を削除する数式』(click here)

◆今回の数式を作るにあたって裏付け調査を行いました。
 ブログに経緯を書きましたので、興味のある方はご覧ください。
 『住所から都道府県名を削除する数式』の更新で調べたこと(click here)

◆「住所から都道府県名を分離する数式」についてのご意見・ご感想は、『うふい's コミュニティ(掲示板)』に書き込んでください。

↑ページトップへ




↑ページトップへ