Excelで郵便番号のみを別の列へ移動させる

質問

1列内に住所情報が入力されていいます。
たとえば、
（B列）
〒123-4567 A県B市XXX
という具合です。
郵便番号と住所の間にはスペースが空いています。
なかには郵便番号なしで住所から始まるものもあります。

これを、郵便番号だけを別の空の列（A列）に移動させたいです。（元の列からは郵便番号を削除）
関数等、便利に実行できる方法を教えてください。

回答No.3

mimazoku_2 · ベストアンサー

例えば、こんなデータで試しました。

Ｃ列のデータ：〒123-4567 東京都謎々区質問１丁目２番地３号 オーケイウェイブ株式会社

Ａ列の数式：=LEFT(C2,FIND(" ",C2))

Ｂ列の数式：=MID(C2,FIND(" ",C2),LEN(C2))

結果は添付図のようになります。

【説明】

Ａ列の数式
・ＦＩＮＤ関数で” ”半角スペースまでの長さを出す。
・ＦＩＮＤ関数の返り値をＬＥＦＴ関数に代入し、郵便番号部分の抽出を行う。

Ｂ列の数式
・ＬＥＮ関数で文字数を数えます。
・ＦＩＮＤ関数で” ”半角スペースまでの長さを出す。
・ＭＩＤ関数に開始位置をＦＩＮＤ関数で与え、抽出文字数はＬＥＮ関数を与える。
これで、文字数が変化しても追従出来ます。

Ａ列・Ｂ列には、分離されたデータが表示されますので、Ａ列をコピーし、値のみペーストすれば、目的を達成出来るかと思います。

お礼

ありがとうございます。
素晴らしいです。
もう一歩、条件で上げていなかったですが、郵便番号後のスペースは、半角のほか、全角のものが混ざっています。
半角パターンと全角パターンで2つの関数をORでつなげたらどうかなと思ったのですがエラーで。。。
基本的なことかも知れませんが、方法はありますでしょうか。困難でしたら2回別の関数をかけようと思います。

回答No.7

mimazoku_2

え～と、考えてみましたがそれ以上の改良は無理のようです。

理由として、関数によって、半角スペースと全角スペースの区別があったりなかったりとするためです。
なので、手順としては、その数式の入ったシートにデータをコピーした後、文字列置換で半角スペースに揃えてください。
ＩＦ関数なども用いましたが、ＢＡＳＩＣのように論理的な判断が出来ていません。

私の力では、これが限界です。

回答No.6

bunjii

>これを、郵便番号だけを別の空の列（A列）に移動させたいです。（元の列からは郵便番号を削除）
発想を変えてください。
B列に郵便番号と住所が半角の空白で区切られた文字列で入力されている状態に統一することから始めれば良いでしょう。
現在A列はすべて未入力とすればA列を削除することで”[郵便番号] [住所]”のがA列になります。
これを空白を区切り記号として「データ」の「区切り位置」で区分すればA列が[郵便番号]、B列が[住所]に置き換わります。

>なかには郵便番号なしで住所から始まるものもあります。
先頭に空白があれば上記の方法で[住所]がB列へ配置されます。
先頭に空白が無い場合は個別に手作業でセルを挿入して右にシフトさせれば良いでしょう。

回答No.5

kagakusuki

郵便番号の次に来るスペースが全角である場合や半角である場合のどちらであっても有効で、尚且つ、「〒」が付いているか否かにも影響されず、文字列の先頭の所に余分なスペースがある場合や、郵便番号自体が全角の数字と半角の数字のどちらであっても影響されず、また、郵便番号の桁数が7桁であっても、5桁であっても、3桁であっても影響されず、郵便番号の途中にある「-」(ハイフォン)が全角文字である場合や半角文字である場合、「―」(全角のダッシュ)である場合、「ー」(全角の長音記号)である場合でも、「(『〒』マーク無し)『前半部分の数字』+『半角文字のハイフォン』+『後半部分の数字』」に直した形で郵便番号を取り出す事が出来る方法です。
　尚、

>元の列からは郵便番号を削除

という処理に関しては関数では行う事が出来ませんので、取り敢えず仮の話として、「郵便番号だけ」をA列のセルに、「郵便番号を除いた住所」をC列のセルに表示させるものとします。

　まず、A1セルに次の関数を入力して下さい。

=IF(ISERROR(1/(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(TRIM(ASC($B1)),FIND(" ",TRIM(ASC($B1))&" ")-1),"〒",),"‐","ー"),"―","ー"),"-","ー")=TEXT(REPLACE(MID(LEFT(TRIM($B1),FIND(" ",TRIM(ASC($B1))&" ")-1),1+(LEFT(TRIM($B1),1)="〒"),10),4,1,)+0,"[<=999]000;[<=9999]000ー00;000ー0000"))),"",TEXT(REPLACE(MID(LEFT(TRIM($B1),FIND(" ",TRIM(ASC($B1))&" ")-1),1+(LEFT(TRIM($B1),1)="〒"),10),4,1,)+0,"[<=999]000;[<=9999]000-00;000-0000"))

　次に、A1セルをコピーして、A2以下に貼り付けて下さい。
　次に、C1セルに次の関数を入力して下さい。

=IF($B1="","",REPLACE(TRIM($B1),1,FIND(" ",TRIM(ASC($B1))&" ")*($A1<>""),))

　次に、C1セルをコピーして、C2以下に貼り付けて下さい。

　以上です。

回答No.4

mimazoku_2

入れ違いになってすいません。
教えて頂いたデータでもサクッと出来ました。

回答No.2

mimazoku_2

＞もう少し簡単に実現できる方法があればうれしいです。

考えてみたいので、何か例題がありますか？
ダミーデータでいいので２～３件程度。
それと、入力されている規則なんかが分かるとうれしいです。

ｐ．ｓ　私の原始的な方法は、ＯＳやアプリのバージョンを選ばないので覚えておいて損はありませんよ。（人間がこなすから・・苦笑）

補足

ありがとうございます。
たとえば、
〒111-1111 平和市平和３－１－１　平和ビル５Ｆ
〒111-1111 平和市平和３丁目 ２－１平和 第二ビル
このようにたまにイレギュラにスペースが空いていたり詰まっていることがあります。

はい、教えていただいた方法も他の状況含め活用できます！
（もうちょっとさっくりできたら嬉しいです。すみません）

回答No.1

mimazoku_2

原始的な方法を１つ。

そのファイルをＣＳＶ（カンマ区切り）形式で保存します。
メモ帳で開き、スペースをカンマに置き換えします。
で、もう一度、置き換えをしますが、「，，」を「，」に置き換え。
そうすると、空白の部分にカンマ「，」が１つだけ入りますので、区切りが完成。
そのファイルを上書き保存し、エクセルで読み込めば、郵便番号と住所が分離されます。
あとは自由に編集し、エクセル形式で保存すれば、目的達成かと思われます。

もし、メモ帳でスペースの置き換えが不可能なら、ワードに読み込んでください。
置き換えは、ワードの方が多様性があります。
置き換えが完了したら、コピーし、メモ帳へ貼り付けてください。
こうすることで、ワードの特殊な記号やコントロールコードが削除され、純粋なテキストだけになります。
メモ帳へ貼り付け（全面上書き）が完了したら、拡張子がＣＳＶのまま書き込んで保存。
で、エクセルで読み込めばいい。

この原始的なやり方は、文字操作が多いときなどに役に立ちますよ。

お礼

ありがとうございます。
この方法でできました。ただ、住所内の他の箇所のスペース（ビル名等）に影響があり、最後に調整する手数が増えてしまいます。いくつもファイルがあるので、もう少し簡単に実現できる方法があればうれしいです。