顧客コードや社員番号、郵便番号のように「桁数が決まっているデータ」を扱う場面は経理・総務・営業事務のどこでも登場します。手入力のExcel表では、桁数が1つ多い・少ないといったミスが混入しやすく、あとからシステムに取り込む段階で初めてエラーに気づく、ということも珍しくありません。LEN関数はセルの文字数を数えるだけのシンプルな関数ですが、入力規則や条件付き書式、SUBSTITUTE関数と組み合わせることで、入力段階でのミスを未然に防ぐ強力なチェック機能に育てられます。

LEN関数の基本構文

LEN関数は、指定したセルの文字数(または数値の桁数)を数値として返します。引数は対象セル1つだけで、非常にシンプルです。

=LEN(文字列)

例:A1に「東京都渋谷区」と入力されている場合
=LEN(A1) → 7(全角・半角を区別せず1文字を1として数える)
例:A2に数値の1000が入力されている場合
=LEN(A2) → 4(表示形式で「1,000」と見えていても、実際の値の桁数を数える)

入力規則と組み合わせて文字数制限をかける

「名前欄は全角20文字以内」「備考欄は100文字まで」のようなルールを、入力した後に気づかせるのではなく、入力した瞬間に弾く仕組みにしたい場合は、データの入力規則にLEN関数を組み込みます。

  1. 制限をかけたいセル範囲(例:B2:B100)を選択する
  2. 「データ」タブ→「データの入力規則」を開く
  3. 「設定」タブで入力値の種類を「ユーザー設定」にする
  4. 数式欄に =LEN(B2)<=20 と入力する
  5. 「エラーメッセージ」タブで、桁数オーバー時に表示する注意文を設定して「OK」
=LEN(B2)<=20

B2に21文字以上を入力しようとすると、設定したエラーメッセージが表示されて入力が確定できなくなります。範囲の先頭セル(この例ではB2)を基準に数式を作る点は条件付き書式と同じ考え方です。

SUBSTITUTE関数と組み合わせて特定の文字の出現回数を数える

LEN関数は「特定の文字が何回出てくるか」を数える用途でも使われます。対象の文字を空文字に置き換えたときの文字数の減り具合から、出現回数を逆算する考え方です。

=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

A1の文字列からカンマを取り除いた文字列とのLENの差が、そのままカンマの出現回数になります。CSV形式で受け取ったデータの区切り文字の数を検証したいときや、氏名欄に複数人の名前が「、」区切りで入っていないかをチェックしたいときに使えます。

実務でのよくある使用シーン

総務担当者であれば、社員番号や部署コードの桁数が統一されているかをLEN関数付きの監視列でチェックし、桁数違反の行だけを条件付き書式で赤く表示することで、新入社員の名簿登録時のミスを早期に発見できます。経理担当者であれば、振込先の口座番号や取引先コードの桁数チェックに使い、システムへの一括取込前にLENで桁数不一致の行を洗い出す運用ができます。営業事務担当者であれば、備考欄や住所欄に文字数制限のあるシステムへ登録する前段階で、Excel上でLEN関数によるチェック列を用意し、超過している行だけを事前に修正してから登録する、という使い方が定着しています。

LEN関数でよくあるエラー・トラブルと対処法

全角スペースが混ざっていて文字数がずれる

TRIM関数は既定では半角スペースしか取り除けません。氏名欄などに全角スペース(「山田　太郎」の間の空白)が混入していると、TRIMしても文字数が減らず、想定より1文字多い状態のままLENの結果を得てしまいます。

対処法:=LEN(SUBSTITUTE(TRIM(A1),"　",""))のように、TRIMで半角スペースを、SUBSTITUTEで全角スペースを別々に除去してから数える

TRIM単体で対処しようとして「全角スペースが消えない」と悩むケースが非常に多いため、全角・半角それぞれの空白を意識して処理を分けることが重要です。

改行(Alt+Enter)を含むセルで文字数が想定より多い

1つのセル内でAlt+Enterによる改行を使って複数行にわたる文章を入力している場合、その改行そのものも1文字としてLENにカウントされます。目視では気づきにくいため、「見た目の文字数と合わない」という問い合わせの原因になりがちです。

対処法:=LEN(SUBSTITUTE(A1,CHAR(10),""))で改行(CHAR(10))を除去してから文字数を数える

備考欄など長文を入力する欄で文字数制限をかける場合は、改行を許可するのかどうかを事前に決め、許可しないのであれば入力規則側でも改行を含む入力を弾く設計にしておくと混乱が減ります。

数値セルの表示形式とLENの結果が食い違う

セルの表示形式(桁区切り、円マーク、日付表示など)は見た目を変えるだけで、セルが保持する実際の値には影響しません。「1,000」と表示されているセルでも、LENが数えるのは実際の値である1000の4文字であり、カンマは含まれません。

例:表示形式で「¥1,000」と見えるセルでも=LEN(A1)は4を返す(実際の値は1000という数値)

桁区切りやハイフンを含めた「見た目通りの桁数」をチェックしたい場合は、あらかじめ文字列としてハイフンなどを含めて入力しておくか、TEXT関数で表示形式を反映した文字列に変換してからLENで数える必要があります。

初心者がつまずきやすいポイントとコツ

まとめ

LEN関数は文字数を数えるだけのシンプルな関数ですが、入力規則と組み合わせれば文字数超過そのものを防ぐ仕組みになり、SUBSTITUTE関数と組み合わせれば特定の文字の出現回数を数えることもできます。実務でつまずきやすいのは、全角スペース・改行といった「見えない文字」の存在と、セルの表示形式と実際の値の違いです。この2点さえ意識しておけば、LEN関数を使った文字数チェックは安定して運用できます。

よくある質問

LEN関数で数えた文字数と、セルに表示されている桁数が合わないのはなぜですか？
セルの表示形式(桁区切りや円マーク、日付表示など)は見た目だけを変えるものであり、LEN関数はセルが実際に保持している値(数値そのものや入力した文字列そのもの)を数えます。たとえば1000という数値に桁区切り表示を設定して「1,000」と見えていても、LEN(A1)は4文字と数えます。逆に郵便番号を文字列として「123-4567」のようにハイフン込みで入力している場合は、見た目通り8文字として数えられます。表示上の見た目とセルの実際の値は別物だと意識することが、LEN関数を正しく使う第一歩です。
全角スペースが入っているのにTRIMで消せないのですが、LENで何がわかりますか？
TRIM関数が既定で除去できるのは半角スペースのみで、全角スペースはそのまま残ります。LEN(A1)で数えた文字数と、目視で数えた文字数が一致しないときは、見えないスペースや改行が紛れ込んでいる可能性が高いというサインになります。原因を特定したい場合は、LEN(A1)とLEN(TRIM(A1))を並べて比較し、値が同じであれば半角スペースの問題ではないと判断できます。全角スペースを除去したいときはSUBSTITUTE(A1,"　","")のように全角スペースを直接指定して置き換える必要があります。
入力規則で文字数を制限しても、あとから貼り付けで長い文字列が入ってしまいます。防げますか？
データの入力規則は、セルに直接入力したりEnterで確定したりする操作に対しては有効ですが、Excelのバージョンや貼り付け方法によっては、コピー&貼り付けの操作で入力規則のチェックが効かずにそのまま値が入ってしまうことがあります。これは入力規則そのものの仕様上の制約であり、LEN関数の問題ではありません。貼り付けによる文字数超過を確実に防ぎたい場合は、入力規則に加えて、別セルにLEN関数で文字数を表示する監視用の列を用意し、条件付き書式で規定文字数を超えたセルを赤く表示するなど、二重のチェック体制を組むことをおすすめします。

関連記事

→ 文字列の数式を作ってみる ← 記事一覧に戻る