Excel関数、VBAで可能か教えてください

いつもこちらの識者の皆様にはお世話になっております。
Excelのことで質問させてください。

A1-A3セルに下記のようなデータがあります。

東京都千代田区千代田１丁目1-1日本マンション 101
東京都千代田区千代田１丁目1-2
東京都千代田区千代田１丁目2

これをそれぞれ、B列に号地までの住所、C列に物件名と部屋番号に分けたいのですが、関数もしくはVBAで可能でしょうか？
定義としては、
1.物件名と部屋番号の間には必ず半角スペースがある。
　（半角スペースを含まないセルはそのままB列にデータをコピーでOK）
2.番地と号地は半角数字、物件名は全角。

一戸建てのデータはifで半角スペースを含まないものをそのまま持ってくればいいんですが、集合住宅の場合、どのようにしたらできるのか分からず行き詰っています。
vbaでsplを使うことも考えましたが、うまくいきそうにありません。

どなたか、上記内容の場合どのような関数、もしくは構文が適しているか教えていただけませんでしょうか。
よろしくお願いいたします。

No.3 ベストアンサー 回答者： nicotinism 回答日時： 2013/05/10 23:23

A列に連続してデータが入っている場合です。

B・C列に書き出しますので、開けておいてからこのマクロを実行してみてください。
多分例外的なモンがあるでしょうから最終的には目視で確認ですね。
少しは役に立つかも？
※[半角数字][全角文字]と繋がっている部分を探してます。

Sub SS()
　　Dim oReg As Object, m As Object, mc As Object
　　Dim i As Long
　　Dim rgVal As String
　　
　　Set oReg = CreateObject("vbscript.regExp")
　　
　　With oReg
　　　　.Pattern = "[0-9][^ -~。-゜]"
　　　　.Global = True
　　End With
　　
　　For i = 1 To Range("A1").End(xlDown).Row
　　　　rgVal = Range("A" & i)
　　　　If oReg.test(rgVal) Then
　　　　　　Set mc = oReg.Execute(rgVal)
　　　　　　For Each m In mc
　　　　　　　　Range("A" & i).Offset(0, 1) = Left(rgVal, InStr(rgVal, m.Value))
　　　　　　　　Range("A" & i).Offset(0, 2) = Mid(rgVal, InStr(rgVal, m.Value) + 1)
　　　　　　Next
　　　　Else
　　　　　　Range("A" & i).Offset(0, 1) = rgVal
　　　　End If
　　Next
　　Set oReg = Nothing
　　MsgBox "おしまい"
End Sub

この回答へのお礼

ありがとうございます。
本当のデータでもできました！
助かりました。

お礼日時：2013/05/25 18:27

No.4 回答者： tom04 回答日時： 2013/05/10 23:37

こんばんは！

VBAになってしまいますが、一例です。

前提条件として
A列データは
質問文にあるように「物件名」の前は必ず数値だというコトで・・・

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, k As Long, str As String
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If InStr(Cells(i, 1), " ") > 0 Then
k = WorksheetFunction.Find(" ", Cells(i, 1))
Do Until StrConv(str, vbNarrow) Like "[0-9]"
str = Mid(Cells(i, 1), k, 1)
k = k - 1
Loop
With Cells(i, 2)
.Value = Left(Cells(i, 1), k + 1)
.Offset(, 1) = Mid(Cells(i, 1), k + 2, Len(Cells(i, 1)))
End With
Else
Cells(i, 2) = Cells(i, 1)
End If
Next i
Columns.AutoFit
End Sub 'この行まで

※　最初に書いた前提条件が合わない場合はご希望通りの表示になりません。m(_ _)m

この回答へのお礼

ありがとうございます。
例題の3行では間違いなくできたんですが、いざ本当のデータでやってみるとうまくいきませんでした。
今回はNo.3の方をベストアンサーに選ばせていただきます。

お礼日時：2013/05/25 18:26

No.2 回答者： STICKY2006 回答日時： 2013/05/10 21:09

試してません。

参考程度に。

区切り位置機能。
http://kokodane.com/tec3_39.htm

文字列操作関係の関数を駆使する。
http://www.eurus.dti.ne.jp/yoneyama/Excel/waza/m …

例）
Left(A1,FIND(半角スペース,A1,0))
「A1の、半角スペースが入ってる位置を先頭から探して、その数字を返して、左端からその文字数分取得する」

未確認なので、多少間違えてるかもですが。
後は、「駆使」すれば、「真ん中だけ取得する」とか、「右側だけ取得する」とか可能です。
LENやMID、LEFTやRIGHTを入れ子入れ子入れ子にしてがんばりましょう。


VBAでやるなら、Split関数
http://officetanaka.net/excel/vba/tips/tips62.htm

でも、そこまでやる必要は無いと思う。
マクロは、最終手段。です。

この回答へのお礼

ありがとうございます。

お礼日時：2013/05/25 18:19

No.1 回答者： JazzCorp 回答日時： 2013/05/10 20:58

ナンセンス！！

住所なんて、ナンデモありの世界、いくらガンバッテモ、．．.カモしれない程度の確率、だからマンション名の前に、コツコツと特殊な文字でも挿入した方が仕事は早い、、、

■05.02.28_Excel：住所を区切る
http://www.geocities.jp/chiquilin_site/data/0502 …

この回答へのお礼

ありがとうございます。リンク先参考にさせていただきます。

お礼日時：2013/05/25 18:19