Excel2010 住所5分割2

質問

以前の続きです。
教えて頂いたコードを少し変えたものです。
分割の仕方が変わりました。
変更前：「都道府県」「市区町村郡」「通称名」「○丁目」「番地ビル」
変更後：「都道府県」「市区郡」「町域名」「○丁目」「番地ビル」
住所はV列の5行目からですが5行目は入っていません。空白とが入り混じってます。
M列　　　　　N列　　　　　　O列　　　　　P列　　　　　　Q列　　　
都道府県　　市区郡　　　　　町域名　　　　丁目　　　　　　番地
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
「東京都」　「豊島区」　　　「東池袋」　　「３丁目」　　「１番１号」
「東京都」　「八王子市」　　「子安町」　　　「４丁目」　　「１０番１５号」
「神奈川県」　「川崎市高津区」　「明津」　　　　　　「１２番地メゾン美鈴」

'Option Explicit
Option Compare Text
DefInt A-Z
'
Sub Macro2()
'
Dim SplitData As Variant
Dim SplitFlag As Variant
Dim StringCheck As String
Dim StringMatch As String
Dim SubAddress As String
Dim IY As Long
'
Application.ScreenUpdating = False

SplitData = Array("都,道,府,県", "市,区,郡", "", "丁目", "")
SplitFlag = Array(-1, 1, 0, -1, 0)
Application.ScreenUpdating = False
'
For IY = 8 To Cells(Rows.Count, "V").End(xlUp).Row
StringCheck = Cells(IY, "V")
If StringCheck <> "" Then
StringMatch = Split1(StringCheck, "0,1,2,3,4,5,6,7,8,9", -1)
Parameter = Len(StringMatch)
SubAddress = Mid(StringCheck, Parameter)
StringCheck = Left(StringCheck, Parameter - 1)
'
For ix = 0 To 4
StringMatch = SplitData(ix)
Parameter = SplitFlag(ix)
'
If StringMatch = "" Then
Cells(IY, ix + 12) = StringCheck
StringCheck = SubAddress
Else
StringMatch = Split1(StringCheck, StringMatch, Parameter)
Cells(IY, ix + 12) = StringMatch
Parameter = Len(StringMatch)
StringCheck = Mid(StringCheck, Parameter + 1)
End If
Next ix
End If
Next IY
End Sub
'
Function Split2(StringCheck As String, StringMatch As String, Parameter) As String
'　 Paemater－：区切り文字のうち、先のものを有効とする。
'　　　　　 ＋：区切り文字のうち、後のものを有効とする。
Dim SMatch As Variant
Dim W1 As String
'
SMatch = Split(StringMatch, ",")
Max = -32768
'
For i1 = 0 To UBound(SMatch)
W1 = SMatch(i1)
ins = InStr(StringCheck, W1) * Sgn(Parameter)
'
If ins <> 0 Then
Max = WorksheetFunction.Max(Max, ins)
End If
Next i1
'
If Max <> -32768 Then
Max = Abs(Max) - 1 + Len(W1)
Split1 = Left(StringCheck, Max)
End If
Application.ScreenUpdating = True
End Function

実行すると全ての京都府が都道府県名で「京都」で市区郡に「府長岡京市」と分割されてしまいます。
市区郡は全て「府長岡京市」ではなく住所で変わります。「府京都市中京区壬生辻町」など
もう一つは市川市など同じ市区郡が入る住所はうまく分割されません。
「千葉県」「市」 「川市市川」
「三重県」「四日市」「市北浜田町」など
関数だとなんとなくできましたがVBAだとわかりません。

IF(M8="東京都",LEFT(N8,FIND("区",N8)),IF(IFERROR(MATCH(LEFT(N8,1),{"堺"},FALSE),
IFERROR(MATCH(LEFT(N8,2),{"札幌","仙台","千葉","横浜","川崎","新潟","静岡","浜松","京都","大阪","神戸","岡山","広島","福岡","熊本"},FALSE),
IFERROR(MATCH(LEFT(N8,3),{"相模原","名古屋","北九州"},FALSE),
IFERROR(MATCH(LEFT(N8,4),{"さいたま"},FALSE),-1))))>0,LEFT(N8,FIND("区",N8)),
IF(IFERROR(MATCH(LEFT(N8,2),{"郡山","市原","郡上","蒲郡","小郡","市川"},FALSE),-1)>0,LEFT(N8,3),
IF(IFERROR(MATCH(LEFT(N8,3),{"四日市","廿日市"},FALSE),-1)>0,LEFT(N8,4),
IF(IFERROR(MATCH(LEFT(N8,4),{"大和郡山市"},FALSE),-1)>0,LEFT(N8,5),
LEFT(N8,IFERROR(FIND("郡",N8),FIND("市",N8))))))))

回答No.2

SI299792 · ベストアンサー

前回は、サンプルデータだけでテストしたのでそこまで考えていませんでした。
１文字目は無視するようにしました。これにより、「市川市」のようなものは判別できます。「四日市市」のようなものは、登録するしかないと思います。
SplitData に追加していってください。
群は外しました。めったに出で来ないものなので、○○群が出て来たら、名前を直接入れた方がいいかと思います。
完全に変換するのは不可能です。実行後に多少手作業で直さなければならないのは仕方ありません。完全を求めるなら、ソフトの購入をお勧めします。
'
'Option Explicit
Option Compare Text
DefInt A-Z
'
Sub Macro1()
'
　　Dim SplitData As Variant
　　Dim SplitFlag As Variant
　　Dim StringCheck As String
　　Dim StringMatch As String
　　Dim IY As Long
　　Dim Parameter As Boolean
'
　　SplitData = Array("東京都,北海道,府,県", _
　　　　　　　　　　　"四日市市,市,区", _
　　　　　　　　　　　"0,1,2,3,4,5,6,7,8,9", "丁目")
　　SplitFlag = Array(True, True, False, True)
　　Application.ScreenUpdating = False
'
　　For IY = 8 To Cells(Rows.Count, "V").End(xlUp).Row
　　　　StringCheck = Cells(IY, "V")
'
　　　　For ix = 0 To 3
　　　　　　StringMatch = SplitData(ix)
　　　　　　Parameter = SplitFlag(ix)
　　　　　　StringMatch = Split1(StringCheck, StringMatch, Parameter)
　　　　　　Cells(IY, ix + 12) = StringMatch
　　　　　　ILen = Len(StringMatch)
　　　　　　StringCheck = Mid(StringCheck, ILen + 1)
　　　　Next ix
　　　　Cells(IY, ix + 12) = StringCheck
　　Next IY
　　Application.ScreenUpdating = True
End Sub
'
Function Split1(StringCheck As String, StringMatch As String, Parameter As Boolean) As String
'
　　Dim SMatch As Variant
　　Dim W1 As String
'
　　SMatch = Split(StringMatch, ",")
　　Min = 32767
'
　　For i1 = 0 To UBound(SMatch)
　　　　W1 = SMatch(i1)
'
　　　　If Len(W1) = 1 And Parameter Then
　　　　　　ins = 1
　　　　End If
　　　　ins = InStr(ins + 1, StringCheck, W1)
'
　　　　If ins > 0 Then
　　　　　　ins = ins - 1 - Len(W1) * Parameter
　　　　　　Min = WorksheetFunction.Min(Min, ins)
'
　　　　　　If Parameter Then
　　　　　　　　Exit For
　　　　　　End If
　　　　End If
　　Next i1
'
　　If Min < 32767 Then
　　　　Split1 = Left(StringCheck, Min)
　　End If
End Function

あと、
Application.ScreenUpdating = True
は入れるのなら、
End Sub
の前に入れてください。ここがプログラムの終了です。

お礼

本当にありがとうございます。
福岡県筑紫郡那珂川町中原３丁目 などの住所でしたら
福岡県　筑紫郡　那珂川町中原　　３丁目と分割なので
SplitData = Array("東京都,北海道,大阪府,京都府,県", _
"四日市市,廿日市市,市原市,市川市,区,市,郡", _
"0,1,2,3,4,5,6,7,8,9", "丁目")
,市,の後に郡を入れればいいですか。

回答No.5

SI299792

"区,市市,市,郡", _
とすればいいです。
１文字目を無視するようにしたので、市原市、市川市は不要です。
また、四日市市、廿日市市はまとめて、市市にしてもいいです。
そのままでも構わないのですが、登録数が少ない方が、登録も楽だし、実行速度も速いかと思います。
もう、実行は終わっているかもしれませんが。

お礼

市原市などは不要でしたか。
助かりました。今までありがとうございます。

回答No.4

SI299792

もう１つミスが
　　For IY = 8 To Cells(Rows.Count, "V").End(xlUp).Row
は
　　For IY = 5 To Cells(Rows.Count, "V").End(xlUp).Row
です。
これが開始位置です。

お礼

ありがとうございます。

回答No.3

SI299792

間違いがありました。
　　　　　　　　　　　"四日市市,市,区", _
は
　　　　　　　　　　　"四日市市,区,市", _
にしてください。
そうすれば、区が優先されるので、
京都市中京区で分割されます。

お礼

御親切にありがとうございます。

回答No.1

bunjii

>分割の仕方が変わりました。
>変更前：「都道府県」「市区町村郡」「通称名」「○丁目」「番地ビル」
>変更後：「都道府県」「市区郡」「町域名」「○丁目」「番地ビル」
前回の回答内容で無理なことを理解できていると思いましたが未だご理解頂けませんか？
都道府県は限られた数ですが市区町村は無数にあり、名前も種々雑多のためデータベースが無いと区切り位置を検出できません。
日本郵便が提供している郵便番号データをデータベースとして活用する方法で区分することをお勧めします。
「都道府県」「市区町村」「大字（地名）」「番地（丁目、番、号）」
「ビルまたはマンション、階、部屋番号」については番地との間に区切り文字が無いと区分が困難です。

>関数だとなんとなくできましたがVBAだとわかりません。
関数で本当にできましたか？
添付画像はExcel 2013で「郵便番号データ」をデータベースとして関数で処理した結果です。
M6=VLOOKUP($W6,[郵便番号データ.xlsx]KEN_ALL!$C:$X,COLUMN()-8,FALSE)
M6をN6へコピペしました。
O6=SUBSTITUTE(LEFT(V6,MIN(INDEX(FIND({"1";"2";"3";"4";"5";"6";"7";"8";"9"},V6&"123456789"),0))-1),M6&N6,"")
P6=SUBSTITUTE(V6,M6&N6&O6,"")
M6:P6を選択して下へ必要数コピーしました。
V列の6行目以下に住所が入力されているものとし、アドインで住所から郵便番号に変換してW列へ収録しました。
添付画像の上段が「郵便番号データ」の全国版です。
下段が検証したブックです。
「郵便番号データ」では都道府県、市区町村、地名（大字）のように区切られていますので、郡での区切りは不適切かと思われます。
尚、V列にビル名が付いているときは番地のセルにビル名も含まれた状態になります。