住所の中に、県と市以外の住所が入っていて、それを県別に分けたいという時があると思います。そんなときにお役に立てると思うマクロです。
はじめに、
住所のデータがある場合、もしかすると、郵便番号からくっついて一つのデータになっているときがあります。ここで紹介するのは、そんなときに、「郵便番号」と「都道府県」と「市以下の住所」にわける方法です。
例として、
札幌市役所 〒060-8611 札幌市中央区北1条西2丁目
熊谷市役所 〒360-0041 埼玉県熊谷市宮町2-47-1
前橋市役所 〒371-8601 群馬県前橋市大手町二丁目12番1号
の場合を考えましょう。3件であれば、手で分けたほうが早いですが、これが、数百行ともなると、なんとかしたくなるものです。以下に、都道府県の県庁所在地リストをサンプルとして置いておきましたので、試しに使ってみてください。
まず、郵便番号を別にしましょう。
今、E列の住所を、役所名、郵便番号、都道府県、住所に分けたいと思います。役所名はそのままで、郵便番号と住所が合体しているセルをとりあえず住所の列に移動します。郵便番号は、計算式で、切り分けたいと思います。
F２のセルに ＋left(E2,9) と入れて、下にコピーすれば、郵便番号だけが取れます。
ここで、必ず、F列全体を値固定（コピーして値の貼り付けを行う）してください。
ただ、E列は別に郵便番号は残ったままで邪魔です。郵便番号を取り除きたいときに、これも置換機能を使って取り除きます。この時、上記F列を値固定していないと、F列に郵便番号が残らないで消えてしまいます。
E列を選択してください。
郵便番号は頭に、「〒」がついて、そのあと、「123-4567」の7桁表示です。これを探すとすると、「〒???-???? 」ということになり、最後にスペースがついております。この〒???-????を””null（空白）に置換してあげます。
都道府県とそれ以下に分けるマクロの話
やっと本題に入ります。郵便番号を除かれた文字列は、
北海道札幌市中央区北1条西2丁目
のようになっていると思います。この列がE列にあるとすれば、E列を全部G列とH列に同じものをコピーしてください。
G列は、都道府県のみにします。H列は都道府県を削除した文字列に変更します。
方法は、上記で郵便番号を除いた方法と同じにするのですが、それをマクロ化しただけのものです。
マクロの内容
マクロの内容は、二つのマクロとなってます。一つは都道府県を削除する内容です。Sub 都道府県を削除()のマクロです。この内容は、まずはじめに、「列を選んでください」のメッセージを出して、列が選択されていないときには、マクロを強制終了できるようにしておきます。確認メッセージがないと、全部のセルに対して都道府県が削除されてしまうので、目的の列だけを削除するように促します。もっとも、マクロの止め方がわからないときには、そのまま進むしかないので、後で困ります。次に、郵便番号を消したときと同じように、検索で”＊県”を探して、それを””（ヌル）に置換します。さらに県を ”＊海道”を””に、”＊府”を””に、”＊京都”を””に置換します。二つめは、都道府県以下を削除するマクロです。内容は同じで”東京都＊”を”東京都”にすることで、東京都よりしたの文字が消えます。同じように、”県*”を”県”に、”大阪府＊“を”大阪府”に、”京都府*”を”京都府”に、”北海道*“を”北海道”に置換するという内容です。
注意）以下のマクロ文をコピーしてもうまく動きません。以下にテキストファイルをアップロードできるようにしておきましたので、その内容をマクロ文に貼り付けて動かしてください。
Sub 都道府県を削除()
‘
‘ Macro1 Macro
‘ 県の前を削除
‘
MsgBox “まず列を選んでください。この状態で、列が選ばれてなかったマクロ中止してください。”
‘
Selection.Replace What:=”*県”, Replacement:=””, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False
Selection.Replace What:=”北海道”, Replacement:=””, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=”大阪府”, Replacement:=””, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False
Selection.Replace What:=”京都府”, Replacement:=””, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=”東京都”, Replacement:=””, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Sub 都道府県のみにする()
‘
‘ 都道府県のみにする Macro
‘
‘
MsgBox “まず列を選んでください。この状態で、列が選ばれてなかったマクロ中止してください。”
‘Columns(“C:C”).Select
Selection.Replace What:=”東京都*“, Replacement:=”東京都”, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False
Selection.Replace What:=”県*”, Replacement:=”県”, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=”大阪府*”, Replacement:=”大阪府”, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:=”京都府*”, Replacement:=”京都府”, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=”北海道*“, Replacement:=”北海道”, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ReplaceFormat:=False
End Sub
さらに応用
ＭｓｇBｏｘを変数にして、さらに使いやすくした例
コメント