Excelで住所を都道府県と残りの住所に分割する方法|3文字・4文字の県名に対応

Excelで住所を分ける時、左から3文字を切り出すだけでは「神奈川県」「和歌山県」「鹿児島県」の末尾の「県」が残ってしまいます。これら3県は4文字、それ以外の都道府県名は3文字です。この違いを使うと、都道府県名で始まる住所を「都道府県」と「残りの住所」に分けられます。

この方法は、市区町村だけを取り出すものではありません。残りの列には町名・番地・建物名も含まれます。郵便番号付き、都道府県を省略した住所、先頭の空白があるデータには、そのまま適用しないでください。

1. 元の住所を残し、分割できる入力か確かめる

A列に元の住所を残し、B列へ都道府県、C列へ残りの住所を出す構成にします。最初はブックを別名で保存し、数行で結果を確認してから対象を広げます。顧客名簿を外部サイトへ送る必要はなく、以下の数式はブック内で処理できます。

使用前に、東京都・大阪府・北海道などの3文字の例と、神奈川県・和歌山県・鹿児島県の4文字の例を確認します。都道府県名の前に「〒」や郵便番号がある行、住所が空欄の行、都道府県を省略した行は別に扱います。先頭に全角空白がある行も、この式では位置がずれます。

また、次の分岐は住所の正しさを判定する仕組みではありません。4文字目が「県」なら4文字を取るだけなので、存在しない地名でも結果が出ます。入力形式が揃っていない名簿では、47都道府県の一覧との照合や例外行の確認を併用してください。

2. 都道府県と残りの住所を別々の列へ取り出す

A2に都道府県名から始まる住所がある場合、B2に次の式を入力します。MIDで4文字目を取り出し、それが「県」なら左から4文字、そうでなければ3文字を取り出します。

=IF(MID(A2,4,1)="県",LEFT(A2,4),LEFT(A2,3))

図の破線が切り出す境界です。例えば「和歌山県和歌山市…」では、最初の「和歌山県」だけをB列へ出し、後ろの「和歌山市…」はC列へ残します。県名と市名に同じ文字が含まれていても、先頭の文字数で区切るので取り違えません。

C2には次の式を入力します。B2に取り出した都道府県名の文字数をLENで数え、その次の文字から残りを取り出します。

=MID(A2,LEN(B2)+1,LEN(A2))

=SUBSTITUTE(A2,B2,"",1)でも最初の1回だけ県名を除く方法になりますが、このページでは「先頭の何文字を除くか」が分かるMIDの式を使います。どちらもB2が正しく取り出されていることが前提です。MIDの位置指定についてはMicrosoftのMID関数の説明でも確認できます。

数行で確認後、B2とC2を下へコピーします。A2・B2の行番号が3、4と変わり、同じ行の住所を処理しているか確かめてください。空欄を含む表で列末尾まで機械的に展開せず、元データがある行を対象にします。

3. 市区町村だけを切り出す処理とは分ける

この式でできるのは、都道府県名とその後ろの分離です。「横浜市中区…」から横浜市だけを取り出すのか、中区まで取り出すのかは、提出先の列仕様によって違います。「○○郡○○町」のように、郡と町を含む住所もあります。

最初の「市」「区」「町」という文字だけで区切る方法は、地名に同じ文字が含まれる場合や、政令指定都市の行政区で誤ることがあります。市区町村単位の正規化が必要なら、郵便番号・自治体コード・提出先の住所マスターなどを使って照合します。文字列を短くする処理と、住所の存在を確認する処理は別です。

郵便番号が先頭にある場合は、郵便番号列と住所列を先に分けます。半角・全角の数字や空白が混在する場合もあるため、固定文字数で一律に除く前に形式を点検します。都道府県が省略されている住所は、短い文字列から推測して補わず、元の登録情報などを確認してください。

4. コピー後は、結合し直した値と元の住所を比較する

確認用の列に=B2&C2=A2を入れると、分割した2列をつないだ文字が元と一致するかを調べられます。FALSEなら文字が欠けた、余分な文字が入った、参照する行がずれたなどの可能性を確認します。ただしTRUEでも区切り位置が正しいとは限りません。3文字・4文字の県名と例外行を目視でも確認します。

特に先頭ゼロを含む郵便番号、建物名や部屋番号、外字・異体字は、別の整形操作で変わっていないかを確かめます。分割と同時に空白削除や表記統一をまとめて行うと、どの処理で変わったか追いにくくなります。一つずつ確認し、元の住所列は結果の承認まで残してください。

提出用に値だけが必要なら、結果をコピーして別シートへ値として貼り付け、件数と列順を確認します。元の列を削除してから数式が参照切れになることを避けるため、値への変換と照合を済ませてから提出ファイルを作ります。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】