Excelで社員番号・品番・郵便番号の先頭の0を守るには、入力・取り込みの前からコードを文字列として扱います。数値の123を「00123」と表示する設定と、文字列「00123」を保存することは別です。既に消えた0の数や、数値変換で失われた16桁目以降の情報は、後から書式を変えても元どおりには戻せません。
この記事では、文字列で残す方法、固定桁で表示する方法、CSVの取り込み、VLOOKUP・XLOOKUPで照合する際の違いを整理します。自動データ変換の設定はExcel for Microsoft 365とExcel 2024のWindows・Mac版が対象です。
先頭の0を残したい目的で方法を選ぶ
| 目的 | 選ぶ方法 |
|---|---|
| 元のコードをそのまま保存・照合する | 文字列。00123と000123を区別する。 |
| 数値として計算し、帳票では5桁表示する | 数値＋ユーザー定義の表示形式00000。内部値は123のまま。 |
| 数値123から仕様で決まった5桁コードを作る | TEXT関数。元コードの桁数が不明なときの復元には使わない。 |
| CSVから取り込む | コード列を数値に変換する前に、取り込み側でテキスト型へ指定する。 |
Excelは数値の先頭0を保持せず、数値の精度は最大15桁です。16桁以上の識別子は、指数表示を解除するだけでは保護できません。Microsoftの先頭ゼロと長い数値の説明にあるように、計算しないコードは文字列として取り扱います。
新しく入力する列を文字列にする
- 入力前に対象の列やセル範囲を選択する。
- 右クリックなどから［セルの書式設定］を開く。WindowsではCtrl＋1も利用できる。
- ［表示形式］で［文字列］を選択して確定する。
- 00123や0007などを入力し、数式バーの値も確認する。
少数のセルなら、先頭にアポストロフィを付けて '00123 と入力する方法もあります。アポストロフィは文字列入力の指定で、セルには00123と表示されます。
既に入力済みの123を後から文字列書式へ変えても、元の00123には戻りません。原本CSV・発行元の一覧・バックアップを確認し、元の値から入力し直すか再取り込みしてください。貼り付け後は、貼り付け元の書式や処理で意図しない型になっていないかも確認します。
「&空文字」で見た目の0は復元できない
セルA2の内部値が数値123で、表示形式だけが00000の場合、次の式の結果は文字列「123」です。画面で見えている「00123」は引き継ぎません。
=A2&""| A2の実際の値 | 式の結果 |
|---|---|
| 文字列「00123」 | 文字列「00123」 |
| 数値123（表示だけ00123） | 文字列「123」 |
| 数値123（表示も123） | 文字列「123」 |
文字列「00123」が既に保存されているセルは、その文字列を保ちます。数値と文字列を見分けたいときは、別セルで =ISTEXT(A2) や =ISNUMBER(A2) を確認すると、見た目だけで判断する誤りを避けられます。
固定桁の表示形式とTEXT関数を使い分ける
数値のまま見た目をそろえる
［セルの書式設定］→［ユーザー定義］で種類を 00000 にすると、数値123は00123、数値7は00007と表示されます。計算は元の数値で行われます。5桁、7桁などの決まった帳票形式に向いた方法です。
この設定から「元の入力は00123だった」と判定することはできません。123・0123・00123は、数値へ変換済みなら同じ内部値になっています。入力ごとに異なる0の数を保存したい場合は文字列を使います。
固定桁の文字列を別列に作る
発行元の仕様が「半角数字5桁」と確認できている場合は、別列に次の式を入れて文字列を作れます。
=TEXT(A2,"00000")数値123から文字列「00123」を作る例です。TEXT関数の結果は文字列になるため、計算用の値は元列を参照します。6桁以上の値を5桁に切り詰める式ではありません。入力範囲や桁数の妥当性も確認してください。
コード体系が不明なまま「全部5桁にする」と、0123と00123など別のコードを同一視するおそれがあります。失われた元値の代わりに新しいルールを当てはめる処理なので、発行元の仕様と照らして使います。
CSVはコード列をテキスト型にして取り込む
CSVにはExcelのセル書式が保存されません。CSVを直接開くと、設定によってコードが数値へ自動変換されます。まず原本をコピーして保管し、作業用のブックへ取り込みます。
- Excelで［データ］→［テキスト/CSVから］を開き、原本のコピーを選択する。
- プレビューから［データの変換］または［編集］でPower Queryエディターを開く。
- コード列を選び、データ型を［テキスト］にする。自動生成された［変更された型］など、数値変換のステップが先に入っていないか確認する。
- 先頭0や長い桁が既に失われている場合は、後段で文字列化せず、元データを読んだ直後の型指定を修正する。必要に応じて現在の型変換を置換する。
- 元CSVと数件照合してから［閉じて読み込む］でシートへ読み込む。
この手順はWindowsのPower Query画面を基準にしています。MacのMicrosoft 365では［データ］→［データの取得］からText/CSVを選べます。メニューやエディターの提供状況は版によって異なるため、同じ画面がない場合はMacの公式取り込み手順を確認し、従来のテキストインポートウィザードなどで対象列を文字列に指定します。
Power Queryは更新時にも変換ステップを実行します。一度だけシート側を直して終わらせず、再取り込み後もコードが文字列になっていることを確認してください。CSVの入出力全般はMicrosoftのCSV取り込み・書き出しの説明も参照できます。
Microsoft 365・Excel 2024では自動データ変換も確認する
| 環境 | 設定の場所 |
|---|---|
| Windows | ［ファイル］→［オプション］→［データ］→［自動データ変換］ |
| Mac | ［Excel］→［環境設定］→［編集］→［自動データ変換］ |
先頭0の削除、16桁以上の数値化、Eを含む文字列の指数変換、日付に似た文字列の変換を無効にする選択肢があります。扱うデータに必要なものを見直します。旧版にも同じ設定があるとは限りません。
この設定はPower Queryの型指定を直接変更しません。CSVを直接開く処理とPower Queryから取り込む処理は分けて管理します。また、既に失った値を復元する設定でもありません。対応版と設定項目は自動データ変換の公式説明で確認できます。
VLOOKUP・XLOOKUPはコード体系と型の両方をそろえる
文字列「00123」と文字列「123」は別の値です。数値123を &"" で文字列にしても「123」なので、「00123」と自動的に一致するようにはなりません。先にマスター側と検索側の元データを確認します。
- コード列は双方とも文字列として取り込み、先頭0を含めた実際の文字を確認する。
- 固定桁が公式なコード仕様なら、原本から同じ桁数でそろえる。仕様が不明なら推測して0を付けない。
- 余分な空白や全角・半角の違いを確認する。空白を削る際もコード仕様で許される範囲に限る。
- 重複コードや未該当を別途確認し、最初の一致だけで正しいマスターと判断しない。
検索値A2と、マスターA列のコードがともに正しい文字列である場合の式例です。範囲は実際の一覧に合わせて変更してください。
=XLOOKUP(A2,マスター!$A$2:$A$100,マスター!$B$2:$B$100,"未該当",0)VLOOKUPを使う場合は、完全一致を指定します。
=VLOOKUP(A2,マスター!$A$2:$B$100,2,FALSE)先頭0を捨てるVALUE変換を、ID照合の一律の解決策にはしません。コードとして区別される「00123」と「123」を両方123へ変えると、誤った一致が起きます。
文字列の原本を残した二列運用と確認方法
帳票向けの加工や計算が別途必要なら、元の文字列コード列を残し、変換結果を別列に作ります。IDそのものを足し算する必要がなければ数値化列は不要です。数量・金額はコードと別の数値列に保存します。
- 00123・0007・16桁以上・英字混在など、実際にあり得るコードをサンプルとして確認する。
- 元データと取り込み後の文字数・型・文字列を照合する。
- CSV出力後はテキストとして内容を確認し、受け取る側でも文字列として取り込む。
- 0が消えたCSVを元ファイルへ上書きしない。更新後も原本を残す。
よくある質問
数値にした後、0の数が分からなくなったら？
原本や発行元の台帳から再取得します。固定桁が正式な仕様である場合だけ、その仕様に従う整形を検討します。表示形式から元の桁数を判定することはできません。
長い番号がE付き表示なら、列幅を広げれば直る？
表示だけの問題なら読みやすくなる場合がありますが、数値変換で15桁を超える精度が失われた場合は直りません。元の番号と比較し、16桁以上のIDは文字列で再取り込みしてください。
先頭0を含むCSVを保存したのに、開き直すと消えるのはなぜ？
CSVには列の型情報がないため、開く側で再び数値変換された可能性があります。テキストエディターでCSV自体の値を確かめ、受け取り側の型指定や自動変換設定を見直します。
コメント