[Googleスプレッドシート] 1つのセルに入っている住所を市区町村・その他 に分ける

Office

※ 本記事はアフィリエイト広告(Amazonアソシエイト)を含みます。

[Googleスプレッドシート] 1つのセルに入っている住所を市区町村・その他 に分ける

CRMとか使ってると分ける必要が出てくる。(特にSalesforce)

わざわざMecabとか辞書使ってやるようなレベルではないやつはスプレッドシート内でちゃちゃっとやりたい。

全列に反映させる前提なので、ArrayFormula 使ってる。

完成イメージ

A列に住所データが入ってる前提で、B〜F列に分けていきます。

A: 元の住所B: 郵便番号C: 住所本体D: 都道府県E: 市区郡F: その他
〒100-0001 東京都千代田区千代田1-1100-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