Excel約4分で読めます

Excelで住所を都道府県と市区町村に分ける|4文字の県の扱い

Excelで住所を都道府県と市区町村に分けるを表したイメージ
記事の内容を表したイメージ(AI生成)

住所が一列にまとまった名簿を、都道府県と市区町村以降に分けたい。よく紹介される「LEFT(A2,3)で左から3文字を取る」方法を使ったら、「神奈川県横浜市」が「神奈川」と「県横浜市」に切れてしまった、という問題が起きやすい場面です。

日本の都道府県名は「東京都」「大阪府」「北海道」のように3文字が多いのですが、「神奈川県」「和歌山県」「鹿児島県」の3つだけ4文字です。この3県を含む住所が混在する場合、文字数を固定して切り取る式はそのままでは使えません。

この記事では、4文字の県を正しく判別して都道府県名を取り出す式の仕組みと、市区町村以降を分ける式をあわせて説明します。作業の前にファイルをコピーして手元に残しておくことを先にお勧めします。


なぜ「左から3文字」では足りないのか

都道府県名の末尾は「都」「道」「府」「県」の4種類で、そのうち「県」で終わるものに4文字が3つあります。

表は横にスクロールできます

都道府県名文字数
北海道3
東京都3
大阪府3
神奈川県4
和歌山県4
鹿児島県4

「LEFT(A2,3)」は4文字の県名を「神奈川」で切ってしまうため、都道府県列に「神奈川」、市区町村列の先頭に「県」が残ります。この状態で集計やVLOOKUPをかけると、都道府県名が一致しない行が発生します。

正しく切り分けるには、「4文字目が『県』かどうかを判定してから、取り出す文字数を変える」という考え方が必要です。


都道府県を取り出す式

判定の考え方

4文字目の文字が「県」であれば4文字、そうでなければ3文字を取り出します。MID関数で4文字目を取り出し、IF関数で分岐させます。

=IF(MID(A2,4,1)="県",LEFT(A2,4),LEFT(A2,3))
  • MID(A2,4,1) :A2セルの4文字目を1文字取り出す
  • ="県" :それが「県」と一致するか判定
  • 一致すれば LEFT(A2,4)(4文字)、一致しなければ LEFT(A2,3)(3文字)

架空の例です。

表は横にスクロールできます

A列(元の住所)結果
神奈川県横浜市港北区○○神奈川県
大阪府大阪市北区○○大阪府
鹿児島県鹿児島市○○鹿児島県
北海道札幌市中央区○○北海道

この式はGoogleスプレッドシートでも同じ書き方で動作します。画面の表示やメニュー名は版や設定により異なります。

「都」「道」「府」は3文字で固定されているので追加の分岐は不要

「東京都」「北海道」「大阪府」「京都府」はすべて3文字です。4文字目が「県」かどうかだけを見れば、47都道府県すべてに対応できます。「都」「道」「府」を個別に判定する必要はありません。


市区町村以降を取り出す式

都道府県名が何文字かわかれば、LEN関数とMID関数を組み合わせて残りを取り出せます。

=MID(A2,LEN(B2)+1,LEN(A2)-LEN(B2))
  • B2 :上の式で取り出した都道府県名が入っているセル
  • LEN(B2)+1 :都道府県名の文字数の次の位置から開始
  • LEN(A2)-LEN(B2) :取り出す文字数(元の住所全体から都道府県名の長さを引いた残り)

架空の例です。

表は横にスクロールできます

A列(元の住所)B列(都道府県)C列(市区町村以降)
神奈川県横浜市港北区○○神奈川県横浜市港北区○○
大阪府大阪市北区○○大阪府大阪市北区○○

B列の式を先に確定させてからC列を書くと、参照がずれにくくなります。


作業前に確認しておくこと

データの状態を先に点検する

式を入れる前に、元データにいくつかの状態が混在していないか確認しておくと、式が想定外の結果を返したときに原因を追いやすくなります。

  • 全角・半角の混在:都道府県名が全角で、番地が半角数字になっているケースはよくあります。MIDやLEFTの文字数カウントは1文字ずつ数えるため、全角・半角が混在していても文字数の計算自体は変わりません。ただし、後でVLOOKUPや集計に使う場合は表記の統一を検討する場面があります。
  • 先頭にスペースが入っている:セルの先頭に空白があると4文字目の判定がずれます。TRIM関数(=TRIM(A2))で余分なスペースを除いてから作業するか、作業列を分けて対処します。
  • 都道府県名が省略されている:「横浜市港北区○○」のように都道府県が入力されていない行が混在する場合、上の式では誤った位置で切れます。この場合は別途処理が必要です。

ファイルのコピーを先に取る

元の住所列を誤って上書きしてしまうと元に戻しにくくなります。作業前にファイル全体をコピーして別名で保存するか、シートを複製しておくことをお勧めします。個人情報を含む名簿を外部サービスや生成AIで処理する場合は、情報管理のルールを先に確認してください。


式がうまく動かないときの確認手順

結果が意図と違う行があった場合、次の順で確認します。

  1. A列のセルを選択してF2キーを押し、先頭にスペースがないか確認する。目で見てわかりにくいスペースが入っていることがあります。
  2. MID(A2,4,1)を別のセルに単独で入れて、4文字目が何か確認する。「県」以外の文字が返ってきた場合、元データの文字数や表記が想定と違います。
  3. LEN(A2)で文字数を確認する。想定より多い場合は、末尾に改行コードや不可視文字が含まれている場合があります。CLEAN関数(=CLEAN(A2))で制御文字を除けることがあります。

次の行動

作業前にファイルのコピーを保存してから、B列に =IF(MID(A2,4,1)="県",LEFT(A2,4),LEFT(A2,3)) を入れて、神奈川県・和歌山県・鹿児島県のデータが含まれる行で正しく4文字取れているか先に確認してください。都道府県列が確定したら、C列に =MID(A2,LEN(B2)+1,LEN(A2)-LEN(B2)) を入れて市区町村以降を取り出します。想定外の結果が出た行は、TRIM・CLEAN・MIDを単独で使って元データの状態を一行ずつ確かめると原因が特定しやすくなります。

参考資料

この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ