【ExcelVBA】都道府県・市区町村ごとにセルを分割する方法【コピペでOK】

2019年12月3日 2019年11月21日

20代怠け者

VBAで住所を都道府県と市区町村に分けるにはどうすればいいの？
住所全文から都道府県だけを抽出したい
コピペで作業できるようにしてほしい

この記事ではExcelVBAで「単一セルに入力された住所から、都道府県・市区町村ごとにセルを分割する」サンプルコードを紹介します。

記事内のサンプルコードをコピペして設定するだけで、作業の自動化が可能です。

また、このサンプルコード内の処理についても、詳しく解説しています。

ご参考までに、それではどうぞ。

1 単一セルの住所全文を都道府県・市区町村に分割する
2 単一セルの住所全文を都道府県・市区町村に分割するコードの解説

単一セルの住所全文を都道府県・市区町村に分割する

'DATAシートを変数「dataSh」に定義
Dim dataSh As Worksheet
Set dataSh = ThisWorkbook.Worksheets("DATA")
'データの数だけループ処理
Dim i As Long
Dim fullAddress As String
Dim prefectureWordCount As Long
Dim prefectureName As String
For i = 2 To 11
    '住所全文が入力されているセルの値を変数に定義
    fullAddress = dataSh.Cells(i, 1).Value
    
    '4文字目が「県」かどうか（神奈川県、和歌山県、鹿児島県）を判定し都道府県の文字数を変数に定義
    If InStr(fullAddress, "県") = 4 Then
        prefectureWordCount = 4
    Else
        prefectureWordCount = 3
    End If
    
    '住所全文から都道府県名を取得し、変数に定義
    prefectureName = Left(fullAddress, prefectureWordCount)
    
    '都道府県名と市区町村以降をセルに転記
    dataSh.Cells(i, 2).Value = prefectureName
    dataSh.Cells(i, 3).Value = Replace(fullAddress, prefectureName, "")
Next i

▼実行前

※住所は全てダミーデータです。

▼実行後

今回のコードでは、画像のように一つのセルに入力されている住所全文を「都道府県」とそれ以外に分けて別のセルに転記しています。

順にコードを解説します。

単一セルの住所全文を都道府県・市区町村に分割するコードの解説

繰り返し処理「For Next」

はじめに、今回の処理でメインとなる「For Next」について解説します。

'DATAシートを変数「dataSh」に定義
Dim dataSh As Worksheet
Set dataSh = ThisWorkbook.Worksheets("DATA")
'データの数だけループ処理
Dim i As Long
Dim fullAddress As String
Dim prefectureWordCount As Long
Dim prefectureName As String
For i = 2 To 11
    '住所全文が入力されているセルの値を変数に定義
    fullAddress = dataSh.Cells(i, 1).Value
～～～
Next i

「For Next」では、カウンターに設定した変数がループするごとに変化していきます。ここでは変数「i」がカウンターとなっており「2 To 11」つまり「2」から「11」まで変化していきます。

つまり、画像のセルから順番にデータを取得して変数「fullAddress」に住所全文を定義することになります。

ThisWorkbook.Worksheets("DATA").Cells(2, 1)
ThisWorkbook.Worksheets("DATA").Cells(3, 1)
ThisWorkbook.Worksheets("DATA").Cells(4, 1)
ThisWorkbook.Worksheets("DATA").Cells(5, 1)
ThisWorkbook.Worksheets("DATA").Cells(6, 1)
ThisWorkbook.Worksheets("DATA").Cells(7, 1)
ThisWorkbook.Worksheets("DATA").Cells(8, 1)
ThisWorkbook.Worksheets("DATA").Cells(9, 1)
ThisWorkbook.Worksheets("DATA").Cells(10, 1)
ThisWorkbook.Worksheets("DATA").Cells(11, 1)

こうして取得した変数「fullAddress」から「都道府県」を取り出し、「都道府県」とそれ以外を分割する処理を繰り返しています。

住所全文から「都道府県」を取り出す

つづいて、住所全文から「都道府県」を取り出す方法を解説します。

'4文字目が「県」かどうか（神奈川県、和歌山県、鹿児島県）を判定し都道府県の文字数を変数に定義
If InStr(fullAddress, "県") = 4 Then
    prefectureWordCount = 4
Else
    prefectureWordCount = 3
End If
        
'住所全文から都道府県名を取得し、変数に定義
prefectureName = Left(fullAddress, prefectureWordCount)

まず「都道府県」を抜き出すには特徴を知る必要があります。

「都道府県」は基本的に「都」「道」「府」「県」という文字までで3文字で構成されています。

ただし、コード内のコメントにもあるように次の3つの県だけは例外です。

神奈川県
和歌山県
鹿児島県

これら3つの県だけは、3文字ではなく4文字で抽出する必要があります。

そこで例外の判定をする必要があるのですが、全て4文字目が「県」となっていることがわかるので、この条件で分岐させ変数「prefectureWordCount」に「3」または「4」を定義します。

またここで使用しているのが「InStr」関数です。第一引数に指定した文字列内から第二引数に指定した文字を検索し、見つかった文字数を返します。

取得した「都道府県」文字数を利用して住所全文を分割してセルに転記する

最後に取得した「都道府県」文字数から「都道府県」とそれ以外の文字列を抽出してセルに転記する処理を解説します。

'住所全文から都道府県名を取得し、変数に定義
prefectureName = Left(fullAddress, prefectureWordCount)

'都道府県名と市区町村以降をセルに転記
dataSh.Cells(i, 2).Value = prefectureName
dataSh.Cells(i, 3).Value = Replace(fullAddress, prefectureName, "")

ここでポイントとなるのは、「Left」関数と「Replace」関数です。

まず、第一引数の左から、第二引数に指定した数値分だけ抽出する「Left」関数を使い、住所全文から「都道府県」を変数「prefectureName」に定義します。第二引数に指定するのは、先ほど取得した「都道府県」文字数の「prefecturesWordCount」です。