エクセルで郵便番号のハイフンあり・なしをそろえる方法(7桁)【LibreOfficeで計算して確認】

住所録に「1234567」と「123-4567」が混ざっていると、並べ替えや突き合わせがそろわず、宛名ラベルの差し込みでも見た目がばらつきます。この記事では、郵便番号を7桁の数字にそろえる式と、ハイフン付き(123-4567)に戻す式を、パターン別に実際に計算して確かめた結果だけを紹介します。

出典: 筆者がLibreOfficeで計算して確認(検証スクリプト: site/verify/postal_code.py)。外部サイトの説明は参照していません。Excelでは未確認です。使う関数はExcelにもありますが、本番のデータに使う前に、数行で結果を確かめてください。

先に結論

まず「〒」・ハイフン類・スペースを取り除き、全角の数字を半角にして、7桁の数字に寄せます(B列)。
7桁の数字になっている行だけ、ハイフン付き(D列)またはハイフンなし(E列)に整えます。
7桁にならない行は、式で無理に直さず「要確認」にして人が見ます。
Excelで数値になって先頭の0が消えた6桁(例: 0234567 → 234567)は、数値セルのときだけ0を足す列(C列)で直せます。文字列の6桁には0を足しません。

手順1: 記号を取り除いて数字に寄せる(B列)

A2に郵便番号が入っているとして、B2に次の式を入れ、下へコピーします。

=ASC(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2&"","〒","")," ",""),"　",""),"-",""),"－",""),"ー",""),"‐",""),"−",""))

内側から順に、次の文字をSUBSTITUTE(…,"その文字","")で消し、全体をASC(…)で包んでいます。

「〒」、半角スペース、全角スペース
ハイフン類: -(半角)、－(全角)、ー(長音)、‐、−

A2&""は、数値のセルも文字列として扱うための書き方です。ASCは全角の数字を半角にします。

手順2: 数値セルの6桁に0を足す(C列)

CSVをExcelで開くと、「0234567」が数値の234567になり、先頭の0が消えることがあります。C2に次の式を入れます。

=IF(AND(ISNUMBER(A2),LEN(B2)=6),"0"&B2,B2)

セルが数値で、記号除去後が6桁のときだけ、先頭に0を足します。文字列として入っている「234567」には足しません(元から6桁の誤入力かもしれず、式では区別できないため)。

手順3: 7桁だけをハイフン付き・なしに整える(D列・E列)

D2(ハイフン付き)とE2(ハイフンなし)に、次の式を入れます。

D2: =IF(AND(LEN(C2)=7,SUMPRODUCT(--ISNUMBER(FIND(MID(C2,ROW($1:$7),1),"0123456789")))=7),LEFT(C2,3)&"-"&MID(C2,4,4),"要確認")
E2: =IF(AND(LEN(C2)=7,SUMPRODUCT(--ISNUMBER(FIND(MID(C2,ROW($1:$7),1),"0123456789")))=7),C2,"要確認")

「7文字で、すべての文字が0〜9」のときだけ整形します。ISNUMBER(VALUE(…))のような簡易判定にしなかったのは、「1E34567」のように指数表記として数値扱いされてしまう文字列があるためです。

実際に計算した結果

入力	B列(記号除去)	D列(ハイフン付き)
1234567	1234567	123-4567
123-4567	1234567	123-4567
123ー4567(長音)	1234567	123-4567
123－4567(全角ハイフン)	1234567	123-4567
１２３－４５６７(全角)	1234567	123-4567
〒123-4567	1234567	123-4567
(前後にスペース)123-4567	1234567	123-4567
123　4567(全角スペース)	1234567	123-4567
1234567(数値セル)	1234567	123-4567
234567(数値セル・6桁)	234567	023-4567(C列で0を補完)
234567(文字列・6桁)	234567	要確認
12-34567(ハイフン位置が違う)	1234567	123-4567
123-45678(8桁)	12345678	要確認
1E34567	1E34567	要確認
123-456A	123456A	要確認
(空欄)	(空欄)	要確認

E列(ハイフンなし)も同じ行で「要確認」になり、7桁の行は023-4567の代わりに0234567の形で出ました。

注意したいのは次の3点です。

ハイフンの位置が違う「12-34567」も、7桁の数字になれば整形されます。位置が違うこと自体は検出しません。
空欄の行は、この式のままだと「要確認」になります。空欄を空欄のまま残したいときは、D2を=IF(A2="","",…)で包んでください(この包み方は上の16パターンの検証には含めていません)。
郵便番号が実在するかどうかは確認していません。「000-0000」のような存在しない番号も、7桁の数字なら整形されます。

注意点: この式ができないこと

住所と郵便番号の対応が合っているかは確認できません。
外国の郵便番号や、5桁の旧郵便番号は対象外です(7桁以外は「要確認」になります)。
上の式は元の列(A列)を書き換えません。結果を残すときは、列をコピーして「値として貼り付け」をしてから元の列を削除します。いきなり上書きすると、やり直せなくなります。

名簿全体を整えるなら

郵便番号のほかに、電話番号のハイフン、全角・半角、スペース、前株・後株など、名簿には直したい表記ゆれが重なりがちです。運営者が作った「名簿クレンジングシート」は、これらをまとめて整えるExcelのシートです。まず試したい方向けに、50行までの無料お試し版があります。この記事の方法で足りる場合は、使う必要はありません。

ブラウザで手軽に整えたいときは、無料の郵便番号の整形ツール(7桁にそろえる・ハイフンを入れる)や、電話番号の整形ツールも使えます(入力は外部に送信されません)。関連記事: 電話番号のハイフンを一括で削除する方法、全角・半角スペースの一括削除。

まとめ

記号とスペースをSUBSTITUTEで消し、ASCで半角にして7桁に寄せる
7桁の数字だけ、LEFT(…,3)&"-"&MID(…,4,4)でハイフン付きにする
数値セルで先頭の0が消えた6桁だけ、0を足せる。文字列の6桁は人が確認する
7桁にならない行は「要確認」で残し、無理に直さない

式はいずれもLibreOfficeで計算した結果です。Excelでは未確認のため、本番のデータに使う前に、数行で結果を確かめてください。

確認方法: この記事の式は、LibreOffice Calcで実際に再計算し、16パターンの入力(数字のみ・ハイフン・全角・長音・〒付き・前後スペース・数値セルの6桁・桁数違い・英字混じり・空欄など)について、記号除去・6桁の0補完・ハイフン付き7桁・ハイフンなし7桁の4列(計64件)が期待値と一致することを検証スクリプト(site/verify/postal_code.py)で確認しました。SUBSTITUTE・ASC・LEFT・MID・LEN・FIND・ROW・SUMPRODUCT・ISNUMBERはExcelにもある関数ですが、Excelでは未確認です。郵便番号が実在するかどうかは確認していません。外部サイトの記述は参照していません。

参考にした情報

出典: 筆者がLibreOfficeで計算して確認(検証スクリプト: site/verify/postal_code.py)