住所から市区町村を抽出する関数です。
市区町村別に集計したい場合等にを使ってください。
VBEの標準モジュールに下のコードを登録して「Excelマクロ有効フォーム」で保存してください。
関数の登録方法は⇒♯000 ユーザー定義関数を登録する方法
登録されたら、シート上からユーザー関数「POCity」が使用できます。
使い方は
ワークシート上で = POCity (住所) の関数として使用することで、 市区町村を を取得できます。
【例】
POCity(住所)
POCity(“神奈川県横浜市青葉区青葉町”)→ 横浜市青葉区
政令指定都市は区までが市区町村となります
POCity(“東京都港区赤坂”)→ 港区
東京都特別区が市区町村となります
POCity(“東京都八王子市暁町”)→八王子市
Function POCity(A As String) As String
'住所から市区町村を取り出す関数
'POCity(住所)
'POCity("神奈川県横浜市青葉区青葉町")→ 横浜市青葉区
'政令指定都市は区までが市区町村となります
'POCity("東京都港区赤坂")→ 港区
'東京都特別区は区までが市区町村となります
'POCity("東京都八王子市暁町")→八王子市
On Error GoTo EXITFUN
Dim Prefecture As String
Dim CA As String
Dim i As Long
Dim City As String
Dim PRE(1 To 47, 1 To 1)
Dim GOCITY(1 To 20, 1 To 2)
Dim SACITY(1 To 23, 1 To 2)
CA = A
PRE(1, 1) = "北海道"
PRE(2, 1) = "青森県"
PRE(3, 1) = "岩手県"
PRE(4, 1) = "宮城県"
PRE(5, 1) = "秋田県"
PRE(6, 1) = "山形県"
PRE(7, 1) = "福島県"
PRE(8, 1) = "茨城県"
PRE(9, 1) = "栃木県"
PRE(10, 1) = "群馬県"
PRE(11, 1) = "埼玉県"
PRE(12, 1) = "千葉県"
PRE(13, 1) = "東京都"
PRE(14, 1) = "神奈川県"
PRE(15, 1) = "新潟県"
PRE(16, 1) = "富山県"
PRE(17, 1) = "石川県"
PRE(18, 1) = "福井県"
PRE(19, 1) = "山梨県"
PRE(20, 1) = "長野県"
PRE(21, 1) = "岐阜県"
PRE(22, 1) = "静岡県"
PRE(23, 1) = "愛知県"
PRE(24, 1) = "三重県"
PRE(25, 1) = "滋賀県"
PRE(26, 1) = "京都府"
PRE(27, 1) = "大阪府"
PRE(28, 1) = "兵庫県"
PRE(29, 1) = "奈良県"
PRE(30, 1) = "和歌山県"
PRE(31, 1) = "鳥取県"
PRE(32, 1) = "島根県"
PRE(33, 1) = "岡山県"
PRE(34, 1) = "広島県"
PRE(35, 1) = "山口県"
PRE(36, 1) = "徳島県"
PRE(37, 1) = "香川県"
PRE(38, 1) = "愛媛県"
PRE(39, 1) = "高知県"
PRE(40, 1) = "福岡県"
PRE(41, 1) = "佐賀県"
PRE(42, 1) = "長崎県"
PRE(43, 1) = "熊本県"
PRE(44, 1) = "大分県"
PRE(45, 1) = "宮崎県"
PRE(46, 1) = "鹿児島県"
PRE(47, 1) = "沖縄県"
GOCITY(1, 1) = "北海道": GOCITY(1, 2) = "札幌市"
GOCITY(2, 1) = "宮城県": GOCITY(2, 2) = "仙台市"
GOCITY(3, 1) = "埼玉県": GOCITY(3, 2) = "さいたま市"
GOCITY(4, 1) = "千葉県": GOCITY(4, 2) = "千葉市"
GOCITY(5, 1) = "神奈川県": GOCITY(5, 2) = "横浜市"
GOCITY(6, 1) = "神奈川県": GOCITY(6, 2) = "川崎市"
GOCITY(7, 1) = "神奈川県": GOCITY(7, 2) = "相模原市"
GOCITY(8, 1) = "新潟県": GOCITY(8, 2) = "新潟市"
GOCITY(9, 1) = "静岡県": GOCITY(9, 2) = "静岡市"
GOCITY(10, 1) = "静岡県": GOCITY(10, 2) = "浜松市"
GOCITY(11, 1) = "愛知県": GOCITY(11, 2) = "名古屋市"
GOCITY(12, 1) = "京都府": GOCITY(12, 2) = "京都市"
GOCITY(13, 1) = "大阪府": GOCITY(13, 2) = "大阪市"
GOCITY(14, 1) = "大阪府": GOCITY(14, 2) = "堺市"
GOCITY(15, 1) = "兵庫県": GOCITY(15, 2) = "神戸市"
GOCITY(16, 1) = "岡山県": GOCITY(16, 2) = "岡山市"
GOCITY(17, 1) = "広島県": GOCITY(17, 2) = "広島市"
GOCITY(18, 1) = "福岡県": GOCITY(18, 2) = "北九州市"
GOCITY(19, 1) = "福岡県": GOCITY(19, 2) = "福岡市"
GOCITY(20, 1) = "熊本県": GOCITY(20, 2) = "熊本市"
SACITY(1, 1) = "東京都": SACITY(1, 2) = "千代田区"
SACITY(2, 1) = "東京都": SACITY(2, 2) = "中央区"
SACITY(3, 1) = "東京都": SACITY(3, 2) = "港区"
SACITY(4, 1) = "東京都": SACITY(4, 2) = "新宿区"
SACITY(5, 1) = "東京都": SACITY(5, 2) = "文京区"
SACITY(6, 1) = "東京都": SACITY(6, 2) = "台東区"
SACITY(7, 1) = "東京都": SACITY(7, 2) = "墨田区"
SACITY(8, 1) = "東京都": SACITY(8, 2) = "江東区"
SACITY(9, 1) = "東京都": SACITY(9, 2) = "品川区"
SACITY(10, 1) = "東京都": SACITY(10, 2) = "目黒区"
SACITY(11, 1) = "東京都": SACITY(11, 2) = "大田区"
SACITY(12, 1) = "東京都": SACITY(12, 2) = "世田谷区"
SACITY(13, 1) = "東京都": SACITY(13, 2) = "渋谷区"
SACITY(14, 1) = "東京都": SACITY(14, 2) = "中野区"
SACITY(15, 1) = "東京都": SACITY(15, 2) = "杉並区"
SACITY(16, 1) = "東京都": SACITY(16, 2) = "豊島区"
SACITY(17, 1) = "東京都": SACITY(17, 2) = "北区"
SACITY(18, 1) = "東京都": SACITY(18, 2) = "荒川区"
SACITY(19, 1) = "東京都": SACITY(19, 2) = "板橋区"
SACITY(20, 1) = "東京都": SACITY(20, 2) = "練馬区"
SACITY(21, 1) = "東京都": SACITY(21, 2) = "足立区"
SACITY(22, 1) = "東京都": SACITY(22, 2) = "葛飾区"
SACITY(23, 1) = "東京都": SACITY(23, 2) = "江戸川区"
For i = 1 To 47
If InStr(A, PRE(i, 1)) = 1 Then
Prefecture = PRE(i, 1)
CA = Replace(A, PRE(i, 1), "")
End If
Next
For i = 1 To 20
If InStr(CA, GOCITY(i, 2)) = 1 Then
City = Mid(CA, 1, InStr(CA, "区"))
GoTo EndSearch
End If
Next
For i = 1 To 23
If InStr(CA, SACITY(i, 2)) = 1 Then
City = SACITY(i, 2)
GoTo EndSearch
End If
Next
If Mid(CA, 1, 3) = "余市郡" Then
If InStr(CA, "町") > 0 Then
City = Mid(CA, 1, InStr(CA, "町"))
Else
City = Mid(CA, 1, InStr(CA, "村"))
End If
GoTo EndSearch
ElseIf InStr(CA, "郡山市") > 0 Then
City = Mid(CA, 1, InStr(CA, "郡山市") + 2)
GoTo EndSearch
ElseIf Mid(CA, 1, 3) = "郡上市" Then
City = "郡上市"
GoTo EndSearch
ElseIf Mid(CA, 1, 3) = "蒲郡市" Then
City = "蒲郡市"
GoTo EndSearch
ElseIf Mid(CA, 1, 3) = "小郡市" Then
City = "小郡市"
GoTo EndSearch
ElseIf Mid(CA, 1, 4) = "四日市市" Then
City = "四日市市"
GoTo EndSearch
ElseIf Mid(CA, 1, 4) = "廿日市市" Then
City = "廿日市市"
GoTo EndSearch
ElseIf Mid(CA, 1, 4) = "野々市市" Then
City = "野々市市"
GoTo EndSearch
ElseIf Mid(CA, 2, 3) = "村山郡" Then
City = Mid(CA, 1, InStr(CA, "町"))
GoTo EndSearch
ElseIf Mid(CA, 1, 3) = "田村郡" Then
City = Mid(CA, 1, InStr(CA, "町"))
GoTo EndSearch
ElseIf Mid(CA, 1, 6) = "佐波郡玉村町" Then
City = "佐波郡玉村町"
GoTo EndSearch
ElseIf Mid(CA, 1, 6) = "柴田郡村田町" Then
City = "柴田郡村田町"
GoTo EndSearch
ElseIf Mid(CA, 1, 6) = "高市郡高取町" Then
City = "高市郡高取町"
GoTo EndSearch
ElseIf Mid(CA, 1, 7) = "高市郡明日香村" Then
City = "高市郡明日香村"
GoTo EndSearch
ElseIf Mid(CA, 1, 6) = "杵島郡大町町" Then
City = "杵島郡大町町"
GoTo EndSearch
End If
If InStr(CA, "市") = 1 And InStr(Mid(CA, 2), "市") > 0 Then
City = Mid(CA, 1, InStr(CA, "市")) & Mid(CA, 2, InStr(Mid(CA, 2), "市"))
GoTo EndSearch
ElseIf InStr(CA, "市") > 0 And InStr(CA, "郡") > 0 Then
If InStr(CA, "市") < InStr(CA, "郡") Then
City = Mid(CA, 1, InStr(CA, "市"))
GoTo EndSearch
ElseIf InStr(CA, "村") = 0 Then
City = Mid(CA, 1, InStr(CA, "町"))
GoTo EndSearch
ElseIf InStr(CA, "町") = 0 Then
City = Mid(CA, 1, InStr(CA, "村"))
GoTo EndSearch
ElseIf InStr(CA, "町") < InStr(CA, "村") Then
City = Mid(CA, 1, InStr(CA, "町"))
GoTo EndSearch
Else
City = Mid(CA, 1, InStr(CA, "村"))
GoTo EndSearch
End If
ElseIf InStr(CA, "市") > 0 Then
City = Mid(CA, 1, InStr(CA, "市"))
GoTo EndSearch
ElseIf InStr(CA, "郡") > 0 And InStr(CA, "町") > 0 And InStr(CA, "村") > 0 Then
If InStr(CA, "町") < InStr(CA, "村") Then
City = Mid(CA, 1, InStr(CA, "町"))
GoTo EndSearch
Else
City = Mid(CA, 1, InStr(CA, "村"))
GoTo EndSearch
End If
ElseIf InStr(CA, "郡") > 0 And InStr(CA, "町") > 0 Then
City = Mid(CA, 1, InStr(CA, "町"))
ElseIf InStr(CA, "郡") > 0 And InStr(CA, "村") > 0 Then
City = Mid(CA, 1, InStr(CA, "村"))
ElseIf InStr(CA, "町") > 0 Then
City = Mid(CA, 1, InStr(CA, "町"))
GoTo EndSearch
ElseIf InStr(CA, "村") > 0 Then
City = Mid(CA, 1, InStr(CA, "村"))
GoTo EndSearch
End If
EndSearch:
POCity = City
EXITFUN:
End Functionここで紹介したコード使用による損害に対しては一切責任は負えません。