excelの置き換え

質問

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

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

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

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

回答No.4

tom04 · ベストアンサー

こんばんは！
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

お礼

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

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

回答No.5

tom04

No.4です！
たびたびごめんなさい。

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

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

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

回答No.3

kikitaiOK

おかしいですね・・・

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

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

お礼

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

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

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

補足

再度試してみたところ、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-*-*

回答No.2

kikitaiOK

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

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

お礼

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

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

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

回答No.1

esupuresso

セル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)&"*-*"))
の式を入れて下へコピー

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

お礼

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

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

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