概要
郵便番号を「XXX-XXXX」の表記にそろえる最短ルートは、TEXT関数の書式指定「000-0000」を使うことです。値を数値として受け取り、3桁と4桁にゼロ埋めしてハイフンを自動で挿入します。取り込みデータにハイフンが混在していても、SUBSTITUTEと組み合わせれば安定して整形できます。
基本の使い方
書式
=TEXT(値,"000-0000")
値が7桁の数値であれば、そのまま「XXX-XXXX」に整形されます。文字列の場合はVALUEで数値化してからTEXTに渡すと確実です。
例
=TEXT(1234567,"000-0000") // "123-4567"
=TEXT(VALUE("0123456"),"000-0000") // "012-3456"
具体例
セルの7桁数値を「XXX-XXXX」にする
=TEXT(A2,"000-0000")
A2が7桁の数値ならハイフン入りに整形されます。
文字列の郵便番号を数値化して整形
=TEXT(VALUE(A2),"000-0000")
A2が「”1234567″」のような文字列でも正しく整形されます。
ハイフン混在データを一律整形
=TEXT(VALUE(SUBSTITUTE(A2,"-","")),"000-0000")
既存のハイフンを除去してから「000-0000」形式に統一します。
応用テンプレート
スペースやタブも除去して整形
=TEXT(VALUE(SUBSTITUTE(SUBSTITUTE(TRIM(A2),CHAR(9),""),"-","")),"000-0000")
余分な空白やタブ、ハイフンを取り除き、確実に「XXX-XXXX」にします。
先頭ゼロを保持して整形
=TEXT(VALUE(SUBSTITUTE(A2,"-","")),"000-0000")
「0123456」→「012-3456」。TEXTのゼロ埋めで先頭ゼロが保持されます。
入力が7桁以外の時は空欄にする
=IF(LEN(SUBSTITUTE(A2,"-",""))=7, TEXT(VALUE(SUBSTITUTE(A2,"-","")),"000-0000"), "")
桁数チェックで安全に整形します。
よくあるつまずきと対策
文字列はそのままではTEXTで整形されない
文字列の数字はVALUEで数値化してからTEXTに渡しましょう。SUBSTITUTEでハイフンも事前に除去します。
先頭ゼロが消える
数値として保持すると先頭ゼロは落ちますが、TEXTの「000-0000」で表示上は復元できます。書式のゼロ埋めを頼りにしましょう。
不正な桁や文字が混じる
整形前に「ハイフン除去→桁数チェック」を行うと安定します。必要ならISNUMBERやVALUEとIFERRORで保護します。
例題
問題1: A2の7桁数値を「XXX-XXXX」に整形してB2に表示してください。
解答例:
=TEXT(A2,"000-0000")
問題2: C2が文字列の郵便番号（ハイフンなし）です。「XXX-XXXX」に整形してD2に表示してください。
解答例:
=TEXT(VALUE(C2),"000-0000")
問題3: E2はハイフン有無が混在しています。正規化して「XXX-XXXX」にし、F2に表示してください。
解答例:
=TEXT(VALUE(SUBSTITUTE(E2,"-","")),"000-0000")
問題4: G2に空白やタブが含まれます。クリーン化して「XXX-XXXX」に整形し、H2に表示してください。
解答例:
=TEXT(VALUE(SUBSTITUTE(SUBSTITUTE(TRIM(G2),CHAR(9),""),"-","")),"000-0000")
問題5: I2が7桁でない場合は空欄、7桁なら「XXX-XXXX」にしてJ2に表示してください。
解答例:
=IF(LEN(SUBSTITUTE(I2,"-",""))=7, TEXT(VALUE(SUBSTITUTE(I2,"-","")),"000-0000"), "")
まとめ
郵便番号の標準化は「TEXT(…,”000-0000″)」が主役、前処理にSUBSTITUTE・TRIM・VALUEを組み合わせるのが定石です。ハイフン混在や先頭ゼロの問題は、数値化とゼロ埋めでシンプルに解決できます。まず“クリーン化”してから“整形”する、この2ステップを習慣化すると実務で強くなれます。