[Googleスプレッドシート] 都道府県 などの文字を消す
Office※ 本記事はアフィリエイト広告(Amazonアソシエイト)を含みます。
参照したいデータシートには 東京、福岡 とかって書いてあるけど住所データの方には普通に東京都って書いてあるときにうまいことしたいなーっていう。
そういうときも正規表現で置換すればいいだけ。 だと思ってたら、京都が厄介だった。
単純に置換すると京都が壊れる
「都道府県のどれかの文字を消す」と考えて、こう書くと…
=REGEXREPLACE(A2,"[都道府県]","")
| A2 | 結果 |
|---|---|
| 東京都 | 東京 |
| 京都府 | 京 |
| 北海道 | 北海 |
京都の「都」まで消えてしまいます。北海道もちょっと困る。
最初の解決策: 最後の1文字を消す
必ず最後の一文字なので、
=LEFT(A2,LEN(A2)-1)
これで「京都府」は「京都」になります。ただ、これだと北海道も「北海」になるのが惜しいところ。
改良版: 末尾の 都・府・県 だけ消す
=REGEXREPLACE(A2,"[都府県]$","")
| A2 | 結果 |
|---|---|
| 東京都 | 東京 |
| 京都府 | 京都 |
| 大阪府 | 大阪 |
| 神奈川県 | 神奈川 |
| 北海道 | 北海道 |
$ は「末尾」の意味なので、最後の1文字だけが対象になって京都の「都」は残ります。「道」を対象に入れていないので、北海道はそのまま。参照データ側でも「北海道」と書かれていることが多いので、これで揃います。
住所から都道府県を取り出してから消す
A列に住所がまるごと入っている場合は、まず都道府県を取り出します。
=REGEXEXTRACT(A2,"^(?:東京都|北海道|(?:京都|大阪)府|.{2,3}県)")
都・道・府は「東京都」「北海道」「京都府」「大阪府」しかないので、名前ごと書いてしまうのが確実。.+[都道府県] のような書き方だと、「東京都府中市」の「府」まで拾ってしまいます。
取り出してから末尾を消すなら、まとめてこう。
=REGEXREPLACE(REGEXEXTRACT(A2,"^(?:東京都|北海道|(?:京都|大阪)府|.{2,3}県)"),"[都府県]$","")
参照データと突き合わせる
これで「東京」「京都」の形に揃ったので、あとは VLOOKUP で参照データを引けます。
=VLOOKUP(REGEXREPLACE(B2,"[都府県]$",""),参照シート!A:C,2,FALSE)
逆に、参照データ側に「都府県」を足して揃える方向もアリですが、どの県に何を付けるかの対応表が必要になるので、消す方向の方が楽です。
まとめ
| やりたいこと | 式 |
|---|---|
| 東京都 → 東京(北海道はそのまま) | =REGEXREPLACE(A2,"[都府県]$","") |
| 住所から都道府県を取り出す | =REGEXEXTRACT(A2,"^(?:東京都|北海道|(?:京都|大阪)府|.{2,3}県)") |
1セルにまとまってる住所データを複数に分けるには1つのセルに入っている住所を市区町村・その他に分けるを参照。
GoogleSpreadsheet