エクセルで１列にあるデータを郵便番号変換ウィザードで出てくる字名までとそれ以外との２列に分離したい

質問

市町村合併で住所録の市町村名を変更する必要があります。今、Ａ１：４２４－００８２、Ｂ１：静岡県清水市旭町１－１を、Ｃ１：静岡県静岡市清水旭町、Ｄ１：１－１にしたいと思います。Ｃ１はＡ１を元に郵便番号変換ウィザードで変換できるのですがＤ１の入力が面倒です。Ｂ１を字部分と番地部分で分離するうまい方法はありませんか。数が多くてかないません。

回答No.1

moon_piyo · ベストアンサー

B1の番地部分というのが
左から探して最初に数字が出現した位置以降のもの、で問題ないのでしたら、こんな感じで...

数字が含まれないときは空白になります

=MID(B1,MIN(1+LEN(B1),IF(NOT(ISERROR(FIND({"０","１","２","３","４","５","６","７","８","９","0","1","2","3","4","5","6","7","8","9"},B1))),FIND({"０","１","２","３","４","５","６","７","８","９","0","1","2","3","4","5","6","7","8","9"},B1))),LEN(B1))

お礼

早速ありがとうございます。
できました。また、半角と全角も考慮していただき感謝しています。使い勝手が良くなって長宝します。

回答No.11

gutugutu

#2です。何度もすみません、
>PHONETICはかな変換だけでなく郵便番号にも変換できるのでしょうか。

#5で紹介したＵＲＬにも書いて有りますが、住所を郵便番号に変換する訳では有りません。
あくまで郵便番号は入力しないといけないです。
市町村合併の新住所にはまだ変換ウィザードの対応が出来ていない所は有ると思います。

お礼

ありがとうございました。漢字変換と同じですね。

回答No.10

BLUEPIXY

#7>…とやっていますが変化しません。
まず、標準モジュールに貼り付けをしてから
=番地(B1)
と入力してみて下さい。
後、計算結果が変化しないようなら
F9キーを押してみて下さい。

お礼

ありがとうございました。
エクセルを再起動したらちゃんと計算されていました。

回答No.9

imogasi

#6です。エラーが出ると言うことですが、＃３の例でもう一度やってみましたが上手く行きました。結果は
山田町
平和市城東区岡町
新町町
新町掘り
キドヤ
東京都千代田区丸の内
東京都千代田区有楽町南
になりました。
’－－－－－
注意点は
●式は
=LEFT(A1,MIN(IF(ISERROR(FIND(G$1:G$9,A1)),99,FIND(G$1:G$9,A1)))-1)
と入れて、SHIFT+CTRL+ENTERの３つを同時に押します。
その後その式を下方向に複写します。
●思うことは、G1:G9について
（A)書式を文字列にして
（B)全角数字１から９を（文字として）G1からG9までに入力しておくこと。
●SHIFT+CTRL+ENTERの３つを同時に押すこと。
などですが。

お礼

ご丁寧にありがとうございます。
指導いただいたとおりやっているのですが、どうも駄目です。ご迷惑をおかけしましたのでこの辺で終わりにしたいと思います。失礼いたしました。

回答No.8

gutugutu

#2です。自分でもやってみました、住所が先に入力されてから紹介したやり方だと、確かにその住所のカナが表示されます。
新たな住所を入力するとして
A1に=PHONETIC(C1)と入力
c1に郵便番号を入力し住所に変換
これでA1に郵便番号が表示されると思いますが？
特別な設定はしなくても、会社のパソコンでも自分のでも
ちゃんと出来ますので、これでうまくいくはずなんですが。

お礼

調べて下さりありがとございます。
ただ、Ｃ１が静岡県静岡市清水旭町だとＡ１も静岡県静岡市清水旭町と表示されて郵便番号に変換されません。=PHONETICはかな変換だけでなく郵便番号にも変換できるのでしょうか。何かやり方が悪いのでしょうかうまくいきません。

回答No.7

BLUEPIXY

>Ｄ１も指定しましたが、残念ながらどうもうまく変化しません
うまく行かないようなら、また直したいと思いますので、どのようにうまく行かないか教えて頂けませんか？
自動的に、再計算しないとかそういうことでしょうか？

あと
D1のセルに
=番地(B1)
と入力するというような意味合いで書いたのですけど
Ｄ１を指定するとはどういう意味でしょうか？

お礼

お尋ねいただきありがとうございます。マクロをよく知らないためにできないのだと思います。
私のやり方はどこかが間違っているのでしょうが、やり方がわかりません。恥ずかし話ですが教えてください。
Ｄ１に「=番地(B1)」と入力
ツール＞マクロ＞visual　basic　editor＞白い画面に教えていただいた「’住所の～End　Function」を貼り付け＞右上の×で画面を閉じる
とやっていますが変化しません。

回答No.6

imogasi

#3です。間違い（不適当）を訂正させて下さい。
=LEFT(A1,MIN(IF(ISERROR(FIND(G$1:G$9,A1)),9,FIND(G$1:G$9,A1)))-1)
を
=LEFT(A1,MIN(IF(ISERROR(FIND(G$1:G$9,A1)),99,FIND(G$1:G$9,A1)))-1)
に9－＞99に訂正してやってみてください。
99とおくのは、わざと最大値にして、ＭＩＮ関数がそれを拾わないようにしてます。９９文字以上はないものと仮定して入ることになります。簡単な短い例でやっていて、訂正洩れをしました。
=MIN(IF(ISERROR(FIND(G$1:G$9,A1)),9,FIND(G$1:G$9,A1)))
も=MIN(IF(ISERROR(FIND(G$1:G$9,A1)),99,FIND(G$1:G$9,A1)))
に訂正します。

お礼

訂正連絡をありがとうございます。
ただ、Ｃ１は山田町２－３、Ｄ１は#VALUE!となってしまいます。色々試してくださっているのに申し訳ありません。

回答No.5

gutugutu

＃２です、私は下記を見てこれは便利と思いやってみたらちゃんと出来たんですが┐(^-^;)┌?

PHONETICはふりがなを表示させるのがもともとの機能みたいですので・・・・
PHONETIC　郵便番号と検索するといろいろヒットしますので何かヒントがあると思います。

参考URL：

http://www.winboys.com/howtoxls/zipcode.htm

回答No.4

BLUEPIXY

ユーザー定義関数を作ってみました
'住所の最後の番地部分をとりだす
Public Function 番地(str As String) As String
dim i As Integer, count As Integer
i = Len(str): count = 0
Do While InStr("0123456789-０１２３４５６７８９－", Mid(str, i, 1)) <> 0
count = count + 1
i = i - 1
Loop
番地 = Right(str, count)
End Function

標準モジュールで貼り付けして
ワークシート上で
D1
=番地(B1)
のようにして使います。
番地として使用する文字の種類で漢数字などがある場合には、instrの部分の文字列を追加して下さい。

お礼

ご回答をありがとうございます。
マクロで組んでいただきありがとうございます。
しかし、私はマクロがさっぱりわからず、なんとか標準モジュールに貼り付けし、Ｄ１も指定しましたが、残念ながらどうもうまく変化しません。折角つくっていただいたのですが、活用できず悔しい思いです。

回答No.3

imogasi

配列数式でも出来ました。
（例データ）
山田町２－３
平和市城東区岡町３２－２３
新町町３４－４５
新町掘り２－３５
キドヤ３－２
東京都千代田区丸の内２３－２
東京都千代田区有楽町南２３‐３
それにどこでも良いが
Ｇ１：Ｇ９に１，２、３，４・・９を入れる。０で始まる
丁目・番地はないとします。
（関数式）
例えばＣ１に
=MIN(IF(ISERROR(FIND(G$1:G$9,A1)),9,FIND(G$1:G$9,A1)))
と入れて、ＳＨＩＦＴキーとＣＴＲＬキーを左手指で押さえ、右手でＥＮＴＥＲキーを押す。
（結果）数字（全角）が現れた文字数目を表示する。
4
9
4
5
4
11
12
（結果）
別列例えばＤ１に関数式=LEFT(A1,C1-1)と入れて下方向に
式を複写する。
山田町
平和市城東区岡町
新町町
新町掘り
キドヤ
東京都千代田区丸の内
東京都千代田区有楽町南
Ｃ，Ｄ列はわかりやすく別列にしたが
=LEFT(A1,MIN(IF(ISERROR(FIND(G$1:G$9,A1)),9,FIND(G$1:G$9,A1)))-1)
と入れてＳＨＩＦＴ＋ＣＴＲＬ＋ＥＮＴＥＲで１列で出来る。
半角数字もありうるならＧ列を下に1,2,・・・9を追加すると出来ると思う。

お礼

ご回答をありがとうございます。
やり方がわかりやすいのですが、どうも「ＳＨＩＦＴキーとＣＴＲＬキーを左手指で押さえ、右手でＥＮＴＥＲキーを押」しても変化ありません。私が使用するのはＥＸＣＥＬＬ２００２ですが、何か違うキーになるのでしょうか？

回答No.2

gutugutu

直接の回答ではないので余計なお世話ですが、
A1に=PHONETIC(C1)と入力してC1に郵便番号変換ウィザードで変換すると、A1には自動的に郵便番号が表示されて便利です。

お礼

ご回答をありがとうございます。
Ａ１にはＣ１のカナしか入りませんが、設定方法が悪いのでしょうか？