Contents
スプレッドシートのLEN関数とは
スプレッドシートのLEN関数は、指定したセルの文字列の長さを調べるためのものです。半角文字、全角文字、数字、空白、句読点などを全て1文字としてカウントします。LEN関数単体で使うほか、LEFT関数やMID関数との組み合わせで使う機会もあります。
LEN関数の構文
=LEN(文字列)- 文字列:文字数を確認したい文字列ないし単一セル
文字列を直接入れたい場合には「=LEN(“ピカチュウ”)」のように「”(ダブルクォーテーション)」で囲みましょう！
LEN関数の使い方
[基本]文字数をカウント
LEN関数の使い方としては、文字数カウントしたい単一セルを入力します。たとえば下図のように「=LEN(A1)」と入力するとA1セルの文字数をカウントしてくれます。
また「”(ダブルクォーテーション)」で囲めば文字列を直接入れてもカウントしてくれます。
[応用1]特定の文字を除いて数える
特定の文字や改行を除いて数える場合には、事前にSUBSTITUTE関数にて不要な文字を除きましょう。下図のA2セルでは「！」を除くために「=SUBSTITUTE(A1,”！”,””)」と記載しています。
SUBSTITUTE関数は〇〇からXXに置き換えるための関数ですが、XXを””のように文字なしで設定すると単に〇〇を削除する役割を果たします！
また、B2セルには「=LEN(A2)」とかいて、「！」を除いた後の文字数をカウントしています。
同様に「…」や「 (半角スペース)」も段階的に削除してみました。SUBSTITUTE関数を1回使うごとに1種類の文字列しか置き換えないし削除はできないので、複数種類の文字列を削除したい場合には都度SUBSTITUTE関数が必要です。
SUBSTITUTE関数を複数回使う
SUBSTITUTE関数を1つのセル内で重ね掛けして、なおかつLEN関数で文字数を数える書き方も可能です。下図では「=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,”！”,””),”…”,””),” “,””))」のように書いています。
関数は内側から処理すると考えやすいです。
- 「SUBSTITUTE(A1,”！”,””)」でA1セルから「！」を取り除く
- 「SUBSTITUTE(①,”…”,””)」で①から「…」を取り除く
- 「SUBSTITUTE(②,” “,””)」で②から半角スペースを取り除く
- 「LEN(③)」で③の文字数を数える
1つのセル内で上記のような計算をした結果、178という文字数がカウントされています。
単一セル内で複数のSUBSTITUTE関数を使うのにはメリットとデメリットがあります。メリットは他のセルに表示をさせず見た目がスッキリする点、反対にデメリットは後から修正したいときに仕組みがわかりづらい点です。
見た目を気にすべきか、後任への引き継ぎを意識するかで使い分けると良いでしょう！
[応用2]改行を除いて数える
スプレッドシートのLEN関数は改行を含めて文字数にカウントしています。もし改行を削除したいのであれば、SUBSTITUTE関数での変換が必要です。
スプレッドシートで改行を表す際はCHAR関数を用いて「CHAR(10)」とします。たとえば今回の置換では「=SUBSTITUTE(A6,CHAR(10),””)」とすることで、改行を削除できます。
[応用3]条件付き書式で文字数に対応させて色をつける
LEN関数と条件付き書式を組み合わせれば、特定の文字数や◯文字以上といった条件でセルに色をつけられます。たとえば、下図において4文字の文字列に色をつけたいとします。
まずは、対象の範囲を囲って右クリック＞「セルで他の操作項目を表示」＞「条件付き書式」を選択します。
「書式ルール」を「カスタム数式」へ変更します。
対象範囲の1番上のセルを事前に確認しておいて「=LEN($”範囲の先頭セル”)=”色付けしたい文字数”」となるように調整します。今回の例では対象の範囲が「B2:B19」であり、4文字のときに色付けしたいので「=LEN($B2)=4」とします。
すると、下図のように対象範囲のうち4文字のセルに色がつきました。
今回は4文字のときに色をつけましたが、設定によっては「5文字より小さいとき」や「4文字以外のとき」のような設定もできるので、状況にあわせて使い分けてみましょう。
LEN関数に関連した関数
LENB関数
LENB関数は、バイト数を数える関数です。半角文字が1、全角文字が2として数えられる文字数カウントという捉え方でおおよそ問題ないです。
=LEN(文字列)- 文字列:バイト数を確認したい文字列ないし単一セル
たとえばLEN関数では254だった文章も、LENB関数では459としてカウントされます。日本語は全角文字なため、基本的に1文字2バイトとして数えられます。
ただし記号によっては4以上としてカウントされる場合もあるので、LENB関数を使う際には注意しましょう。
LEN関数の注意点
複数行には対応していない
LEN関数のかっこ内は単一セルにのみ対応しているので、「=LEN(B2:B19)」のようにセルの範囲指定はできません。
もしまとめて文字数を数えたいのであれば、CONCATENATE関数を用いて「=CONCATENATE(B2:B19)」のように文字列を結合してからLEN関数を使うとよいでしょう。
また「=LEN(CONCATENATE(B2:B19))」のようにまとめて1つのセルでカウントしてもOKです。
表示形式により表示される文字も数える
LEN関数は表示形式により現れる文字も数えるので注意しましょう。たとえば下図では「A2」セルは1文字としてカウントされていますが、これは書式設定により変わります。
試しに、書式を「数値」にして小数点以下1桁まで表示してみます。
この場合、数字自体は1のままですが、表示が「1.0」になったことで文字数は3としてカウントされました。そのため、見た目の文字数を数えたいのか、システム上の文字数を数えたいのかは区別して考えましょう。
