エクセルで2つの郵便番号を比較し、正しい方を7桁に処理して反映させたい

エクセルで郵便番号の整理をしています。

下記のようにA列とB列を比較して選び、C列に処理したものを反映させたいです。

どのように処理させれば実現できますでしょうか。


　元の〒　　住所生成した〒　　希望する結果
　　A列　　　　　　B列　　　　　C列
1、336　　　　 336-0321　　336-0321　→Aが8文字以下なのでBを反映
2、336-　　　　336-0000　　336-0000　→Aが8文字以下なのでBを反映
3、250021　　　025-0021　　025-0021　→Aが8文字以下なのでBを反映
4、542-2210　　(空欄)　　　　542-2210　→B空欄なのでAを反映
5、452-0000　　452-0312　　452-0312　→両方8文字だがAは末尾が0000なのでBを反映
6、225-0055　　225-0000　　225-0055　→両方8文字だがBは末尾が0000なのでAを反映
7、025-　　　　(空欄)　　　　025-0000　→B空欄なのでAを反映　→「-」から後ろが
　　　　　　　　　　　　　　　　　　　　　無いので「0000」を足す
8、0250021　　(空欄)　　　 025-0021　→B空欄なのでAを反映→　
　　　　　　　　　　　　　　　　　　　「-」無しの7文字なので「-」を挿入する
9、250021　　　(空欄)　　　025-0021　→B空欄なのでAを反映→　
　　　　　　　　　　　　　　　　　　　　「-」無し4文字以上6文字以下なので
　　　　　　　　　　　　　　　　　　　　頭に0を足して7文字して「-」を挿入
10、45　　　　　(空欄)　　 045-0000　→B空欄なのでAを反映→　
　　　　　　　　　　　　　　　　　　　　3文字未満なので頭に0を足して
　　　　　　　　　　　　　　　　　　　　3文字し「-0000」を足す
11、7　　　　　(空欄)　　　007-0000　→B空欄なのでAを反映→　
　　　　　　　　　　　　　　　　　　　　3文字未満なので頭に0を足して3文字し
　　　　　　　　　　　　　　　　　　　　「-0000」を足す
12、45-　　　　(空欄)　　　045-0000　→B空欄なのでAを反映　→「-」の前が3文字
　　　　　　　　　　　　　　　　　　　　未満なので頭に0を足し3文字し「0000」を足す
13、45-0010　　(空欄)　　　045-0010　→B空欄なのでAを反映　→「-」の前が3文字
　　　　　　　　　　　　　　　　　　　　未満なので頭に0を足す
14、045-010　　(空欄)　　　045-010　→B空欄なのでAを反映　→「-」から後ろが
　　　　　　　　　　　　　　　　　　　　足りないがそのまま


※頭に0を追加するのは書式が数値になっていて0が消えている為です。
※AB両方8文字なら末尾が0000ではない方を反映し、まったく同一ならB列を反映します。
※約25万行あります。

郵便番号が3桁だった時代から追記している為、同一人物のデータでも3桁と7桁が混在している状況です。

下記のページを参考に住所列から7桁の郵便番号をB列に反映させましたが、村や市が合併しているものは(空欄)や末尾-0000になってしまいました。
http://www.wanichan.com/pc/excel/2010/2/page06.h …

何卒、宜しくお願いします。

質問者からの補足コメント

• 15、(空欄)　　045-0000　　045-0000　→Aが空欄なのでBの末尾が0000でもBを反映
  16、(空欄)　　(空欄)　　　(空欄)　　　　→飛ばす

    補足日時：2015/05/01 16:59

• 17、452-0300　　452-0312　　452-0312　→両方8文字で末尾0000も無い場合はBを反映
  
  後から追加ですみません。

    補足日時：2015/05/02 03:08

• ありがとうございます。
  
  045-010
  
  このようにハイフンがあって、後ろが4桁になっていないものは
  人為的な入力ミスだと私は思っているので、そのままにしておくしかないかなと思っています。
  
  Bが空欄という事は住所にもミスがあるか、村や市の合併で古くなっていますし。。。
  
  何か案があるようでしたら、検討したいのでお聞かせ頂けますと幸いです。
  
  宜しくお願い致します。

  No.5の回答に寄せられた補足コメントです。 補足日時：2015/05/07 20:56

• 皆様、マクロ例など大変参考になるスクリプトを多数ありがとうございます。
  
  明日、GW明け出勤しますので、早速試してみたいと思います。
  
  結果が出たら追記致します。

    補足日時：2015/05/07 20:59

• 皆様ご回答ありがとうございます。
  下記のようにハイフンより前の3桁が同じである状況しか想定できていませんでした。
  A　　　　　B
  055-2300　055-0000
  
  実際は下記のように前3桁が違っていて、Bの末尾が0000のものが多数ある事が判明しました。
  A　　　　　B
  030-0131　038-0000
  
  現在の条件ですと、下記6番に該当し、A採用になるかと思いますが、Bを採用したいです。
  　　A　　　　　B　　　　　C
  6、225-0055　225-0000　225-0055　→両方8文字だがBは末尾が0000なのでAを反映
  
  理由は、B列はつい1ヶ月前に住所列から最新の郵便番号DBを元に生成したもので、
  A列は10年以上前から手入力で古くなっています。
  
  B列の末尾が0000だった場合は住所不備で完全な郵便番号になっていませんが、
  昔の郵便番号よりは近い番号になっているようです。

    補足日時：2015/05/09 21:35

• その為、
  
  　　　A　　　　　　B　　　　　　C
  18、079-2300　　055-0000　　055-0000　→両方8文字でBの末尾が0000だった時、
  　　　　　　　　　　　　　　　　　　　　　　　前3桁の数字が一致した場合はAを、
  　　　　　　　　　　　　　　　　　　　　　　　一致しなかった場合はBを選択する
  
  を追加したいです。
  
  尚、No.8のご回答にて、この条件を考慮して頂いたマクロがあるとの事です。(No.13談)
  
  No.7以降に頂いたサンプルに関しては、退勤してしまった為、まだ試す事が出来ておりません。
  
  次回、5月12日(火)の出勤で試す事が出来ますので、その時にまた結果等をご報告させて頂きます。

    補足日時：2015/05/09 21:36

• >No.2 に書いた「データを処理する方針」や各数式の意味は、理解しましたか？
  すみません。
  勤務中は非常にバタバタしていまして、素っ気無い結果のみのお礼になってしまい失礼しました。
  書式の変更で「0」と「-」が自動的に付く事は認知しておりますが、
  普段データを記入する者の多くが、そういったエクセルの挙動を知らず、
  長年にわたり入力やコピペを繰り返した結果、今のA列の状況を生んでいる為、
  今回の整理で「ハイフンありの文字列に統一してしまった方が良さそうだ」というのが私の判断です。
  
  No.11に記載して頂いている各条件理由の推測に関してはズレを感じます。
  
  B列を優先している理由は、前述した通り、つい１ヶ月前に下記ページの「住所から郵便番号を入力する」を実行した最新の郵便番号だからです。
  http://www.wanichan.com/pc/excel/2010/2/page06.h …

  No.11の回答に寄せられた補足コメントです。 補足日時：2015/05/09 21:36

• A列は10年以上前からの手入力なのでパターンが多いですが、B列は３パターンしかありません。
  
  1、末尾が0000ではない郵便番号(完全体)　←全部これだったら良かった
  2、末尾が0000の郵便番号
  3、空欄
  
  なので、この3パターンから条件を考えてみるとこうなりました。
  
  B列が、
  1、末尾が0000ではない郵便番号　→　無条件にB列採用　(整形無しでそのまま)
  2、末尾が0000の郵便番号　→　整形したA列と比較して前3桁が不一致ならB列採用、一致ならA列採用
  3、空欄　→　A列採用し整形
  となります。
  
  最初からB列の3パターンから条件を提示した方がシンプルだったかもしれませんね。
  お手数をお掛けして申し訳ありません。
  
  10件以上の回答がつくのはなかなか珍らしく、ご注目ご注力頂き、感謝致します。

    補足日時：2015/05/09 21:37

• サンプルスクリプトはNo.8にあるSamp3を採用させて頂きましたが、
  カスタマイズ性に優れ、スクリプトを見ただけで勉強になるのは
  圧倒的にsoixante様のサンプルであった為、ベストアンサーはNo.1＆No.15とさせて頂きました。
  
  多数のご回答ありがとう御座いました。

    補足日時：2015/05/15 17:33

No.6 回答者： 30246kiku 回答日時： 2015/05/08 13:25

＃３、５です

>　　　　　　　　If ((sA(1) = CZR4) Or (sB(1) <> CZR4)) Then
>　　　　　　　　　　sA(1) = sB(1)
>　　　　　　　　End If
>　　　　　　　　If ((sA(0) = CZR3) Or (sB(0) <> CZR3)) Then
>　　　　　　　　　　sA(0) = sB(0)
>　　　　　　　　End If

↓　上記部分を以下の１行に変更してどうなりますか

　　　　　　　　If (sB(1) <> CZR4) Then sA = sB

※
>　　　　　　　　vA(i, 1) = Join(sA, "-")

↓ "-" を Const にしていたので、以下に変更しても

　　　　　　　　vA(i, 1) = Join(sA, CSEP)

No.5 回答者： 30246kiku 回答日時： 2015/05/05 20:05

＃３です

> 14、045-010　　(空欄)　　　045-010　→B空欄なのでAを反映　→「-」から後ろが
> 　　　　　　　　　　　　　　　　　　　　足りないがそのまま

確認ですが、このパターンは変更してはいけないのですよね？

No.4 回答者： WindFaller 回答日時： 2015/05/05 13:03

こんにちは。

本来、私自身、最初からなら、このような発想にはしないのですが、あえて、質問者さんのご要望に合わせて作ってみました。なぜなら、番号ウィザードのオープンコードを加工して、その出力から判定させればよいからです。ただ、それは、VBAを知っている人に限ります。また、これでも、VBAの裏ワザを使っていますので、わかる人は、どこで使っているかわかるはずです。

最初みた時に、気になった質問でしたので、フィードバックして作ってみました。

>17、452-0300　　452-0312　　452-0312　→両方8文字で末尾0000も無い場合はBを反映

端に、どちらが重いか測るだけです。逆のケースが出てくるかもしれません。

'//
Sub CheckPhonNumAr()
　'No. 8973501
　Dim i As Long
　Dim x As Variant, y As Variant
　Dim Rng As Range
　Dim Ar As Variant
　'ユーザー設定（最初の行の決定
　'---------------------------
　Set Rng = Range("A2", Cells(Rows.Count, 1).End(xlUp).Resize(, 3))
　'---------------------------
　Ar = Rng.Value
　For i = LBound(Ar) To UBound(Ar)
　　x = StrConv(Ar(i, 1), vbNarrow): y = StrConv(Ar(i, 2), vbNarrow)
　　If x Like "###-#*" Then
　　　x = x & String(8 - Len(x), "0")
　　ElseIf y Like "###-#*" Then
　　　y = y & String(8 - Len(y), "0")
　　End If
　　x = Replace(x, "-", ""): y = Replace(y, "-", "")
　　If IsNumeric(x) Then
　　　If Len(x) > 4 Then
　　　　x = Format$(x, "000-0000")
　　　ElseIf Len(x) > 0 Then
　　　　x = Format$(x, "000") & "-0000"
　　　End If
　　End If
　　If IsNumeric(y) Then
　　　If Len(y) > 4 Then
　　　　y = Format$(y, "000-0000")
　　　ElseIf Len(y) > 0 Then
　　　　y = Format$(y, "000") & "-0000"
　　　End If
　　End If
　　If x >= y Then Ar(i, 3) = x
　　If y > x Then Ar(i, 3) = y
　Next
　Rng.Columns(3).Value = Application.Index(Ar, 0, 3)
End Sub

この回答へのお礼

ご回答ありがとうございます。

>>17、452-0300　　452-0312　　452-0312　→両方8文字で末尾0000も無い場合はBを反映
>端に、どちらが重いか測るだけです。逆のケースが出てくるかもしれません。

これはつまり、

A　　　　　　 B
452-0012　　452-0001

このパターンだった場合は、Aが選択されるという事でしょうか？

そうだとちょっとまずいです。

Aは手入力したもので、
Bは住所から自動生成したものなので、
Bの末尾が0000でない限り、Bの方が信憑性がある為、Bを選びたいのです。

お礼日時：2015/05/08 11:33

No.3 回答者： 30246kiku 回答日時： 2015/05/03 13:00

処理抜けがあるかもしれませんが、以下でどうなりますか

※ ２５万行なんですよね

Option Explicit

Const CZR3 As String = "000"
Const CZR4 As String = "0000"
Const CSEP As String = "-"

Public Sub Samp1()
　　Dim vA As Variant
　　Dim sA() As String, sB() As String
　　Dim i As Long

　　With ActiveSheet.UsedRange.Resize(, 3)
　　　　vA = .Resize(, 2).Value
　　　　For i = 1 To UBound(vA)
　　　　　　If ((vA(i, 1) <> "") Or (vA(i, 2) <> "")) Then
　　　　　　　　sA = PostCode(vA(i, 1))
　　　　　　　　sB = PostCode(vA(i, 2))
　　　　　　　　If ((sA(1) = CZR4) Or (sB(1) <> CZR4)) Then
　　　　　　　　　　sA(1) = sB(1)
　　　　　　　　End If
　　　　　　　　If ((sA(0) = CZR3) Or (sB(0) <> CZR3)) Then
　　　　　　　　　　sA(0) = sB(0)
　　　　　　　　End If
　　　　　　　　vA(i, 1) = Join(sA, "-")
　　　　　　End If
　　　　Next
　　　　.Columns(3).Value = vA
　　End With
End Sub

Private Function PostCode(vSrc As Variant) As String()
　　Dim sA() As String

　　sA = Split(vSrc & CSEP, CSEP)
　　If (Len(sA(1)) = 0) Then sA(1) = CZR4
　　If (Len(sA(0)) > Len(CZR3)) Then
　　　　sA(1) = Right(sA(0), Len(CZR4))
　　　　sA(0) = Left(sA(0), Len(sA(0)) - Len(CZR4))
　　End If
　　sA(0) = Right(CZR3 & sA(0), Len(CZR3))
　　If (UBound(sA) > 1) Then ReDim Preserve sA(1)
　　PostCode = sA
End Function

この回答へのお礼

ご回答ありがとうございます。

マクロを実行してみたところ、下記の結果になりました。

A　　　　　　B　　　　　　C
029-52　　　024-0000　　024-52
030-0131　　038-0000　　038-0131
079-23　　　055-0000　　055-23

Bの末尾が0000だった場合の処理におかしい点があるようです。

前3桁がBのものが反映され、後ろ4桁がAのものが反映されています。

お礼日時：2015/05/08 12:44

No.2 回答者： MarcoRossiItaly 回答日時： 2015/05/02 17:00

普通、Excel では、郵便番号は「-」を含む文字列データではなく、「7 桁以下の整数」である数値として保存します。

しかし「-」を表示させたくなったときは、セルの書式を設定することにより、「7 桁以下の整数」に「-」が挿入されたような見かけにすることが可能です。数値を文字列に変換する必要はありません。

具体的には、例えばどこかのセルに、「12345」と記入してください。「セルの書式設定 > 表示形式タブ > その他 > 郵便番号」とセルの書式を指定するだけで、「001-2345」とセルに表示されます。数式バーには引き続き「12345」と表示されているので、書式を設定しても、数値が文字列になったわけではありません。

しかし「012-012」という文字列が記入されているセルに郵便番号の書式を設定したとしても、表示は何も変化しません。だから数値や文字列を含む広いセル範囲に、まとめて郵便番号の書式を設定すればオッケーです。

「先頭に 0 を付ける目的で文字列の書式を設定する」ということは、行いません。郵便番号の書式は数値データのための書式ですが、文字列の書式に変更しなくても、7 桁未満なら勝手に 0 が付きます。

したがってお手元のデータを処理する方針としては、次のようになります。質問文のデータのみであれば、マクロなどを使わず基本的な数式のみでできます。

(1)全データの文字列から「-」を数式により削除、ただし条件 14
　 （「-」を含むが「-」以降が 1 桁以上 3 桁以下）のケースのみを除外
(2) A 列が 3 桁以下の数値であれば A 列を 10,000 倍
(3) B 列が空欄であれば A 列のほうを採用し、B 列にデータがあれば
　 大きなほうの数値を採用（7 桁未満でも気にせずそのまま）
(4)得られた答えのデータに郵便番号の書式を設定

添付図の数式は、次のとおりです。

E1　=countif(b1,"*-?")+countif(b1,"*-??")+countif(b1,"*-???")=0
F1　=if(e1,0+substitute(b1,"-",),b1)
G1　=if(e1*(len(f1)<=3),f1*10^4,f1)
H1　=0+substitute(0&c1,"-",)
I1　=if(e1,max(g1:h1),g1)
J1　=i1

※ F1・H1 セルの「0+」という部分は、SUBSTITUTE 関数で「-」を削っただけでは
　文字列のままなので、それを数値に変換するための操作です。
※ H1 の「0&」という部分は、C 列が空白の場合、空文字列にゼロを足すことにより
　発生するエラーを回避するための操作です。

この回答へのお礼

ご回答ありがとうございます。

頂いた関数を実行してみたところJ列に、下記の結果が出ました。

B　　　　　　C　　　　　　J
010-05　　010-1502　　　010-05
(空欄)　　　311-4152　　　#VALUE!
001-0915　001-0910　　　001-0915


下記３つの条件が満たされていないようです。

　　　A　　　　　　B　　　　　　C
　3、250021　　　025-0021　　025-0021　→Aが8文字以下なのでBを反映
15、(空欄)　　　　045-0000　　045-0000　→Aが空欄なのでBの末尾が0000でもBを反映
17、452-0300　　452-0312　　452-0312　→両方8文字で末尾0000も無い場合はBを反映

お礼日時：2015/05/08 13:31

No.1 回答者： soixante 回答日時： 2015/05/02 13:41

条件が多岐にわたっているので、マクロでの処理かと思います。

漏れもあるかもしれませんが、お書きの17パターンはいけると思います。

■マクロ処理した後のものは戻せませんので、必ずバックアップを取ってからやってください
データは1行目から始まると想定しています。A、Bにデータ、Cに最終結果を出す設定です。

マクロがお分かりにならない場合、以下の流れでお願いします。
1 .エクセル画面で、Alt+F11　でマクロの画面（Visual Basic Editor）を出す。
２. Alt + I 、M　で標準モジュールを挿入する。（真っ白な画面が出ます）
３．以下のコードをコピペする。
４．エクセル画面に戻り、Alt+F8　から　AAA　を実行。
■マクロ処理した後のものは戻せませんので、必ずバックアップを取ってからやってください

-------------------------------------------------------------------
Sub AAA()

Dim r As Long
Dim MojiA As Variant, MojiB As Variant
Dim MojiC As Variant
Dim Lstrow As Long
Lstrow = Cells(Rows.Count, 1).End(xlUp).Row

r = 1
Do While r <= Lstrow
　　　MojiA = Cells(r, 1).Value
　　　MojiB = Cells(r, 2).Value

'両方とも形式が正しい場合
If Len(MojiA) = 8 And Mid(MojiA, 4, 1) = "-" And Len(MojiB) = 8 And Mid(MojiB, 4, 1) = "-" Then
　　　If Right(MojiA, 4) = "0000" Then
　　　　　　MojiC = MojiB
　　　ElseIf Right(MojiB, 4) = "0000" Then
　　　　　　MojiC = MojiA
　　　Else
　　　　　　MojiC = MojiB
　　　End If
'両方埋まっていて、片方が形式が正しい場合
ElseIf Len(MojiA) = 8 And Mid(MojiA, 4, 1) = "-" And Len(MojiB) = 0 Then
　　　MojiC = MojiA
ElseIf Len(MojiB) = 8 And Mid(MojiB, 4, 1) = "-" And Len(MojiA) = 0 Then
　　　MojiC = MojiB
'片方埋まっていて、その形式が正しい場合
ElseIf Len(MojiA) = 8 And Mid(MojiA, 4, 1) = "-" Then
　　　MojiC = MojiA
ElseIf Len(MojiB) = 8 And Mid(MojiB, 4, 1) = "-" Then
　　　MojiC = MojiB

'Aが空白
ElseIf Len(MojiA) = 0 Then
　　　If Len(MojiB) > 0 Then
　　　　　　MojiC = MojiB
　　　End If

'Bが空白
ElseIf Len(MojiB) = 0 Then
　　　If InStr(MojiA, "-") > 0 Then 'ハイフンがあるとき
　　　　　　If Mid(MojiA, 4, 1) = "-" And Len(MojiA) = 8 Then
　　　　　　　　　MojiC = MojiA
　　　　　　ElseIf Right(MojiA, 1) = "-" Then
　　　　　　　　　MojiC = String(4 - Len(MojiA), "0") & MojiA & "0000"
　　　　　　ElseIf Left(Right(MojiA, 5), 1) = "-" Then
　　　　　　　　　MojiC = String(8 - Len(MojiA), "0") & MojiA
　　Else
　　　　　MojiC = MojiA
　　End If
ElseIf InStr(MojiA, "-") = 0 Then 'ハイフンがないとき
　　　If Len(MojiA) = 7 Then
　　　　　　MojiC = Left(MojiA, 3) & "-" & Right(MojiA, 4)
　　　ElseIf Len(MojiA) >= 4 And Len(MojiA) <= 6 Then
　　　　　　MojiC = String(7 - Len(MojiA), "0") & MojiA
　　　　　　MojiC = Left(MojiC, 3) & "-" & Right(MojiC, 4)
　　　ElseIf Len(MojiA) <= 3 Then
　　　　　　MojiC = String(3 - Len(MojiA), "0") & MojiA & "-0000"
　　　End If
End If
Else
　　　MojiC = "未定義"
End If


Cells(r, 3).Value = MojiC
MojiC = ""
r = r + 1
Loop

End Sub
-------------------------------------------------------------------------------

この回答へのお礼

ご回答ありがとうございます。

希望の通りの結果が得られました。

スクリプトにも随所にコメントが記載されていて、どの部分がなんの処理をしているのか解かりやすく
カスタマイズして活用できそうです。

マクロの勉強にもなって非常に助かります。

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

お礼日時：2015/05/08 17:19