Excel関数で7桁にする方法をお探しですね。

広告

Excelで桁数を指定する方法【6桁・7桁・8桁の表示と抽出テクニック】

会員番号や社員番号、商品コードなどをExcelで扱っていると、「6桁にそろえたい」「7桁のコードだけ抽出したい」「8桁の数字を日付として見やすくしたい」といった場面によく出会います。

ただ、Excelは数値の先頭にある「0」を勝手に省略してしまうので、何も設定せずに「000123」と入力しても「123」と表示されてしまうことがあります。

この記事では、Excelで桁数を指定（6桁・7桁・8桁）して表示したり抽出したりする関数テクニックを、表示形式との違いも含めて分かりやすく解説します。

1. 桁数を指定する前に知っておきたい「表示」と「値」の違い

Excelで桁数をそろえるとき、まず理解しておきたいのが「セルに実際に入っている値」と「画面上の見た目」は別物だということです。

例えば、セルに「123」と入力されている場合、ユーザー定義の表示形式で「000000」と設定すれば、画面上は「000123」と表示できます。

でも、セルの中身はあくまで数値の「123」のままなんです。

計算や並べ替えに使う予定があるなら、この方法はとても便利です。

一方で、別のシステムにCSVで出力したり、コードとして文字列のまま扱ったり、先頭の「0」を含めてコピーしたいときは、関数を使って文字列として「000123」を作る方が安全です。

表示形式と関数、どう使い分ける?

桁数を指定する代表的な方法は2つあります。

– **セルの表示形式を使う方法**:元のデータは変えずに見た目だけを整える
– **TEXT関数やRIGHT関数を使う方法**:別のセルに加工済みの値を作る

例えば、6桁なら表示形式に「000000」、7桁なら「0000000」、8桁なら「00000000」と指定すれば、数値の左側に0を補って表示できます。

ただし、数式バーには元の数値が表示されるので、データを受け渡すときは注意が必要です。

実務では「印刷や社内確認だけなら表示形式」「データ連携や抽出に使うなら関数」と考えると判断しやすいですよ。

特に商品IDや会員番号のようなコードは、見た目が数字でも計算に使うものではないことが多いので、文字列として扱った方がトラブルを防げます。

2. 6桁・7桁・8桁にそろえて表示する関数テクニック

TEXT関数で桁数をそろえる

関数で桁数をそろえる基本は、**TEXT関数**を使う方法です。

TEXT関数は、数値を指定した表示形式の文字列に変換してくれます。

例えば、A2セルに「123」が入っている場合:

– **6桁にしたい**:`=TEXT(A2,”000000″)` → 「000123」
– **7桁にしたい**:`=TEXT(A2,”0000000″)` → 「0000123」
– **8桁にしたい**:`=TEXT(A2,”00000000″)` → 「00000123」

数値を文字列に変換するので、先頭の「0」もデータとしてちゃんと保持されるのが大きなメリットです。

RIGHT関数でも同じことができる

同じ処理は、**RIGHT関数**を使っても実現できます。

RIGHT関数は文字列の右側から指定した文字数を取り出す関数で、あらかじめ左側に十分な数の「0」を付けてから右端の桁数だけ抜き出すと、ゼロ埋めされたコードが作れます。

– **6桁にしたい**:`=RIGHT(“000000″&A2,6)`
– **7桁にしたい**:`=RIGHT(“0000000″&A2,7)`
– **8桁にしたい**:`=RIGHT(“00000000″&A2,8)`

この方法は考え方が直感的で、元データが数値でも文字列でも扱いやすいのが良いところです。

TEXT関数とRIGHT関数、どっちを使う?

どちらを使うべきかは、データの性質で選ぶと良いです。

– **TEXT関数**:単純に数値を指定桁数のコードに変換したいとき
– **RIGHT関数**:元データに文字が混じる可能性がある場合や、後ろから固定桁だけ取得したいとき

ただし注意点があります。

元の値が指定桁数を超えている場合、TEXT関数はそのまま全桁を表示しますが、RIGHT関数は右側の指定桁数だけを返します。

例えば、A2が「123456789」のとき、8桁指定のRIGHT関数では「23456789」になってしまいます。

意図せず先頭が切れる可能性があるので、元データの桁数を確認してから使いましょう。

3. 指定した桁数だけ抽出するLEFT・RIGHT・MID・LENの使い方

基本の抽出関数3つ

桁数をそろえるだけでなく、文字列やコードの中から6桁、7桁、8桁を抽出したい場合は、次の関数を使います。

– **LEFT関数**:左から指定文字数を取り出す
– **RIGHT関数**:右から指定文字数を取り出す
– **MID関数**:指定位置から指定文字数を取り出す

例えば、A2に「AB-123456-XYZ」という文字列があって、中央の6桁だけを取り出すなら、文字の位置が固定であれば`=MID(A2,4,6)`のように指定できます。

よくある使い方

**右端の管理番号を取り出す**

A2に「TOKYO-00012345」のような値が入っていて、末尾8桁を抽出したいなら:
“`
=RIGHT(A2,8)
“`

**左端の分類コードを取り出す**

7桁の商品コードが常に先頭から始まる場合:
“`
=LEFT(A2,7)
“`

抽出した結果をさらに数値として扱いたい場合は、`=VALUE(RIGHT(A2,8))`のようにVALUE関数を組み合わせます。

ただし、先頭の「0」を残したいコードではVALUE関数を使わないように注意してください。

LEN関数で桁数を判定する

桁数そのものを判定してから抽出したい場合は、**LEN関数**が役立ちます。

LEN関数は文字数を数える関数です。

例えば、6桁のデータだけを抽出対象にしたいなら:
“`
=IF(LEN(A2)=6,A2,””)
“`

7桁や8桁も同じ考え方で、`=IF(LEN(A2)=7,A2,””)`、`=IF(LEN(A2)=8,A2,””)`とすれば、指定した桁数のデータだけを残せます。

**注意点**:数値として入力されているデータは先頭の「0」が消えている可能性があります。

厳密に桁数を判定したい場合は、最初から文字列として取り込むか、TEXT関数で桁数を補正してから判定するのが安全です。

区切り文字を基準に取り出す

抽出位置が一定ではなく、ハイフンやスペースなどの区切り文字を基準に取り出したい場合は、新しいExcelならTEXTBEFORE関数やTEXTAFTER関数が使えます。

例えば「部署-社員番号」の形式で、ハイフンより後ろの番号を取り出すなら:
“`
=TEXTAFTER(A2,”-“)
“`

ただし、すべてのExcelで使えるわけではないので、共有ファイルではLEFT、RIGHT、MID、FINDを組み合わせる方法も覚えておくと安心です。

4. 8桁の日付やコードを扱うときの注意点と実務での使い分け

8桁の数字は日付?それともコード?

8桁の数字は、商品コードや会員番号だけでなく、「20240601」のような日付データとして扱われることもあります。

この場合、単に「2024/06/01」のように見せるだけでいいのか、Excelの日付として計算できる状態にしたいのかで方法が変わります。

見た目だけ日付にする

見た目だけを「2024/06/01」にしたいなら、表示形式で`0000″/”00″/”00″`を指定する方法があります。

ただし、これはあくまで8桁の数値を日付のように表示しているだけなので、日数計算や月ごとの集計では期待どおりに動かないことがあります。

本当の日付データに変換する

日付として正しく扱いたい場合は、**DATE関数**とLEFT、MID、RIGHTを組み合わせる方法が確実です。

A2に「20240601」が入っているなら:
“`
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
“`

これでExcelが認識できる日付データに変換できます。

表示を「2024/6/1」にするか「2024年6月1日」にするかは、セルの表示形式で後から変更できます。

この方法なら、日付の差分計算、月別集計、フィルター、ピボットテーブルでの集計にも利用しやすくなります。

コードの場合は日付に変換しない

一方で、8桁の値が日付ではなくコードである場合は、DATE関数で変換してはいけません。

例えば「00001234」のような商品コードは日付ではないので、TEXT関数やRIGHT関数で文字列として保持するのが適切です。

実務では、同じ8桁でも「日付」「番号」「コード」で扱い方が異なります。

データの意味を確認せずに一括変換すると、先頭の「0」が消えたり、存在しない日付が別の日付に補正されたりする可能性があります。

CSVから取り込むときの注意

特にCSVから取り込んだデータは、Excelが自動で数値や日付に変換してしまうことがあります。

インポート時点で文字列として読み込む設定も検討しましょう。

まとめ:目的に合わせて方法を選ぼう

6桁・7桁・8桁の表示や抽出を安定させるには、作業の目的に合わせて方法を選ぶことが大切です。

– **印刷や画面確認だけ**:ユーザー定義の表示形式
– **別セルにゼロ埋め結果を作る**:TEXT関数
– **末尾や先頭から固定桁を取り出す**:RIGHT関数やLEFT関数
– **途中の桁を抽出する**:MID関数
– **桁数をチェックする**:LEN関数
– **日付化する**:DATE関数

Excelで桁数を指定して表示・抽出する操作は、関数の役割を理解して使い分ければ、手入力の修正を減らし、コード管理やデータ整形のミスを大きく減らせます。

ぜひこの記事を参考に、実務で活用してみてください!

広告