Excelに取り込んだ南アフリカID番号（13桁）を、先頭の0を保ったまま「000000 0000 000」のように読みやすく表示したい場面は意外と多いです。この記事では、データが文字列（テキスト）か数値かで最適な手段が変わる点を整理し、式・表示形式・正規表現の3ルートを具体例つきで解説します。
Excelで「13桁IDを途中にスペースを入れて表示」するときの前提
南アフリカID番号は13桁で、視認性のために「6桁 + スペース + 4桁 + スペース + 3桁」（例：800101 5009 087）のように区切って表記したくなることがあります。Excelでは、ここでまずセルが「文字列（テキスト）」なのか「数値」なのかを確認するのが近道です。
| セルの中身 | 見た目の例 | 先頭ゼロ | おすすめアプローチ | 理由 |
|---|---|---|---|---|
| 文字列（テキスト） | 000123456789012（13桁でも先頭0が残る） | 維持できる | 式で分割して結合 | 表示形式（書式）が効かないため |
| 数値 | 1234567890123（先頭0が落ちることがある） | 落ちやすい | ユーザー定義の表示形式 | 見た目だけを安全に整形できるため |
| 数値だが、Excelが指数表記にする | 1.23457E+12 | 落ちる | 表示形式 or TEXT関数 | 桁が見えず、目視確認が難しいため |
ポイントは次の2つです。
- 「スペースを入れて見せたいだけ」なら、数値のまま表示形式で解決できることが多い
- 先頭ゼロを絶対に失いたくない、あるいは文字列として管理したいなら、式（またはPower Query）で新しい文字列を作るのが確実
方法A：文字列でも確実に対応する（推奨） 式で分割して結合
この方法は、入力が文字列でも数値でも安定して「000000 0000 000」の形を作れます。先頭ゼロ維持を最優先する実務では、このルートが一番トラブルが少ないです。
基本形（LEFT / MID / RIGHT）
A1に13桁が入っているとして、別セル（例：B1）に次を入力します。
=LEFT(A1,6)&" "&MID(A1,7,4)&" "&RIGHT(A1,3)関数の役割を整理すると理解が早いです。
| 関数 | 意味 | このケース | 取り出す位置 |
|---|---|---|---|
| LEFT | 左から指定文字数 | 最初の6桁 | 1〜6桁 |
| MID | 途中から指定文字数 | 次の4桁 | 7〜10桁 |
| RIGHT | 右から指定文字数 | 最後の3桁 | 11〜13桁 |
この式の良い点は、セルの見た目ではなく結果そのものが「スペース入りの文字列」になることです。Wordやメール、Webフォーム貼り付けなど、Excel外でも同じ見た目を保てます。
13桁以外のデータが混じっても崩れない式
運用上ありがちなのが、「空欄」「12桁/14桁」「途中に空白やハイフンが混入」などです。まずは13桁のときだけ整形する形にすると、事故が減ります。
=IF(LEN(A1)=13,LEFT(A1,6)&" "&MID(A1,7,4)&" "&RIGHT(A1,3),A1)LENで桁数をチェックし、13桁以外はそのまま返します。これなら、未入力行や不正データを無理に加工しません。
スペースやハイフンを除去してから整形する（現場向け）
「すでに8001015009 087みたいに途中に空白が入っている」「800101-5009-087のようにハイフン区切りで渡された」など、入力が揺れる場合は、一度“数字だけ”に寄せてから整形すると強いです。
空白とハイフンを取り除いてから整形する例：
=IF(LEN(SUBSTITUTE(SUBSTITUTE(A1," ",""),"-",""))=13,
LEFT(SUBSTITUTE(SUBSTITUTE(A1," ",""),"-",""),6)&" "&
MID(SUBSTITUTE(SUBSTITUTE(A1," ",""),"-",""),7,4)&" "&
RIGHT(SUBSTITUTE(SUBSTITUTE(A1," ",""),"-",""),3),
A1)同じ処理を何度も書くのが気になる場合、Microsoft 365の環境ならLETで読みやすくできます。
=LET(x,SUBSTITUTE(SUBSTITUTE(A1," ",""),"-",""),
IF(LEN(x)=13,LEFT(x,6)&" "&MID(x,7,4)&" "&RIGHT(x,3),A1))結果を元の列に置き換える（値貼り付け）
「表示用の列を別に作るのではなく、元の列をスペース入りにしたい」という場合は、次の手順が安全です。
- 整形結果の列（例：B列）を最後までコピー
- 元の列（A列）を選択
- 右クリック → 「形式を選択して貼り付け」 → 「値」
これで、式ではなく結果の文字列として確定します。あとから別システムに渡すときも、意図した表記を保てます。
方法B：数値として入っている場合は「ユーザー定義の表示形式」で見た目だけ整える
データが数値として入っている（つまりExcelが「数」として扱っている）なら、中身は変えずに見た目だけ「000000 0000 000」にできます。ID番号を数値計算に使わない前提なら、表示形式で済ませるのは合理的です。
設定手順（セルの書式設定）
- 対象セル（または列）を選択
- 右クリック → 「セルの書式設定」
- 「表示形式」タブ → 「ユーザー定義」
- 種類に次を入力
000000" "0000" "000この書式の意味は、「13桁になるまでゼロで埋めて、6桁目と10桁目の後ろにスペースを表示する」です。
表示形式が向いているケース・向かないケース
| 観点 | 表示形式（ユーザー定義） | 式で文字列を作る |
|---|---|---|
| セルの中身 | 数値のまま | 文字列になる |
| 見た目 | 整形される | 整形される |
| コピーして他アプリへ貼り付け | 環境により“見た目”か“中身”が貼られる | スペース入りの文字列が貼られやすい |
| CSV出力 | 基本は数値として出る（スペースが消えることがある） | スペース入りで出しやすい |
| テキストとして入っているデータ | 効かない | 効く |
「Excel内で見やすくしたい」なら表示形式が最短です。一方で、「貼り付け先でもスペース入りを維持したい」、「文字列で扱う必要がある」なら方法Aが安全です。
TEXT関数で“見た目を文字列として”取り出す（数値向け）
数値として入っているけれど、結果は文字列で欲しい場合はTEXT関数が便利です。
=TEXT(A1,"000000 0000 000")この結果は文字列なので、他アプリへ渡すときの見た目が安定しやすくなります。表示形式と違って、セルの中身そのものがスペース入りになります。
方法C：REGEXREPLACEが使える環境なら正規表現で一発
Excelの一部環境（主にMicrosoft 365系）では、正規表現を扱える関数が利用できることがあります。利用できるなら、13桁を3グループに分けて差し込むのが最短です。
=REGEXREPLACE(A2,"(\d{6})(\d{4})(\d{3})","$1 $2 $3")仕組みは単純で、\d{6}（6桁）、\d{4}（4桁）、\d{3}（3桁）を括弧でグループ化し、置換文字列で$1 $2 $3として並べ直しています。
正規表現を採用するときの注意点
- 利用できる関数・範囲は環境差があるため、組織内で配布するファイルでは互換性確認が必要
- 入力が「13桁の数字だけ」である前提が強いので、空白やハイフン混入があるなら事前に除去する設計が安心
空白とハイフンを取り除いてから正規表現をかけたい場合（利用できるなら）の例：
=REGEXREPLACE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"-",""),"(\d{6})(\d{4})(\d{3})","$1 $2 $3")どれを使うべき？状況別のおすすめ早見表
| やりたいこと | データの状態 | おすすめ | 理由 |
|---|---|---|---|
| とにかく確実にスペース入りで表示・出力したい | 文字列/数値どちらでも混在 | 方法A（LEFT/MID/RIGHT） | 互換性が高く、結果が文字列として残る |
| Excel内の見た目だけ整えたい | 数値として入っている | 方法B（ユーザー定義の表示形式） | 元データを変えずに見た目だけ整形できる |
| 一発で整形したい（短い式が好み） | 正規表現関数が使える環境 | 方法C（REGEXREPLACE） | 式が短く、パターン変更にも強い |
| 貼り付け・CSV・システム連携まで見た目を固定したい | どの状態でも | 方法AまたはTEXT関数 + 値貼り付け | 最終成果物を“スペース入り文字列”に確定できる |
よくあるトラブルと対処法
先頭の0が消えてしまう
南アフリカID番号は先頭が00などになり得るため、貼り付けやCSV取り込みで数値扱いされると先頭ゼロが消えます。
- 入力前に列の表示形式を「文字列」にしてから貼り付ける
- 取り込み時に「テキスト」として列データ型を指定する（Power Queryが有効）
- すでに数値化してしまった場合は、表示形式で13桁にゼロ埋めするか、TEXT関数で復元する
「1.23457E+12」みたいな指数表記になって読めない
セル幅が狭い、または標準の表示のまま大きい数値を置くと、指数表記で見えることがあります。
- セル幅を広げる
- 表示形式をユーザー定義にする（
000000" "0000" "000） - TEXT関数で文字列にして表示する
途中にスペースやハイフンが混ざって整形が崩れる
方法Aの応用で、SUBSTITUTEで不要文字を除去してから分割してください。特に現場データは「人が入力した表記ゆれ」が混ざるので、最初から耐性を持たせるのが安全です。
13桁ではないデータが混在している
整形してしまうと「間違ったIDがそれっぽく見えてしまう」リスクがあります。LENでチェックするか、別列でエラー検出を行うのがおすすめです。
チェック用（13桁以外を警告表示したい例）：
=IF(LEN(SUBSTITUTE(SUBSTITUTE(A1," ",""),"-",""))<>13,"桁数要確認","OK")実務で役立つ：入力ミス検出と個人情報の扱い
条件付き書式で“怪しい行”を目立たせる
ID番号は個人情報なので、入力ミスがあると修正依頼が増え、二重管理も起きやすくなります。Excelの条件付き書式で、桁数が合わないものだけ色付けすると、確認が速くなります。
- 対象範囲を選択
- 「ホーム」→「条件付き書式」→「新しいルール」
- 「数式を使用して、書式設定するセルを決定」
- 次のような式を設定（A1を範囲先頭に合わせて調整）
=LEN(SUBSTITUTE(SUBSTITUTE($A1," ",""),"-",""))<>13共有用シートでは一部マスキングして表示する
社内共有や外部提出用に、ID番号をそのまま出さない運用が必要なこともあります。スペース区切りの形を保ちつつ、一部を伏せるなら次のような式が使えます（元データは別列に保持するのが基本です）。
=LEFT(A1,6)&" "&"****"&" "&RIGHT(A1,3)「どの桁を伏せるか」は運用ルールに合わせて調整してください。Excelはコピーされやすいので、表示だけでなくファイル権限・共有範囲・保存先も含めて扱いを設計するのが安全です。
大量データならPower Queryで一括整形が安定
数千〜数万行のIDを毎月取り込むような運用では、セル関数よりPower Queryのほうが再現性が高いことがあります。Power Queryなら「テキストとして取り込む」「不要文字を除去」「指定位置で区切る」を手順として固定でき、更新ボタンで同じ加工を繰り返せます。
考え方の流れはシンプルです。
- 列のデータ型をテキストにする（先頭0対策）
- 空白やハイフンを置換で削除する
- 6桁・4桁・3桁で分割して結合する
Power Queryが苦手でも、最初に一度手順を作ってしまえば、次回以降は「更新」だけで同じ形に揃います。
どうしても同じ列で“実データを置換”したい場合（VBA例）
運用上「A列をスペース入り文字列に書き換える」ことが確定しており、毎回の作業をボタン化したい場合はVBAも選択肢になります。配布・セキュリティの都合でマクロが許可される環境向けです。
Sub FormatSouthAfricaID()
Dim rng As Range, c As Range
Set rng = Selection ' 事前に対象範囲を選択しておく
For Each c In rng.Cells
Dim s As String
s = CStr(c.Value)
' 空白とハイフンを除去
s = Replace(s, " ", "")
s = Replace(s, "-", "")
If Len(s) = 13 Then
c.Value = Left(s, 6) & " " & Mid(s, 7, 4) & " " & Right(s, 3)
End If
Next c
End Subこのマクロは13桁のものだけを整形し、それ以外は触りません。ID列に空欄や別コードが混ざる場合でも、被害を限定できます。
仕上げに確認したいチェックポイント
- 13桁のままか（空白・ハイフンを除いた実桁数で確認）
- 先頭ゼロが必要なデータで落ちていないか
- Excel内で見やすいだけで良いのか、外部へ渡す表記も固定したいのか
- 元データを残す必要があるか（監査・照合・再取り込みの都合）
「見た目だけ整える」なら表示形式、「貼り付け先でも同じ表記にしたい」なら式で文字列を作って値貼り付け、が判断の基本になります。南アフリカID番号のように先頭ゼロが意味を持つデータは、Excelが勝手に数値化してしまうところから問題が起きやすいので、最初に“文字列として扱うか”を決めておくと、後工程がとても楽になります。
コメント