住所から都道府県名を削除する数式
HOME > PCワンポイント活用術 > エクセル(Excel)の活用術 > コピペで使える数式【住所から都道府県名を削除する数式】
Excel Version: Excel2013 Excel2010 Excel2007 Excel2003 Excel2002 Excel2000 Excel97 Excel95
Update:2012-8-20[1]
 この数式は、都道府県名の付いた住所から都道府県名を削除した住所を得る数式です。

● 数式
 >下表中の式、“=REPLACE(TRIM(A2),1,IF(LEFT……”をコピペしてお使いください。
 >“A2”は都道府県名付の住所が入っているセル番号です。実際に住所が入っているセル番号に置き換えてご使用ください。
 >住所の前後にスペース(空白文字)が入っている場合は、削除されます。
 >都道府県名が付いていない住所だった場合は、変化しません。

A B
1 都道府県名付住所 都道府県名無住所
2 埼玉県さいたま市岩槻区加倉4-31-18 さいたま市岩槻区加倉4-31-18
3 (B2セルの式)→ =REPLACE(TRIM(A2),1,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))),"")

↑ページトップへ

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

  B B列の式 解説
2 さいたま市岩槻区加倉4-31-18 =REPLACE(A2,1,3,"") 基本になる式はこれ。
A2セルの“埼玉県”(都道府県名) 3字を“無”("")で置き換える。
つまり、都道府県名の文字数を確定するのがポイントになる。
3 0 =IF(LEFT(A2,3)="東京都",3,0) (1)最初の3文字が“東京都”か判定し、“東京都”の場合は3(字)、違う場合は0(字)
4 0 =IF(LEFT(A2,3)="北海道",3,0) (2)最初の3文字が“北海道”か判定し、“北海道”の場合は3(字)、違う場合は0(字)
5 0 =IF(OR(LEFT(A2,3)="京都府",LEFT(A2,3)="大阪府"),3,0) (3)最初の3文字が“京都府”か“大阪府”かを判定し、どちらかの場合は3(字)、違う場合は0(字)
6 3 =IF(OR(LEFT(A2,4)="神奈川県",LEFT(A2,4)="和歌山県",LEFT(A2,4)="鹿児島県"),4,3)
(4-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))
(4-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))) (4-3)“県”じゃない場合にエラーが発生するので、“県”無しのエラーチェックを最初に追加します。=0(字)
7 さいたま市岩槻区加倉4-31-18 =REPLACE(A2,1,(B3+B4+B5+B6),"") (5)都道府県名の文字数チェックでは、該当しない場合はすべて0(ゼロ)になるので、4つのパターンの文字数を合計して文字数が確定します。
確定した文字数の式 (B3+B4+B5+B6) でB2セルの式の文字数(3)を置き換えると、都道府県すべてに対応できるようになります。
8 さいたま市岩槻区加倉4-31-18 =REPLACE(A2,1,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))),"") (6)(5)の式のB3+B4+B5+B6セル番号をそれぞれ(1)(2)(3)(4-3)の式で置き換えて、一本の式にまとめます。
9 さいたま市岩槻区加倉4-31-18 =REPLACE(TRIM(A2),1,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))),"") (7)(6)の式では都道府県名の前にスペース(空白文字)が入っていると、正しく都道府県名を検出できないので、A2セルのデータから空白をTRIM関数を使って削除してから使うようにします。

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

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

↑ページトップへ

● 旧掲載の数式について

 ◆旧掲載の数式では、都道府県名以外に“都”“道”“府”“県”の文字が入っていた場合、特に都道府県名が付かいない住所だった場合には、削除されるべきではない住所部分が除かれてしまうケースが生じてしまいます。
 例1:北海道寿都郡黒松内町 → 郡黒松内町 ・・・ “都”が先に判定されるため北海道が付いていても先頭〜都までが除かれる。
 例2:喜多方市岩月町大都000 → 000 ・・・ 県名が付いていなくても“都”が判定されるためほぼすべてが除かれる。
 例3:東京都西多摩郡檜原村三都郷 → 西多摩郡檜原村三都郷 ・・・ 東京都がついているため県名以外の“都”があっても正しく処理される。
 例4:西多摩郡檜原村三都郷 → 郷 ・・・ 東京都であっても県名以外の“都”が判定されるため大半が除かれる。
 例5:千葉県四街道市 → 市 ・・・ 県名が付いていても“道”が先に判定されるため市より前の部分が除かれる。
 例6:山梨県甲府市 → 市 ・・・ 県名が付いていても“府”が先に判定されるため市より前の部分が除かれる。
 例7:足利市県町 → 市 ・・・ 県名が付いていないため県名ではない“県”を検出して市より前の部分が除かれる。
 例8:京都府京都市 → 府京都市 ・・・ 県名の“府”より“都”が先に判定されるため最初の京都が除かれる。
 ◆反省点:安直に“都道府県”の4文字を判定に使ったために、“都道府県”の文字が都道府県名以外の住所に含まれるケースが処理されておらず、正しい結果とならない場合が発生してしまう。詰めが甘かったです。

A B
1 都道府県名付住所 都道府県名無住所
2 埼玉県さいたま市岩槻区加倉4-31-18 さいたま市岩槻区加倉4-31-18
3 (B2セルの式)→ =RIGHT(A2,LEN(A2)-IF(ISERROR(FIND("都",A2,1))=TRUE,IF(ISERROR(FIND("道",A2,1))=TRUE,IF(ISERROR(FIND("府",A2,1))=TRUE,IF(ISERROR(FIND("県",A2,1))=TRUE,0,FIND("県",A2,1)),FIND("府",A2,1)),FIND("道",A2,1)),FIND("都",A2,1)))

↑ページトップへ


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

↑ページトップへ




↑ページトップへ