Excelで氏名、商品コード、電話番号、金額などを扱っていると、セルに入力された文字列や数字が何桁なのかを確認したい場面があります。
桁数のカウントにはLEN関数を使うのが基本ですが、半角と全角、空白、数式の結果、先頭のゼロなどによって表示結果が変わることもあります。
この記事では、エクセルで文字数や数字の桁数を数える関数、条件付きで判定する方法、実務でつまずきやすい注意点を詳しく解説していきます。
Excelでセル内の桁数を数える基本関数はLEN関数です。
文字列、数値、記号、空白を含めた文字数を確認でき、データ入力規則やコード番号のチェックにも活用できます。
Excelで桁数を数えるLEN関数の基本
それではまず、エクセルで文字列や数字の桁数をカウントする基本のLEN関数について解説していきます。
| A列 | B列 | |
|---|---|---|
| 1 | 商品コード | 桁数 |
| 2 | AB-12345 | 8 |
| 3 | 987654 | 6 |
LEN関数の書式
LEN関数は、指定したセルまたは文字列に含まれる文字数を返す関数です。
数式
=LEN(A2)
上記の式をB2セルに入力すると、A2セルのAB-12345はハイフンも含めて8文字と判定されます。
LEN関数は数字だけではなく、英字、漢字、記号、空白も原則として1文字ずつ数えます。
1行目を見出しにした表であれば、データが入っている2行目のB2セルに数式を入力し、フィルハンドルを下へドラッグしてオートフィルすると便利です。
【操作のポイント】数える対象がA2セルの場合は、結果を表示するセルに=LEN(A2)と入力します。
数字の桁数を数える方法
A2セルに12345と入力されている場合、=LEN(A2)の結果は5になります。
数値として入力したデータでも、LEN関数は表示上の文字列として扱い、数字の個数を返します。
たとえば売上番号、管理番号、郵便番号のように、決められた桁数で入力されているかを確認したい場合にLEN関数は有効です。
ただし、セルの表示形式で桁区切りのカンマを表示していても、通常は元の数値を基準にカウントされます。
1234567に表示形式で1,234,567と表示していても、=LEN(A2)の結果は7です。
画面に見えているカンマを含めた文字数を数えたいときは、TEXT関数で表示形式を文字列化してからLEN関数を組み合わせます。
=LEN(TEXT(A2,”#,##0″))
この式なら、1,234,567を9文字としてカウントできます。
【操作のポイント】元データの桁数か、表示された文字数かを先に決めることが重要です。
文字列と数値の違い
Excelでは、12345という数値と、文字列として入力された「12345」は見た目が同じでも、計算時の扱いが異なります。
ただしLEN関数で桁数を確認する目的では、どちらも5という結果になるケースが一般的です。
違いが大きく出るのは、先頭にゼロが付くデータです。
たとえば00123を数値として入力すると、Excelは通常123として保存するため、LEN関数の結果も3になります。
郵便番号、社員番号、商品コードなど先頭ゼロを残す必要がある値は、セルを文字列形式にして入力します。
先頭にアポストロフィを付けて’00123と入力する方法もありますが、データの統一を考えるなら列全体を文字列形式にする方法が扱いやすいでしょう。
【操作のポイント】先頭ゼロを含む桁数を確認したい場合は、入力前に対象列の表示形式を文字列へ変更します。
文字列と空白を含む桁数の確認
続いては、文字列、スペース、改行などを含むセルでの桁数カウントを確認していきます。
| A列 | B列 | |
|---|---|---|
| 1 | 入力値 | LENの結果 |
| 2 | 山田 太郎 | 5 |
| 3 | 東京 営業部 | 6 |
半角スペースと全角スペース
氏名の間に入る半角スペース、部署名に入る全角スペースも、LEN関数ではそれぞれ1文字として数えられます。
山田 太郎は、山、田、半角スペース、太、郎で5文字です。
東京 営業部も、東、京、全角スペース、営、業、部で6文字になります。
見た目だけでは気付きにくいスペースも桁数に含まれるため、入力チェックでは注意が必要です。
特にWebサイトや別システムから貼り付けたデータには、意図しない空白が先頭や末尾に混入することがあります。
そのままLEN関数で判定すると、本来は5文字のはずなのに6文字以上という結果になるかもしれません。
【操作のポイント】氏名やコードの桁数が合わないときは、セル内の先頭、末尾、途中に空白がないか確認します。
余分な空白を除いて数える方法
不要な半角スペースを削除してから文字数を数えたい場合は、TRIM関数とLEN関数を組み合わせます。
=LEN(TRIM(A2))
TRIM関数は文字列の先頭と末尾の半角スペースを削除し、単語間に連続する半角スペースを1つに整えます。
ただし、全角スペースはTRIM関数だけでは削除できません。
全角スペースも除外したい場合は、SUBSTITUTE関数を組み合わせる方法が便利です。
=LEN(SUBSTITUTE(A2,” “,””))
この式では、A2セルに含まれる全角スペースを空文字へ置き換えた後の文字数を返します。
空白を除いた文字数を求める式と、入力された内容そのものを数える式は用途が異なります。
住所や文章の文字数を調べる場合は空白も情報になり得るため、むやみに除外しないことが大切です。
【操作のポイント】半角空白はTRIM関数、全角空白はSUBSTITUTE関数で処理する考え方です。
改行や記号を含めた文字数
セル内でAltキーを押しながらEnterキーを押して改行した場合、その改行もLEN関数では1文字として数えられます。
たとえばセル内に商品名と規格を2行で入力していると、見た目の文字数とLEN関数の結果が1つずれることがあります。
改行を除外して数えるには、SUBSTITUTE関数でCHAR(10)を削除します。
=LEN(SUBSTITUTE(A2,CHAR(10),””))
ハイフン、スラッシュ、丸括弧、句読点も1文字として扱われます。
文字数制限がある入力フォームへ転記する前には、記号や改行を含めるルールを確認しましょう。
Excelで正しい桁数に見えても、転記先のシステムでは入力エラーになることがあります。
【操作のポイント】セル内改行を除外する場合はCHAR(10)をSUBSTITUTE関数で空欄に置換します。
数字の桁数を条件付きで判定する方法
続いては、指定桁数になっているかを自動で判定する方法を確認していきます。
| A列 | B列 | |
|---|---|---|
| 1 | 社員番号 | 判定 |
| 2 | 123456 | OK |
| 3 | 12345 | 桁数エラー |
指定した桁数と一致するかの判定
6桁の社員番号が正しく入力されているかを確認する場合は、IF関数とLEN関数を組み合わせます。
=IF(LEN(A2)=6,”OK”,”桁数エラー”)
この数式をB2セルに入力すると、A2セルが6文字ならOK、それ以外なら桁数エラーと表示されます。
桁数の目視確認を減らしたいときは、判定列を設ける方法がシンプルで確実です。
数式をB2セルから下方向へコピーすれば、複数行のデータを一括確認できます。
空欄の行までエラーと表示したくない場合は、最初にA2セルが空欄かどうかを判定します。
=IF(A2=””,””,IF(LEN(A2)=6,”OK”,”桁数エラー”))
【操作のポイント】空欄を判定対象から外すと、未入力行が多い表でも見やすくなります。
指定桁数以外を色分けする条件付き書式
判定結果を別の列に出さず、入力セルそのものを色分けしたい場合は条件付き書式を使用します。
対象範囲がA2からA100の場合は、その範囲を選択してから条件付き書式の新しいルールを開きます。
数式を使用して書式設定するセルを決定する項目で、次の数式を入力します。
=LEN(A2)<>6
書式で薄い赤などを指定すると、6桁以外のセルだけが自動的に強調されます。
条件付き書式では、選択範囲の左上セルであるA2を基準に式を書くのが基本です。
文字列として保存した番号でも数値として入力した番号でも、LEN関数を使った判定は利用できます。
【操作のポイント】条件付き書式の数式では、セル参照の列記号や行番号を固定しすぎないよう注意します。
数値だけを対象にする確認式
商品コードのように英字と数字が混在してよい項目ではなく、数字だけの固定桁番号を確認したい場合もあります。
その場合はISNUMBER関数を組み合わせる方法があります。
=IF(AND(ISNUMBER(A2),LEN(A2)=6),”OK”,”入力内容を確認”)
この式では、A2セルが数値であり、かつ6桁である場合だけOKと表示します。
ただし先頭ゼロを残す番号は文字列として保存する必要があるため、ISNUMBER関数を使うと不適切な判定になります。
郵便番号や口座番号のように先頭ゼロがあり得る項目は、数値判定ではなく文字列としての桁数判定を優先します。
データの性質に合わせて、数値か文字列かを決めることが実務上の重要なポイントです。
【操作のポイント】先頭ゼロが必要なコードは、数値チェックよりも文字列の桁数チェックに向いています。
数式バーとオートフィルによる桁数確認
続いては、LEN関数を入力し、オートフィルで複数のデータへ適用する操作を確認していきます。
| A列 | B列 | |
|---|---|---|
| 1 | 顧客コード | 文字数 |
| 2 | C-10025 | 7 |
| 3 | C-10026 | 7 |
数式バーへのLEN関数入力
結果を表示したいB2セルを選択し、数式バーまたはセルへ=LEN(A2)と入力します。
Enterキーを押すと、A2セルの顧客コードに含まれる文字数がB2セルへ表示されます。
参照先のセル番地をクリックして指定すると、入力ミスを減らせるでしょう。
【操作のポイント】列見出しが1行目にある表では、数式は通常2行目から入力します。
オートフィルによる数式コピー
B2セルを選択すると、セル右下に小さな四角形のフィルハンドルが表示されます。
フィルハンドルを下方向へドラッグすると、B3、B4以降へ数式がコピーされ、参照先はA3、A4のように自動調整されます。
| A | B | |
|---|---|---|
| 1 | 顧客コード | 文字数 |
| 2 | C-10025 | 7 |
| 3 | C-10026 | 7 |
| 4 | C-10027 | 7 |
オートフィルを使うと、1件ずつLEN関数を入力する手間を省けます。
隣接するA列に連続データがある場合は、フィルハンドルをダブルクリックして最終行付近まで自動コピーすることもできます。
【操作のポイント】数式が入った最初のセルのフィルハンドルを利用して、下の行へ参照式を展開します。
数式の結果を値として残す方法
桁数チェックが終わった後に、B列の結果だけを固定して残したいことがあります。
数式セルをコピーし、貼り付け先で値として貼り付けを選ぶと、LEN関数ではなく計算結果の数字だけを保存できます。
元のA列を後から変更しても桁数結果を変えたくない場合には便利な操作です。
一方、入力内容の変更に合わせて常に判定を更新したい表では、数式をそのまま残します。
桁数チェックを一時的な確認に使うのか、継続管理に使うのかで貼り付け方法を選びましょう。
【操作のポイント】値として貼り付けると、元セルの変更による再計算は行われなくなります。
桁数カウントで起こりやすいエラーと対処
続いては、LEN関数で桁数を数えるときに起こりやすい問題と対処方法を確認していきます。
| A列 | B列 | |
|---|---|---|
| 1 | 確認内容 | 対処例 |
| 2 | 先頭ゼロが消える | 文字列形式に変更 |
| 3 | 文字数が多い | 空白や改行を確認 |
エラー値が含まれるセル
参照先セルが#N/Aや#VALUE!などのエラーになっている場合、LEN関数もエラーを返します。
エラーが出ても処理を止めず、空欄や任意のメッセージを表示したい場合はIFERROR関数を使用します。
=IFERROR(LEN(A2),””)
この式なら、A2セルでエラーが発生しているときは空欄を表示し、それ以外では通常どおり桁数を返します。
IFERROR関数は便利ですが、元データの異常を見逃す可能性もあるため、重要な一覧では別途エラー確認を行います。
【操作のポイント】エラーを隠す目的だけでなく、元のセルで何が起きているかも確認します。
小数点やマイナス記号の扱い
-123.45のような数値をLEN関数で数えると、数値の内部表現や表示形式によって想定と異なる結果になることがあります。
桁数として整数部だけを数えたいのか、小数点や符号も含む入力文字数を数えたいのかを明確にしましょう。
整数部の数字だけを確認したい場合は、ABS関数とINT関数などを組み合わせる方法があります。
=LEN(INT(ABS(A2)))
たとえばA2が-123.45なら、ABS関数で絶対値にし、INT関数で小数部分を切り捨て、結果の123を3桁として数えます。
金額や数量の桁数チェックでは、マイナス記号と小数点を数に含めるかを業務ルールに合わせます。
【操作のポイント】数値の桁数と、画面に入力された文字数は別の基準として考えます。
全角数字と半角数字の確認
全角の１２３と半角の123は、LEN関数ではどちらも3文字です。
しかし、外部システムへの登録やCSV出力では全角数字がエラー原因になる場合があります。
半角文字へ統一したい場合は、ASC関数を使用できます。
=LEN(ASC(A2))
ASC関数は全角の英数字や記号を半角へ変換した文字列を返します。
文字数が同じでも文字種が異なると入力エラーになることがあるため、桁数と文字種を分けて確認しましょう。
【操作のポイント】外部システムと連携するデータでは、半角と全角の統一ルールを事前に確認します。
まとめ エクセルで桁数をカウントする関数
Excelで文字列や数字の桁数を数える基本は、=LEN(A2)のようにLEN関数を使う方法です。
数字、英字、漢字、記号、半角スペース、全角スペース、セル内改行は、基本的にそれぞれ1文字としてカウントされます。
固定桁のコードや番号を確認する場合は、LEN関数とIF関数を組み合わせてOKとエラーを自動判定すると効率的です。
先頭ゼロを保持する必要があるデータは文字列として管理し、空白や改行を除外したい場合はTRIM関数やSUBSTITUTE関数を組み合わせましょう。
さらに条件付き書式を使えば、指定桁数以外のセルを色分けでき、入力ミスを早い段階で見つけやすくなります。
桁数だけでなく、数値か文字列か、全角か半角か、表示文字数か元データの文字数かを意識して、目的に合った数式を選ぶことが大切です。
