エクセルで住所と郵便番号を別のセルに切り出したい

質問

下記を別々の列に切り出したいのですが、教えてください。

(1)
Ａ列　
【ＸＸＸＸＸＸＸＸＸ】　　　
【ＸＸＸＸＸＸ】　
【ＸＸＸＸＸＸＸＸ】　

Ａ列は　、ＸＸＸＸＸの部分だけＢ列に抜き出したい。

(2)
Ｅ列
北海道　〒004-1234　札幌市ＸＸ区ＸＸＸＸ1条2-3-4
秋田県　〒011-1234　秋田市ＸＸＸＸ1-1-1
群馬県　〒373-1234　太田市ＸＸＸＸ８７５－１

は、
Ｆ列に郵便番号を(〒のマークはいらない）

Ｇ列に　　北海道札幌市ＸＸ区ＸＸＸＸ1条2-3-4
　　　　　秋田県秋田市ＸＸＸＸ1-1-1

と、はじめの県名と○○市～をつなげて、完全な住所にしたい。

のですが、式を教えてください。

回答No.1

mshr1962 · ベストアンサー

先頭と最後の【】を削除するだけなら
B2=MID(A2,2,LEN(A2)-2)
〒の後の8桁を抽出
F2=MID(E2,FIND("〒",E2)+1,8)
全角スペースで囲まれた部分を除くなら
G2=LEFT(E2,FIND("　",E2)-1)&MID(E2,FIND("　",E2)+11,LEN(E2))

お礼

瞬時に、とても的確な回答をありがとうございました。しかも、初心者の私にそのまま使えるよう、セルの番地まで考慮して教えてくださり、感激です。
(2)の作業は、あっという間に完了しましたが、

(1)の作業ですが、　Ｂ２に　=MID(A2,2,LEN(A2)-2)　
と貼り付ければいいのですよね？
はじめの【　は、取れても、最後の　】　が残ってしまうのですが・・・。もう一度、教えていただけますでしょうか？

回答No.6

mshr1962

#1のmshr1962です。
＞はじめの【　は、取れても、最後の　】　が残ってしまうのですが・・・
】の後にスペースがあるようですね。
B2=MID(TRIM(A2),2,LEN(TRIM(A2))-2)
としてみてください。

お礼

ホントに、すごい！！！　お見事です。
これで、全て解決です。
６００件ほどのデータでしたので、何日かかるか・・と心配していました。本当に、本当にありがとうございました。

回答No.5

telescope

簡単にやるなら「データ」－「区切り位置」で出来ると思います。

後の作業も考慮して、4列ほど空白列を挿入します。
（BからEまで選択して右クリックで「挿入」）
A列に
【ＸＸＸＸＸＸＸＸＸ】
も残したいなら、A列をB列にコピーして貼り付けます。
B列を選択して、「データ」－「区切り位置」で
［カンマやタブなどの～］にチェックして［次へ］ボタンを押します。
［その他］を選んでとなりの欄に【　を入力します。
［完了］ボタンを押します。
C列に
ＸＸＸＸＸＸＸＸＸ】
のようになります。
C列を選択して、
同じく「データ」－「区切り位置」で［その他］の欄に　】　を入力します。
C列にＸＸＸＸＸの部分だけが残ります。これをB列に貼り付けます。
不要な列を削除します。

(2)の方ですが、上記の作業をした後は列がずれていると思いますのでF列として説明します。
例を見ると、県名と郵便番号、郵便番号とその他の住所の間にそれぞれスペースが入っているように見えます。もしそうなら、
上記と同じく「データ」－「区切り位置」で［スペース］にチェックを入れ、その他に　〒　を入れて完了ボタンを押します。
G列にもデータが入っているならあらかじめ空白列を挿入しておきます。
これでG列に県名、H列に郵便番号(〒のマークなし）、I列に市以下の住所と分かれます。
J1セルに
=G1&I1
と式を入れて、下方向にコピーします。
セルを選択するとセルの周りが線で囲まれて、右下の部分に小さな■がありますが、それをダブルクリックすると隣の列の最後までコピーされます。

もし、県名と郵便番号の間にスペースがないのなら、
「データ」－「区切り位置」で［その他］に　〒　を入れて完了ボタンを押すと、県名と郵便番号以下に分かれますので、
I1セルに
=LEFT(H1,7)
J1セルに
=MID(H1,8,50)
と式を入れて下方向にコピーします。

お礼

とても、丁寧に説明してくださり、ありがとうございました。難しい関数がわからなくても、「データ」－「区切り位置」の技があるのですね。これを、覚えれば、人に頼らずできすね。

回答No.4

imogasi

（２）は
E列を、データー区切り位置ースペースでE,F,G列に分離し、
F列の〒は置換で消す。
G列は、一旦、=F1&H1をI列につくり、I列を値に固定化して（形式を選択して貼り付け-値)、G列に持っていく（コピー）
（１）は使わないでよいと思う。
最初のスペースの次から、最後までの中で、最初の最初のスペースまでを抜き出せば関数でできるが、長くなる。
ーーーー
それにしても、質問がわかりにくい。（１）と（２）の関係がわかりにくい。（１）は（２）のことを抽象化したものか？
（１）で質問文において、３段にXXXが表現されているが、それが何かと、それに気を回してしまう。

回答No.3

noname#22222

s_husky です。

勘違いでした。
単に、郵便番号を抽出するだけの質問でした。
No2は、ゴミとして無視して下さい。

回答No.2

noname#22222

目茶、面倒な質問ですね！
先ず、Excel の変換機能を使って、

北海道　〒004-1234　札幌市;ＸＸ;区:ＸＸＸＸ:1条2-3-4
秋田県　〒011-1234　秋田市;ＸＸＸＸ;1-1-1
群馬県　〒373-1234　太田市;ＸＸＸＸ;８７５－１

とデータを変更すれば、容易に抽出と合成が出来ると思いますが...