Excel(エクセル)で文字数をカウントする|半角・全角、複数範囲、特定文字など

Excel(エクセル)で文字数をカウントする|半角・全角、複数範囲、特定文字など
Excel

Excel(エクセル)で文字数をカウントする|半角・全角、複数範囲、特定文字など

使用関数:LEN、LENB、SUBSTITUTE、CHAR

LEN・LENB関数で文字数を数える

LENとLENBは共に文字の長さ、文字数をカウントしてくれる関数です。
両者の決定的な違いはカウントする単位にあります。LENは文字そのものの数をそのまま返し、LENBは文字のバイト数を返します。

全角・半角にこだわらず、文字だけを数えたい

とにかくセル内の文字の数を素直に知りたい時にはLEN関数を使います。全角でも半角でも文字列でも数値でも1とカウントします。
LEN関数で文字を数える

英文の文字数が「13」になっています。単語の間の空白も1文字としてカウントされるということです。空白の他、句読点も文字としてカウントされます。
LEN関数では空白も1文字としてカウントされる

LENB関数はバイト数で半角の文字を「1」全角を「2」と数える

LENBはバイトという単位で文字をカウントします。
半角文字が基準になっており、日本語の全角の文字列は「2」とカウントされます。
全角の文字列はLENの2倍の数値になっていますね。
LENBはバイト数で文字をカウントする

LEN・LENB関数を組み合わせて全角・半角の文字数を別個に数える

全角と半角が混在している文字列から、全角・半角それぞれの文字数を数えるにはLEN関数とLENB関数を組み合わせて実行します。

全角のカウント

LENBは半角文字をそのまま数え、全角文字を2倍に数えます。
LENは半角も全角もそのまま数えます。
LENBからLENを引き算すれば、全角文字の数が残ります。
LENBからLENを引き算すれば、全角文字の数が返る

半角のカウント

LENは半角も全角もそのまま数えます。これを*2で倍にします。半角が2倍、全角が2倍です。
LENBは半角文字をそのまま数え、全角文字を2倍に数えます。
LEN×2からLENBを引き算すれば、半角文字の数が残ります。
LENの2倍からLENBを引き算すれば、半角文字の数が返る

複数のセルの文字数をカウントするには?

~Excel2016での指定

LEN関数では「A2:A5」のように複数のセル範囲を指定できません。複数のセルを指定するには「&」でセルをつないでいきます。
LEN関数で複数のセル範囲を指定
数式は =LEN(A2&A3&A4&A5)

LENB関数でも同じです。
LENB関数で複数のセル範囲を指定

離れたセル、飛び飛びのセルでも上記の方法で指定できます。
LENB関数で離れたセル、飛び飛びのセルを指定

Excel2019、2021、365ではセル範囲をまとめて指定できる

LEN関数では「A2:A5」のように複数のセル範囲を指定できないと上に書きましたが、エクセルの最近のバージョン及び365ではスピル機能でできます。
任意のセルに「A2:A5」と範囲指定したLEN関数の数式(=LEN(A2:A5))を入れると、結果の予測値が隣接するセルに自動表示されます。オートフィルコピーする必要もありません。
範囲指定したLEN関数の数式を入れると、結果の予測値が隣接するセルに自動表示

LEN(A2:A5)」をSUM関数に入れ子すれば、複数範囲の文字数をカウントできます。
SUM関数にLEN関数を入れ子して複数のセルの文字数をカウント

数式は =SUM(LEN(A2:A5))

特定の文字の数をカウントするには?

特定の文字列を指定して、その数をカウントします。これはSUBSTITUTE関数の助けを借りて実装します。
以下のセル範囲にある「花」の数を数えてみましょう。
特定の文字列を指定して、その数をカウントするセル範囲

~Excel2016での指定

SUBSTITUTEは特定の文字を別の文字に置き換える関数です。
まず、範囲から「花」を検索して、空白に置き換える=「花」を削除する数式をSUBSTITUTE関数で組み立てます。
引数のダイアログでの指定はこうなります。
SUBSTITUTE関数で特定の文字列を空白に置き換える

「花」が除かれた文字列になりました。左の元の文字列から右の「花」を削除した文字列を引けば「花」の文字数が分かるというわけです。
元の文字数からSUBSTITUTEで特定の文字を削除した文字数を引く

元の文字数のカウントは前章で組み立てた=LEN(A2&A3&A4&A5)を流用します。
この式に対応させるため、SUBSTITUTEの数式の範囲をSUBSTITUTE(A2&A3&A4&A5,”花”,””)に修正、マイナス記号でつなげます。
SUBSTITUTEとLENの組み合わせで特定文字列のカウント

最終的な数式 =LEN(A2&A3&A4&A5)LEN(SUBSTITUTE(A2&A3&A4&A5,”花”,””))

Excel2019、2021、365での計算式

この場合もExcelの最近のバージョン及び365では、もう少しコンパクトな式にできます。
スピルで特定文字列をセルごとに計算した値が自動表示されるので、SUM関数に入れ子して合計させています。
Excel2019、2021、365で特定の文字列をカウントする計算式

=SUM(LEN(A2:A5)LEN(SUBSTITUTE(A2:A5,”花”,””)))

改行の数をカウントするには?

セル内で改行すると見えない改行文字が入り、LEN関数では改行文字も1文字とカウントされてしまいます。
文字数を正確にカウントするためには改行文字を排除する必要があります。

改行が入ったセル範囲の文字数をLEN関数でカウントしましたが、この数値には改行文字も含まれています。
改行が入ったセル範囲の文字数をLEN関数でカウント

ここからは前章の復習になりますが、SUBSTITUTE関数で改行文字を削除します。
文字コードから検索するCHAR関数で改行文字を取り出してSUBSTITUTEで空白に置き換え、LEN関数に入れ子してカウントします。

改行文字の文字コードは「10」です。CHAR(10)」をSUBSTITUTEの「検索文字列」に入力します。
CHAR(10)をSUBSTITUTEの「検索文字列」に入力

SUBSTITUTEの式をLEN関数に入れ子した数式は =LEN(SUBSTITUTE(A2&A3&A4&A5,CHAR(10),””)) となります。
この式で求められた結果が正確な文字数です。
改行文字を除いた正確な文字数

改行文字の数は元の文字数から正しい文字数を引けばカウントできます。
改行文字のカウント

まとめ
  • LEN関数は全角・半角にかかわらず、文字そのものをカウントする
  • LENB関数はバイト数でカウントするため、半角は「1」、全角は「2」と数える
  • 複数のセル範囲の入力値をカウントするにはセルを「&」でつないで指定する(Excel旧バージョン)
  • スピルが使えるExcel2021や365ではLEN関数に複数のセル範囲を「A2:A5」のように指定できる
  • 特定の文字をカウントするにはSUBSTITUTEとLENを組み合わせる
  • 改行をカウントするにはCHAR関数とSUBSTITUTE・LENを組み合わせる
タイトルとURLをコピーしました