Excel2010 住所5分割

質問

東京都豊島区東池袋３丁目１番１号
東京都八王子市子安町４丁目１０番１５号
神奈川県川崎市高津区明津１２番地メゾン美鈴
とある住所を「都道府県」「市区町村郡」「通称名」「○丁目」「番地ビル」
のように分割したいです。

　M列　　　　　N列　　　　　　O列　　　　　P列　　　　　　Q列　　　
都道府県　　市区町村郡　　　　通称名　　　　丁目　　　　　　番地
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
「東京都」　「豊島区」　　　「東池袋」　　「３丁目」　　「１番１号」
「東京都」　「八王子市子安町」 　　　　　「４丁目」　　「１０番１５号」
「神奈川県」　「川崎市高津区」　「明津」　　　　　　「１２番地メゾン美鈴」

5万近く件数があり手作業では無理なので、関数かVBAで出来れば助かります。
都道府県とそれ以降の分割しかわかりません。
宜しくお願いします。

回答No.10

SI299792 · ベストアンサー

空白があれば、エラーになります。分かっていたのですが、情報がほしかったので、返事を待つことにしました。５行目から実行するようにして、空白があっても止まらないようにしました。
'
'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 SubAddress As String
　　Dim IY As Long
'
　　SplitData = Array("都,道,府,県", "市,区,町,村,郡", "", "丁目", "")
　　SplitFlag = Array(-1, 1, 0, -1, 0)
　　Application.ScreenUpdating = False
'
　　For IY = 5 To Cells(Rows.Count, "V").End(xlUp).Row
　　　　StringCheck = Cells(IY, "V")
　　　　StringMatch = Split1(StringCheck, "0,1,2,3,4,5,6,7,8,9", -1)
　　　　Parameter = Len(StringMatch)
　　　　SubAddress = ""
'
　　　　If Parameter > 0 Then
　　　　　　SubAddress = Mid(StringCheck, Parameter)
　　　　　　StringCheck = Left(StringCheck, Parameter - 1)
　　　　End If
'
　　　　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
　　Next IY
End Sub
'
Function Split1(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
End Function

「808行まで実行されないのはなぜですか。」どのような状況なのでしょうか。もし、上のプログラムが動かなければ、もっと詳しく状況を書いてください。

お礼

ありがとうございます。
このコードだと実行されました。
新たな問題が出たので改めて質問させてもらいます。

回答No.9

SI299792

「空白空白空白記入空白記入記入と空白記入がバラバラです。」
これは、どういう意味でしょうか、元データがそうなっているということですか？
それとも実行したらそうなったということですか？
サンプルデータに空白は全くありませんが。

「あと試してみたところK2しか分割されませんでした。」
これは、K1が空白セルになっていることが原因と思われます。
どっちにしろ、私が想定したデータと、実データが違っているわけですから、どっちにしろ、プログラムの修正が必要です。V8から処理し、V7が空白でも動くようにしました。
必要な情報がないのは、プログラムを作る方も困ります。

「前のはちゃんと回答されてましたよ 」
昨日は、ほかの人に表示されていませんでした。サポートで、問題なしと判断されたので、出たものと思われます。しかし、回答No.5はバグがあるので使わないでください。（八王子市子安町が、八王子市・子安町に分割されてしまう）

「丁目」など文字で判断しているので、「3-1-1 」のような書き方では、判断できません。対策として、 "丁目,-" とすれば、- も区切り文字になるので、丁目のところに、「3-」が入りますが、それでも正確な判断はできません。
また、数字かどうかで判断していますので、「三丁目」のような表記があれば正しく分割されません。
'
'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 SubAddress As String
　　Dim IY As Long
'
　　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")
　　　　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
　　Next IY
End Sub
'
Function Split1(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
End Function

お礼

ごめんなさい。
データは一部を載せただけだったので
元データがそうなってます。
M４～Q４、V４にタイトルが入ってます。
M５～Q５からそれぞれ分割したのを入れます。
V５空白
V６空白
V７空白
V８東京都豊島区東池袋３丁目１番１号
V９空白
V１０東京都港区港南２丁目１６番１号
V１１神奈川県厚木市恩名２丁目１番２３号
V１２東京都新宿区百人町２丁目２０番２号
・
・
・という感じです。
実行したところ実行エラー５がでますが分割できます。

補足

追加です。
808行まで実行されないのはなぜですか。

回答No.8

SI299792

「この投稿は、現在サポートで確認中のため、
回答は他の方には表示されなくなっております。」
の表示が出ています。
　原因がわかったので、再回答します。

住所が何列にあるのか、何行目からデータがあるのか書いてありません。
Ｋ列に住所があり、２行目にからデータが始まると仮定しました。
K1には、住所というタイトルが入っているとします。
違っていたらご記入下さい。

完全ではありません
(1)都道府県が省略されている場合その後に「都道府県」のどれかの文字があれば、そこまでを都道府県とします。
(2)市区町村郡は後に出てきた文字で判断しています。したがって、通称名、丁目、番地に
「市区町村郡」のどれかがあれば、そこまでを、市区町村名とします。
(3)市区町村群から、数字までを通称名としています。したがって、文字で始まる丁目、番地があれば正しく正しくできません。
(4)通称名に数字があれば、正しく反映されません。
'
'Option Explicit
Option Compare Text
DefInt A-Z
'
Sub Macro1()
'
　　Dim SplitData As Variant
　　Dim SplitFlag As Variant
　　Dim IY As Long
　　Dim Address As String
　　Dim StringMatch As String
　　Dim Out As Boolean
'
　　SplitData = Array("都,道,府,県", "市,区,町,村,郡", "0,1,2,3,4,5,6,7,8,9", "丁目")
　　SplitFlag = Array(-1, 1, -2, -1)
　　IY = 2
'
　　For IY = 2 To [K1].End(xlDown).Row
　　　　Address = Cells(IY, "K")
'
　　　　For ix = 0 To 3
　　　　　　StringMatch = SplitData(ix)
　　　　　　Parameter = SplitFlag(ix)
　　　　　　StringMatch = Split1(Address, StringMatch, Parameter)
　　　　　　Cells(IY, ix + 12) = StringMatch
　　　　　　Add = Len(StringMatch)
　　　　　　Address = Mid(Address, Add + 1)
　　　　Next ix
　　　　Cells(IY, ix + 12) = Address
　　Next IY
End Sub
'
Function Split1(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
　　absParameter = Abs(Parameter)
'
　　If max <> -32768 Then
　　　　max = Abs(max) - 1 - Len(W1) * (absParameter = 1)
　　　　Split1 = Left(StringCheck, max)
　　End If
End Function

なお、このマクロは、関数としても使えます。この場合Sub Macro1()はいりません。
関数としても使う場合は、
L2に =Split1($K2,"都,道,府,県",-1)
M2に =Split1(MID($K2,LEN(L2)+1,255),"市,区,町,村,郡",1)
N2に =Split1(MID($K2,LEN(L2&M2)+1,255),"0,1,2,3,4,5,6,7,8,9",-2)
O2に =Split1(MID($K2,LEN(L2&M2&N2)+1,255),"丁目",1)
P2に =MID($K2,LEN(L2&M2&N2&O2)+1,255)
と入力し、下へコピペしてください。
Macro1を実行するなら、この処理は入りません。

お礼

ありがとうございます。
前のはちゃんと回答されてましたよ

回答No.5

SI299792

住所が何列にあるのか、何行目からデータがあるのか書いてありません。
Ｋ列に住所があり、２行目にからデータが始まると仮定しました。
K1には、住所というタイトルが入っているとします。
違っていたらご記入下さい。

完全ではありません
(1)都道府県が省略されている場合その後に「都道府県」のどれかの文字があれば、そこまでを都道府県とします。市区町村郡も同じです。
例：金沢市県坂長→金沢市県が都道府県になる。
(2)市区町村群から、数字までを通称名としています。したがって、文字で始まる。丁目、番地があれば正しく正しくできません。
私の家はイ３３番地ですが、イが通称名となり、３３番地が番地になります。
通称名に数字があれば、正しく反映されません。
'
'Option Explicit
Option Compare Text
DefInt A-Z
'
Sub Macro1()
'
　Dim SplitData As Variant
　Dim SplitFlag As Variant
　Dim IY As Long
　Dim Address As String
　Dim StringMatch As String
'
　SplitData = Array("都道府県", "市区町村郡", "0123456789", "丁目")
　SplitFlag = Array(1, 1, 0, 2)
　IY = 2
　Range("L2:P" & Rows.Count).ClearContents
'
　For IY = 2 To [K1].End(xlDown).Row
　　ptr = 1
'　都道府県　市区町村郡 通称名 丁目　番地
　　For ix = 0 To 3
　　　Address = Cells(IY, "K")
　　　StringMatch = SplitData(ix)
　　　Add = SplitFlag(ix)
　　　StringMatch = Split1(ptr, Address, StringMatch, Add)
　　　Cells(IY, ix + 12) = StringMatch
　　　ptr = ptr + Len(StringMatch)
　　Next ix
　　Cells(IY, ix + 12) = Mid(Address, ptr)
　Next IY
End Sub
'
Function Split1(Start, StringCheck As String, StringMatch As String, ilen) As String
'
　Dim W1 As String
　Dim OutData As String
'
　plen = ilen - (ilen = 0)
'
　For i1 = Start To Len(StringCheck)
　　W1 = Mid(StringCheck, i1, plen)
　　ins = InStr(StringMatch, W1)
　　If ins > 0 Then
　　　Exit For
　　End If
'
　　OutData = OutData & Left(W1, 1)
　Next i1
'
　OutData = OutData & Left(W1, ilen)
'
　If i1 >= Len(StringCheck) Then
　　OutData = ""
　End If
　Split1 = OutData
End Function

なお、このマクロは、関数としても使えます。この場合Sub Macro1()はいりません。
L2に =Split1(1,$K2,L$1,1)
M2に =Split1(LEN(L2)+1,$K2,M$1,1)
N2に =Split1(LEN(L2)+LEN(M2)+1,$K2,"0123456789",0)
O2に =Split1(LEN(L2)+LEN(M2)+LEN(N2)+1,$K2,O$1,2)
P2に =MID($K2,LEN(L2)+LEN(M2)+LEN(N2)+LEN(O2)+1,255)
と入力し、下へコピペしてください。

お礼

ありがとうございます。
住所はV列の5行目からですが住所が入り始めるのは8行目です。
空白空白空白記入空白記入記入と空白記入がバラバラです。
4行目にこの題名が入ってます。
「都道府県」「市区町村郡」「通称名」「○丁目」「番地ビル」

あと試してみたところK2しか分割されませんでした。

回答No.4

bunjii

>住所を「都道府県」「市区町村郡」「通称名」「○丁目」「番地ビル」のように分割したいです。
郵便番号が記録されていればJP（日本郵便）が提供している郵便番号データと照合して都道府県、市区町村、大字（地名）を切り分けることができます。
但し、JPの郵便番号データには同じ大字が条件付きで別の郵便番号になっているケースもあるので郵便番号データを一部修正する必要があるかも知れません。
あなたが提示している「通称名」とは「大字」に相当すると思います。
尚、番地とビル名を同一セルへ収録するのは不適切かと思います。
一般的には〇丁目△△番地◇◇号のように纏めて、ビル名（マンション名）と階数と部屋番号を組み合わせるようです。
基本的にはJPが提供している郵便番号データから目的の郵便番号を使ってVLOOKUP関数で都道府県名、市区町村名、大字名を抽出する方法になります。
数式はあなたの力量で組み立ててください。

参考URL：

http://www.post.japanpost.jp/zipcode/dl/kogaki-zip.html

お礼

ありがとうございます。
力量がないので無理かもしれません。

回答No.3

Prome_Lin

私の回答は、無視して頂いて結構です。

私も、住所を扱うことがよくあるのですが、クライアントからお預かりするデータで、すでに住所のセルをくっつけてこられる方がおられます。

そのとき困るのは、「マンション名」が前の住所にくっついていることです。

質問者の持っておられる住所が、必ず、「番地」という文言が入るのでしたらいいのですが、住所によっては、「官有地」だの何だのあり、「数字」で終わることもあります。

また、住所録のマンション名などが、必ず入力されているのでしたら、まだ、いいのですが、私がクライアントからお預かりするデータは、いきなり「～号室」で、マンション名が抜けているものも散見します。

このような状況で、番地と住所をくっつけられてしまうと、「12-12345号室」となってしまいます。

私は、クライアントに「住所」と「マンション名」の間にスペースを入れて頂くように、何度もお願いしているのですが、聞いてもらえません。

私は、住所の概念から言って、「都道府県」「市町村」「その次から～丁目の前まで」「～丁目何番地何号」「マンション名」として、「マンション名」は明確に別セルにすべきだと考えています。

私が扱うデータでは、何丁目や何番地などという記載はほとんどなく、「12-34」のようになっています。

そのあたりは、大丈夫なのでしょうか？

回答でなく、申し訳ございません。

普段、住所を扱うことが多いので、気になりました。

お礼

統一されていないのが一番困りますよね～
この住所たちもハイフンや何番地などあります

回答No.2

chie65536（@chie65535）

＞自動でなくてもある程度出来れば楽になります。

ある程度、正規化されている（例えば「ヶ」「ケ」の一致不一致、「字」「大字」の一致不一致、「条」「丁目」の省略の補完など）のが前提なのであれば

郵便番号データダウンロード
http://www.post.japanpost.jp/zipcode/download.html
で住所一覧をダウンロードして比較すれば

都道府県名
市町村名
町域名（質問者さんが「通称名」と書いている部分）

までは分割できます。

それ以降の部分は「自動では不可能」です。

お礼

ありがとうございます。
比較とはどういうことでしょうか。

補足

比較の謎は解けました。

回答No.1

chie65536（@chie65535）

自動で行なうのは不可能に近いほど困難です。

なので

http://biz.kkc.co.jp/software/geo/addressmatch/function/

のように、住所正規化コンバータを開発する事によって商売が可能なほどです。

「商売になる」って事は「普通の人には無理」って事です。普通の人が簡単に出来るような事なら「商売にならない」ですからね。

諦めて、上記のような「有料サービス」や「有料ソフト」を使用しましょう。

お礼

ありがとうございます。
自動でなくてもある程度出来れば楽になります。