Excel(エクセル)で文字数が簡単に把握できるようにしてほしい、と思ったことはないですか？
エクセルで作成した文章など一定の文字数を超えてはいけない場面が多々あり、目視で1文字ずつ文字数をカウントしていては時間がかかります。
この記事では文字数をカウントする方法をパターン別に紹介します。
全角・半角のカウント、特殊文字のカウント、文字数を超過すると自動で色塗りする方法など様々紹介しています。
1つのセルの文字数をカウントする方法
「１つのセル」の文字数をカウントするには、LEN関数(レン)とLENB関数(レンビー)が大活躍します。
「LEN」とは、長さを意味する英語の「Length」の頭文字からきています。
つまり、文字列の長さ(数)を測るということです。
LENB関数の「B」は情報量の単位を表す「byte」(バイト)の頭文字から来ています。つまり、文字列のバイト数の長さ(数)を調べる関数です。
バイト数とは半角文字が1バイト、全角文字が2バイトとカウントします。
LEN関数を利用する(半角・全角を『１』でカウント)
LEN関数は、句読点やスペースや半角文字や全角文字を１文字としてカウントする関数です。
LEN関数の構文は下のとおりです。
＝LEN(文字列)
引数の「文字列」には、直接文字を入力するか、セル番号(A2等）を指定します。
下の図のように、「4月は春だ。」と入力されたB3セルに対して、LEN関数を挿入します。
そうすると、LEN関数の結果は「6」となり、「4月は春だ。」という文字列は6文字だということが分かります。
下の表のように、LEN関数は、半角文字、全角文字の区別はなく、すべて1文字としてカウントします。
「スペース」や「句読点」「ピリオド」なども、すべて「１」としてカウントされます。
| 文字 | LEN関数 | 説明 |
| 4 | 1 | 半角数字 |
| ４ | 1 | 全角数字 |
| カ | 1 | 全角カナ |
| ｶ | 1 | 半角カナ |
| 月 | 1 | 漢字 |
| 1 | 半角スペース | |
| 1 | 全角スペース | |
| 。 | 1 | 句点 |
| ． | 1 | ピリオド(全角) |
| . | 1 | ピリオド(半角) |
数式が挿入されたセルに対してLEN関数を利用するとどうカウントされるでしょう。
下の図のように、「=5*15*30」の数式の入力されたB3セルに対してLEN関数を使用すると、計算された結果「2250」に対して文字数は「4」とカウントされます。
1点注意が必要なのは、「Alt＋Enter」キーで改行されたセルに対して文字数をカウントすると、改行も”１”としてカウントされるので注意が必要です。
セルの中に「改行」がいくつ挿入されているか確認する方法を後半の見出しで紹介していますので、気になる方は参考にしてみてください。
LENB関数を利用する(全角を『２』でカウント)
LENB関数(レンビー)は、文字列のバイト数をカウントする関数です。
全角1文字を「２」バイト、半角1文字を「１」バイトとして数えます。
LENB関数の構文もLEN関数と同じです。
＝LENB(文字列)
引数の「文字列」には、直接文字を入力するか、セル番号(A2等）を指定します。
下の図のように、「4月は春だ。」という文字列に対して、「＝LENB(B3)」とセルにLENB関数を挿入すると、カウントした結果は「１１」となります。
6文字の2倍で「12」とならないのは、「4」が半角のため「１」バイトとしてカウントされているからです。
同じ文字列をLEN関数とLENB関数でカウントしましたが、下の図のとおり、当然結果に差が出ます。
次の見出しではこのLENB関数とLEN関数を組み合わせて利用することで、全角の文字数と半角の文字数をそれぞれカウントする方法を紹介します。
全角と半角の文字数をカウントする方法
文字数制限がかけられている場合、全角で何文字、半角で何文字と条件が付けられている場合があります。
この見出しでは、セルの中の「半角」と「全角」の文字数をカウントする方法を紹介します。
LEN関数は半角・全角関係なく文字列の文字数をカウントし、LENB関数は全角1文字を「２」、半角1文字を「１」とカウントします。
そのため、LENB関数で出た文字数から、LEN関数を引けば、全角文字の数がわかります。
LEN関数とLENB関数を利用する
先述のとおり、LENB関数は、全角1文字を「２」、半角１文字を「１」とカウントします。
この特性を利用して、以下の数式で半角の文字数をカウント出来ます。
＝(LEN関数の結果×２）- (LENB関数の結果）
下の図のように、「ｴｸｾﾙ関数」という文字列の半角文字数をカウントするために、「=LEN(B3)*2-LENB(B3)」という関数をC3セルに挿入すると、半角の「ｴｸｾﾙ」が「4」と正しくカウントされています。
全角の文字数は以下の数式でカウント出来ます。
＝(LENB関数の結果）- (LEN関数の結果）
下の図のように、「ｴｸｾﾙ関数」という文字列の全角文字数をカウントするために、「=LENB(B3)-LEN(B3)」という関数をD3セルに挿入すると、全角の「関数」という文字が「2」と正しくカウントされています。
複数のセルの文字数をカウントする方法
複数のセルの文字数を纏めてカウントする方法をご紹介します。
１つのセルに対して１つのLEN関数を挿入して、SUM関数で合計していてはLEN関数ばかりになってしまいます。
そんな時は、SUM関数の中にLEN関数を挿入ことで、複数セルの文字数を纏めてカウントすることが出来ます。
このSUM関数とLEN関数を組み合わせた使い方は、エクセルに「スピル」機能が備わっているかによって、操作が少しだけ異なります。
そのため、ExcelのバージョンがExcel2021以前か、Microsoft365(サブスク)を利用しているかによって、最後の操作手順だけ少し注意が必要です。
違いは数式を確定させる瞬間の手順だけなので、複雑な違いは無いよ。
LEN関数とSUM関数を利用する(複数のセルが隣接している場合）
まず、文字数をカウントするセルが一塊に隣接している場合の関数を作り方を説明します。
手順１．SUM関数を挿入
まず文字数をカウントしたいセルに、「＝SUM(」とSUM関数を挿入します。
手順２．SUM関数の中にLEN関数を挿入
次に、SUM関数の括弧「(」の中にLEN関数を挿入します。「=SUM(LEN(」
手順３．LEN関数の引数を設定
文字数をカウントするセル範囲を選択するために、マウス操作で対象のセル範囲をドラッグして選択します。
手順４．関数を完成させる
LEN関数を確定させるための「)」と、SUM関数を確定させるための「)」を入力して、関数を完成させます。
この段階で、まだ「Enter」キーを押して関数を確定させないで次の手順に移ってください。
手順5．関数を確定させる
この作業は注意が必要です。
Microsoft365を利用されている方は、「Enter」キーでSUM関数を確定させてください。
Excel2021以前のバージョンを利用されている方は、「Ctrl」キーと「Shift」キーを押しつづながら、「Enter」キーで関数を確定させてください。
Microsoft365
Microsoft365の場合、以下のように文字数の「9」が表示され、複数のセルの文字数が纏めてカウントすることが出来ます。
Excel2021以前
Excel2021以前のバージョンの場合、関数の先頭と最後に「{」「}」が挿入され、関数全体が括弧で囲われます。そして、以下のように文字数の「9」が表示され、複数のセルの文字数が纏めてカウントすることが出来ます。
もし、エラー値「#N/A」が表示された場合は、もう一度関数を確定させる手順からやり直してみてください。
LEN関数とSUM関数を利用する(複数のセルが離れている場合）
続いて、文字数をカウントするセルが離れている場合の関数を作り方を説明します。
基本的に上の見出しで紹介した方法と同じですが、セルを選択する方法だけが異なります。
手順１．SUM関数を挿入
まず文字数をカウントしたいセルに、「＝SUM(」とSUM関数を挿入します。
手順２．SUM関数の中にLEN関数を挿入
SUM関数の中にLEN関数を，(カンマ)で区切りながら挿入します。「=SUM(LEN(B3),LEN(B5),LEN(B7))」
手順３．関数を確定させる
「Enter」キーでSUM関数を確定させてください。そうすると、離れた複数のセルの文字数をカウントすることが出来ます。
特定の文字が入ったセル数をカウントする方法
文字列の中から「-」や「％」などの特定の文字が含まれたセルの数をカウントする方法を紹介します。
この場合は、文字数ではなくセルの数をカウントするので、LEN関数ではなくCOUNTIF関数を利用します。
COUNTIF関数を利用する
『COUNTIF関数』と『ワイルドカード』という「*」と「？」の特殊文字を利用することで、『～を含む』という条件で対象の文字列をカウントすることが出来ます。
ワイルドカードの便利な使い方はこちらの記事に纏めていますので、参考にしてみてください。
下のB2セルからB7セルの中から、①「A」から始まるセルの数と、➁「-」の後ろが1文字のセルの数をカウントする方法を紹介します。
まず、「A」から始まるセルの数をカウントする方法を紹介します。
利用するCOUNTIF関数は、条件に一致するセルの数をカウントする関数です。
COUNTIF関数の構文と引数は次のとおりです。
＝COUNTIF(範囲,検索条件)
この２つ目の引数「検索条件」に、カウントしたい条件を指定します。
この場合は、「0文字以上の任意の文字列」という意味を持つワイルドカード「*」を利用することで、「A*」と入力します。
=COUNTIF($B$2:$B$7,"A*")
「A*」で「A」で始まる文字列という意味となります。COUNTIF関数の結果は「4」となり、「A111-1」「A123-02」「A111-01」「A121-1」のセルを正しくカウント出来ています。
続いて、「-」の後ろが１文字のセルの数をカウントする方法を紹介します。
この場合は、「任意の１文字」という意味を持つワイルドカード「?」と組み合わせて、「*-?」と入力します。
=COUNTIF($B$3:$B$7,"*-?")
「*-?」で「-」で終わる１文字の文字列という意味となります。COUNTIF関数の結果は「３」となり、「A111-1」「B111-3」「A121-1」のセルを正しくカウント出来ています。
COUNTIF関数の詳しい使い方は以下の記事も合わせて参考にしてみてください。
セル内で改行されている数をカウントする方法
先述のとおり、LEN関数を普通に利用するとセル内の改行は「１」でカウントされてしまいます。
セル内に改行があると、「検索」機能などを利用する際に上手くいきません。
そんな時のために、セル内に改行が何個あるかカウントする方法を紹介します。
LEN関数とSUBSTITUTE関数を利用
セル内の改行をカウントするためには、LEN関数とSUBSTITUTE関数を利用します。
SUBSTITUTE関数は、検索した文字列を、指定した違う文字列に置き換えられる関数です。
SUBSTITUTE関数を使って改行が無い場合のセルの文字数から、改行があるセルの文字数を引くことで、改行の数を調べる、という理屈です。
利用する関数は以下となります。CHAR(10)はエクセルの改行を表すコードで、「””」は空白を表します。
＝SUBSTITUTE(文字列,CHAR(10),””)
つまり、このSUBSTITUTE関数の意味は、「文字列の改行を空白に置き換える」を表します。
それでは実際に、上のSUBSTITUTE関数とLEN関数を使って改行の数をカウントする方法を紹介します。
手順１．LEN関数で文字数をカウント
まず改行されているセルの文字数をLEN関数でカウントします。「＝LEN(B3)」
手順２．改行を除いた文字数をカウント
次に、隣の空いているセルに改行を除いた文字数をカウントするための関数を挿入します。
LEN関数の中にSUBSTITUTE関数を挿入した以下の関数を挿入します。
=LEN(SUBSTITUTE(B3,CHAR(10),""))
関数の結果は「6」となり、手順１のLEN関数の結果である「8」と比較して、「2」少ないことが分かります。
手順３．改行された数を算出
手順１の結果から手順2の結果を引き算することで、改行された数をカウントすることが出来ます。「＝C3-D3」
説明用に分かりやすくするため、手順１と２のセルを分けましたが、以下のように数式を纏めて利用すれば１つのセルだけで改行数をカウントすることが出来ます。
=LEN(B3)-LEN(SUBSTITUTE(B3,CHAR(10),""))
以下の記事で「改行を解除」する方法を紹介していますので、興味のある方は合わせて参考にしてみてください。
日付や％の文字数を正しくカウントする方法
LEN関数で「日付」や「％」表示されたセルの文字数をカウントすると、下のように正しく文字数がカウントされません。
「2023/6/5」のセルは、「20230605」なので「8」文字とカウントしてほしいのに、結果は「5」でカウントされています。
また、「15％」のセルは、３文字なので「3」とカウントしてほしいのに、結果は「4」とカウントされています。
これは、エクセルがセルの表示形式を見た目上だけ変更しているためです。
LEN関数とTEXT関数を利用する
この表示形式に合わせた文字数のカウントを行うためには、LEN関数だけでなくTEXT関数も利用する必要があります。
TEXT関数は、セルの数値を表示形式を設定して文字列に変換する関数です。
日付をカウントするためには、以下のようにLEN関数の中にTEXT関数を挿入します。
=LEN(TEXT(B3,"yyyymmdd"))
その結果、「2023/6/5」のセルは、「8」文字と正しくカウントされました。
次に、「％」のセルを正しくカウントするためには、以下のようにLEN関数の中にTEXT関数を挿入します。
=LEN(TEXT(B4,"0％"))
その結果、「15％」のセルは、「3」文字と正しくカウントされました。
特定の文字数で制限をかける方法
文字数をカウントするだけでなく、セルの文字数によって、セルを目立たせたり、入力出来ないように制限を変えたり、〇×判定を行うことも出来ます。
特定の文字数で【〇】【×】判定する方法
IF関数とLEN関数を利用することで、文字数を【〇】【×】判定することが出来ます。
６文字以上は【×】、6文字未満は【〇】と判定するために、以下のIF関数を挿入します。
=IF(LEN(B3)>=6,"×","〇")
IF関数は条件を満たすかどうかによって返す値を変えることが出来る関数です。
=IF(論理式,[値が真の場合],[値が偽の場合])
上の図のとおり、６文字以上か6文字未満かによって、【〇】か【×】で返す値を変えることが出来ました。
IF関数の使い方を復習したい方は以下の記事を参考にしてください。
文字数を超えると色を表示する方法
特定の文字数を超えると、自動でセルの塗りつぶしの色やフォントの色が変わるように設定することも出来ます。
今回は下のB列から、文字数が６文字以上のセルに「黄色塗りつぶし」が自動で適用される設定手順を紹介します。
手順１．データを選択し「新しいルール」をクリック
自動で色付けしたいセル範囲を選択します。次に「ホーム」タブ⇒「条件付き書式」の「新しいルール」をクリックします。
手順２．「新しいルール」画面でルールを設定
「新しいルール」画面で「数式を使用して、書式設定するセルを決定」を選択します。
手順３．条件にLEN関数を設定
画面の下半分のルールの内容の編集欄に、6文字以上を数式で表すために以下のLEN関数を入力します。関数を入力し終えたら、画面右下の「書式」をクリックします。
=LEN(B3)>=6
手順４．書式を設定
「セルの書式設定」画面の「塗りつぶし」タブから、色を選択して「OK」を押します。
フォントの色を変えたい場合は、「フォント」タブから選択してください。
手順５．設定が完了
ルールと書式を設定し終えたので、「新しい書式ルール」画面を「OK」で閉じます。以上で書式の設定は完了です。
手順６．色塗りつぶしの結果を確認
そうすると、６文字以上のセルに自動で色が塗られました。
このように「条件付き書式」機能を利用することで、特定の範囲の文字数を満たすセルに色塗りなどの書式を設定することが出来ます。
特定の文字数で「注意」や「警告」を表示する方法
Excelには文字数を制限できる機能が備わっています。
文字数を超過した際に、以下の３つの表示スタイルを利用することが出来ます。
停止
１つ目は「停止」スタイルです。「Enter」で入力を確定しようとすると、強制的に修正させられます。
注意
２つ目は「注意」スタイルです。「Enter」キーで入力を確定しようとすると、修正するか、確定するかを求められます。
情報
3つ目は「情報」スタイルです。修正を促されますが、修正は強制させられません。
設定手順は以下の記事で紹介していますので、機能に興味のある方は参考にしてみてください。
