excelの置き換え

excel2010を使用しています。
住所に含まれる番地の最初の数字だけを残し、ハイフン以外の数字をアスタリスクに変換。
さらに、番地の後に記載されているビル名を削除するという置き換えを一括で行いたいです。
何か方法はありませんでしょうか。

番地の数字やハイフンは全角の場合と半角の場合があり、
できればこれらも半角に統一したいです。


【置換前】
東京都台東区0-0-0 AAAビル
東京都豊島区１-１-１
神奈川県横浜市2-1-5 BBBビル

【置換後】
東京都台東区0-*-*
東京都豊島区1-*-*
神奈川県横浜市2-*-*

No.5 回答者： tom04 回答日時： 2013/05/27 19:52

No.4です！

たびたびごめんなさい。

前回のコードで最後から3行目
＞Cells(i, 2) = Left(Cells(i, 2), cnt + 1)
の行を
＞Cells(i, 2) = Left(Cells(i, 2), cnt)
に訂正してください。

前回のコードですと、1文字余計なものが含まれてしまいます。

何度も失礼しました。m(_ _)m

No.4 ベストアンサー 回答者： tom04 回答日時： 2013/05/27 19:43

こんばんは！

VBAになってしまいますが、一例です。
データはA列の1行目からあるとします。
B列に表示するようにしてみました。

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

Sub Sample1() 'この行から
Dim i As Long, k As Long, cnt As Long, str As String
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
Cells(i, 2) = StrConv(Cells(i, 1), vbNarrow)
cnt = 0
For k = 1 To Len(Cells(i, 2))
str = Mid(Cells(i, 2), k, 1)
If str = "-" Then
cnt = cnt + 1
ElseIf str Like "[0-9]" And cnt > 0 Then
Cells(i, 2) = WorksheetFunction.Replace(Cells(i, 2), k, 1, "*")
End If
Next k
For k = Len(Cells(i, 2)) To 1 Step -1
str = Mid(Cells(i, 2), k, 1)
If str = "*" Then
cnt = k
Exit For
End If
Next k
Cells(i, 2) = Left(Cells(i, 2), cnt + 1)
Next i
End Sub 'この行まで

こんな感じではどうでしょうか？m(_ _)m

この回答へのお礼

できました！すばらしいです！
質問する時は正直解決すると思っていなかったので、驚いております。

ありがとうございました！

お礼日時：2013/05/28 09:35

No.3 回答者： kikitaiOK 回答日時： 2013/05/27 18:07

おかしいですね・・・

私の環境では正常に動作しています。

よろしければ"-*-*-*"になったところの関数をコピペしていただけないでしょうか？

この回答への補足

再度試してみたところ、1-1-1の場合はうまくいきました。
ですが、番地の値を変更すると下記のような結果になってしまいます。

B1に入力
=LEFT(A1,FIND({"１","２","３","４","５","６","７","８","９","０"},
JIS(A1)&"１２３４５６７８９０")) & "-*-*"

A1 東京都台東区2-2-2 AAAビル

結果⇒東京都台東区2-2-2 AAAビル-*-*


B3に入力
=LEFT(A3,FIND({"１","２","３","４","５","６","７","８","９","０"},
JIS(A3)&"１２３４５６７８９０")) & "-*-*"

A3 東京都台東区3-3-1 AAAビル

結果⇒東京都台東区3-3-1-*-*

補足日時：2013/05/27 18:25

この回答へのお礼

もしかしたら私の記述ミスか、環境の問題なのかもしれません。

とりあえず他の方の回答で解決してしまったため、
質問を打ち切らせていただきます。

ありがとうございました。

お礼日時：2013/05/28 09:38

No.2 回答者： kikitaiOK 回答日時： 2013/05/27 16:53

=LEFT(A1,FIND({"１","２","３","４","５","６","７","８","９","０"},

JIS(A1)&"１２３４５６７８９０")) & "-*-*"

関数中のA1がセルの位置ですので住所のセル位置に変更してください。

この回答へのお礼

ありがとうございます。
あと一歩なのですが、～ビルの部分が「-*」に変換されてしまいます。
ビルは削除したいです。


東京都台東区1-1-1 AAAビル
⇒東京都台東区1-*-*-*

こうしたい
⇒東京都台東区1-*-*

お礼日時：2013/05/27 17:37

No.1 回答者： esupuresso 回答日時： 2013/05/27 13:28

セルA1に住所が入力されているとして・・・

Excel2007以上の場合
隣のセルに　=IF(A1="","",IFERROR(LEFT(A1,FIND("区",A1)+2)&"*-*",LEFT(A1,FIND("市",A1)+2)&"*-*"))
の式を入れて下へコピー

Excel2003以下の場合
=IF(A1="","",IF(ISERROR(LEFT(A1,FIND("区",A1)+2)&"*-*"),LEFT(A1,FIND("市",A1)+2)&"*-*",LEFT(A1,FIND("区",A1)+2)&"*-*"))
の式を入れて下へコピー

取り敢えず"市"と"区"の場合だけを想定してます。

この回答へのお礼

変換できました。素晴らしいです。

説明不足で申し訳ないのですが、
実際のデータは番地の前に町名が入ることが多いので、区や市で終わることはほとんどありません。

その場合の変換は難しいでしょうか？

お礼日時：2013/05/27 14:39