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

#PR

2022年11月26日

20代怠け者 / 上本敏雅

Excelについて詳しく解説します

VBAで住所を都道府県と市区町村に分けるにはどうすればいい？
住所全文から都道府県だけを抽出したい
コピペで作業できるようにしてほしい
都道府県ごとにシートを分割する方法も一緒に教えてほしい

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

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

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

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

1 【コード①】単一セルの住所全文を都道府県・市区町村に分割する
2 【コード①解説】単一セルの住所全文を都道府県・市区町村に分割するコードの解説
3 【コード②】単一セルの住所全文から都道府県ごとにシートを追加する
4 【コード②解説】単一セルの住所全文から都道府県ごとにシートを追加するコードの解説
5 まとめ

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

'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」です。

次に、取得した「都道府県」を元の住所全文から削除すれば「都道府県」以外の文字列が取得できるので、第一引数から第二引数を検索し第3引数に置き換える「Replace」関数を使います。

「Replace(fullAddress, prefectureName, "")」で、住所全文から「都道府県」を探し「""」つまる空白に置き換えます。

それぞれを対象の行の2列目、3列目に転記すれば、処理は完了です。

以上、コピペでできるExcelVBAでの「単一セルに入力された住所から、都道府県・市区町村ごとにセルを分割する」方法の紹介でした。

コード内で解説した繰り返し処理や関数もコピペで使えますので、試してみてください。

【コード②】単一セルの住所全文から都道府県ごとにシートを追加する

もう１つ応用として、住所と都道府県から、シートを追加していくコードについても、合わせてご紹介します。

'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 14
    '住所全文が入力されているセルの値を変数に定義
    fullAddress = dataSh.Cells(i, 1).Value
    
    '4文字目が「県」かどうか（神奈川県、和歌山県、鹿児島県）を判定し都道府県の文字数を変数に定義
    If InStr(fullAddress, "県") = 4 Then
        prefectureWordCount = 4
    Else
        prefectureWordCount = 3
    End If
    
    '住所全文から都道府県名を取得し、変数に定義
    prefectureName = Left(fullAddress, prefectureWordCount)
    
    '都道府県シートがあるかどうか判定
    Dim flg As Boolean
    flg = False
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = prefectureName Then
            flg = True
            Exit For
        End If
    Next ws
    
    '都道府県シートがない場合はシート追加
    If flg = False Then
        Dim wsCnt As Long
        wsCnt = ThisWorkbook.Worksheets.Count
        Worksheets.Add After:=ThisWorkbook.Worksheets(wsCnt)
        ActiveSheet.Name = prefectureName
    End If
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 14
    '住所全文が入力されているセルの値を変数に定義
    fullAddress = dataSh.Cells(i, 1).Value
～～～
Next i

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

つまり、画像のセルから順番にデータを取得して変数「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)
ThisWorkbook.Worksheets("DATA").Cells(12, 1)
ThisWorkbook.Worksheets("DATA").Cells(13, 1)
ThisWorkbook.Worksheets("DATA").Cells(14, 1)

こうして取得した変数「fullAddress」から「都道府県」を取り出し、取り出した「都道府県」名を名前としたシートを追加する処理を繰り返しています。

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

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

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

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

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

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

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

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

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

取得した「都道府県」名のシートがあるかどうかを判定

次に、取得した「都道府県」名のシートがすでに存在しているかどうかを確認する方法を解説します。

'都道府県シートがあるかどうか判定
Dim flg As Boolean
flg = False
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    If ws.Name = prefectureName Then
        flg = True
        Exit For
    End If
Next ws

ここでポイントとなるのは、繰り返し処理「For Each」です。

「For Each」は指定した範囲内の全ての要素に対して処理を行います。ここでは、「ThisWorkbook.Worksheets」内の全ワークシート「ws」に対して処理を行っています。

そして、シートの名前「ws.Name」が「For Next」で取得した「prefectureName」と同じであったら「flg = True」として「Exit For」で繰り返し処理を抜けています。

また、はじめに解説した「For Next」処理で再度同じ処理が実行される際、「flg」の値が引き継がれるので、毎回「flg = False」として初期化する処理を入れています。

この処理で、コピーしようとしている「都道府県名」のシートがすでにワークブック内に存在しているかどうかを確認することができます。

この判定をいれずシート名の重複を確認しないと、同一のデータがあった場合、画像のエラーが出てマクロが止まってしまうので注意しましょう。

取得した「都道府県」名のシートを末尾に追加

取得した「都道府県」名のシートを末尾に追加する方法を解説します。

'都道府県シートがない場合はシート追加
If flg = False Then
    Dim wsCnt As Long
    wsCnt = ThisWorkbook.Worksheets.Count
    Worksheets.Add After:=ThisWorkbook.Worksheets(wsCnt)
    ActiveSheet.Name = prefectureName
End If

先ほど「For Each」処理で取得した、「都道府県」名のシートがあるかどうかを判定した「flg」が「False」の場合、つまり該当名称のシートがない場合にシート追加処理を実行します。

まず、現在のワークシートの枚数を「ThisWorkbook.Worksheets.Count」で取得します。例えば、画像の状態なら、「ThisWorkbook.Worksheets.Count」は「2」となります。