[Googleスプレッドシート] 1つのセルに入っている住所を市区町村・その他 に分ける
Office※ 本記事はアフィリエイト広告(Amazonアソシエイト)を含みます。
CRMとか使ってると分ける必要が出てくる。(特にSalesforce)
わざわざMecabとか辞書使ってやるようなレベルではないやつはスプレッドシート内でちゃちゃっとやりたい。
全列に反映させる前提なので、ArrayFormula 使ってる。
完成イメージ
A列に住所データが入ってる前提で、B〜F列に分けていきます。
| A: 元の住所 | B: 郵便番号 | C: 住所本体 | D: 都道府県 | E: 市区郡 | F: その他 |
|---|---|---|---|---|---|
| 〒100-0001 東京都千代田区千代田1-1 | 100-0001 | 東京都千代田区千代田1-1 | 東京都 | 千代田区 | 千代田1-1 |
| 千葉県市川市八幡1-1 | 千葉県市川市八幡1-1 | 千葉県 | 市川市 | 八幡1-1 |
各式は2行目に1つ書くだけで、下の行すべてに反映されます。IF(A2:A="","",...) は空行でエラーを出さないためのおまじない。
郵便番号(B列)
=ArrayFormula(IF(A2:A="","",IFERROR(REGEXEXTRACT(A2:A,"\d{3}-\d{4}"),"")))
郵便番号が入っていない行もあるので、IFERROR で空にしておきます。
住所本体(C列)
郵便番号や「〒」が混ざったままだと、この後の都道府県の判定がずれます。先に取り除いた列を作っておくのがコツ。
=ArrayFormula(IF(A2:A="","",TRIM(REGEXREPLACE(A2:A,"〒?\s*\d{3}-?\d{4}",""))))
都道府県(D列)
=ArrayFormula(IF(C2:C="","",REGEXEXTRACT(C2:C,"^(?:東京都|北海道|(?:京都|大阪)府|.{2,3}県)")))
以前は .+[都道府県] や .{2,3}?[都道府県] で書いていましたが、これだと
.+[都道府県]は最長一致なので、一番最後の都道府県の文字まで拾う(「東京都府中市」→「東京都府」).{2,3}?[都道府県]は先頭が都道府県なら正しく取れるが、位置を固定していないので、前に郵便番号などが残っていると「1東京都」のように巻き込む
といったずれが出ます。都・道・府は「東京都」「北海道」「京都府」「大阪府」の4つしかないので、名前ごと書いて、残りは「2〜3文字+県」と分けて書くのが確実です。
市区郡(E列)
=ArrayFormula(IF(C2:C="","",REGEXEXTRACT(C2:C,"^(?:東京都|北海道|(?:京都|大阪)府|.{2,3}県)((?:四日市|廿日市|野々市)市|大和郡山市|.+?[市区郡])")))
都道府県の後ろから、最短一致(.+?)で最初の市区郡までを取り出します。
| 書き方 | 「千葉県市川市」の結果 |
|---|---|
.+[市区郡](最長一致) | 後ろの番地に「区」などがあると、そこまで伸びる |
.+?[市区郡](最短一致) | 市川市 |
.+? は「1文字以上」なので、市川市の先頭の「市」では止まりません。ただし最短一致でもずれる地名(四日市市、廿日市市、野々市市、大和郡山市)があるので、それらは先に名前で拾っています。
その他(F列)
=ArrayFormula(IF(C2:C="","",MID(C2:C,LEN(D2:D)+LEN(E2:E)+1,200)))
住所本体から、都道府県と市区郡の文字数分を飛ばして残りを取ります。
注意点
- 政令指定都市: 「横浜市中区」は市区郡が「横浜市」、その他が「中区…」になります。区まで市区郡に入れたいかは、流し先の運用で決めてください
- 都道府県が省略された住所: D列・E列がエラーになります。件数が少なければ手で直すのが早い
- 全国の住所を100%分けられる保証はないので、取り込む前にざっと目視で確認を
JavaScript で同じことをやる場合はjsで 市区郡 を切り分けたいときに書いています。
まとめ
- 郵便番号は先に取り除いて「住所本体」の列を作る
- 都道府県は「東京都|北海道|京都府|大阪府|2〜3文字+県」で名前ごと
- 市区郡は最短一致
.+?[市区郡]+ 例外の地名リスト - 残りは
MIDで文字数分を飛ばす
都道府県から「都」「府」「県」を外したいときは都道府県 などの文字を消すもあわせてどうぞ。
GoogleSpreadsheet