こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

Excel2010 住所5分割

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


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

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

投稿日時 - 2016-09-21 14:01:14

QNo.9232196

すぐに回答ほしいです

質問者が選んだベストアンサー

空白があれば、エラーになります。分かっていたのですが、情報がほしかったので、返事を待つことにしました。5行目から実行するようにして、空白があっても止まらないようにしました。
'
'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行まで実行されないのはなぜですか。」どのような状況なのでしょうか。もし、上のプログラムが動かなければ、もっと詳しく状況を書いてください。

投稿日時 - 2016-09-23 16:43:34

お礼

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

投稿日時 - 2016-09-23 16:50:51

ANo.10

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(8)

ANo.9

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

「あと試してみたところ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

投稿日時 - 2016-09-23 11:59:31

補足

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

投稿日時 - 2016-09-23 14:01:19

お礼

ごめんなさい。
データは一部を載せただけだったので
元データがそうなってます。
M4~Q4、V4にタイトルが入ってます。
M5~Q5からそれぞれ分割したのを入れます。
V5空白
V6空白
V7空白
V8東京都豊島区東池袋3丁目1番1号
V9空白
V10東京都港区港南2丁目16番1号
V11神奈川県厚木市恩名2丁目1番23号
V12東京都新宿区百人町2丁目20番2号


・という感じです。
実行したところ実行エラー5がでますが分割できます。

投稿日時 - 2016-09-23 13:55:11

ANo.8

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

住所が何列にあるのか、何行目からデータがあるのか書いてありません。
K列に住所があり、2行目にからデータが始まると仮定しました。
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-:区切り文字のうち、先のものを有効とする。
'      +:区切り文字のうち、後のものを有効とする。
'      1:区切り文字を出力する。
'      2:区切り文字を出力しない。
  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を実行するなら、この処理は入りません。

投稿日時 - 2016-09-22 08:47:02

お礼

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

投稿日時 - 2016-09-23 09:34:51

ANo.5

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

完全ではありません
(1)都道府県が省略されている場合その後に「都道府県」のどれかの文字があれば、そこまでを都道府県とします。市区町村郡も同じです。
例:金沢市県坂長→金沢市県が都道府県になる。
(2)市区町村群から、数字までを通称名としています。したがって、文字で始まる。丁目、番地があれば正しく正しくできません。
私の家はイ33番地ですが、イが通称名となり、33番地が番地になります。
通称名に数字があれば、正しく反映されません。
'
'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)
と入力し、下へコピペしてください。

投稿日時 - 2016-09-21 19:27:54

お礼

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

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

投稿日時 - 2016-09-23 09:33:45

ANo.4

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

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

投稿日時 - 2016-09-21 18:32:15

お礼

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

投稿日時 - 2016-09-23 09:22:32

ANo.3

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

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

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

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

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

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

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

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

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

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

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

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

投稿日時 - 2016-09-21 18:18:27

お礼

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

投稿日時 - 2016-09-23 09:21:25

ANo.2

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

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

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

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

までは分割できます。

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

投稿日時 - 2016-09-21 14:49:11

補足

比較の謎は解けました。

投稿日時 - 2016-09-21 15:12:24

お礼

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

投稿日時 - 2016-09-21 14:59:57

ANo.1

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

なので

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

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

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

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

投稿日時 - 2016-09-21 14:20:06

お礼

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

投稿日時 - 2016-09-21 14:24:47

あなたにオススメの質問