概要
Excelで「指定した文字が何個含まれているか」を数える方法としてよく使われるのが、=LEN(A1)-LEN(SUBSTITUTE(A1,"a","")) という式です。
これは「文字列全体の長さ」から「指定文字を削除した後の長さ」を引くことで、指定文字の出現回数を求める仕組みです。関数単体では“文字数を数える”機能はないため、LENとSUBSTITUTEを組み合わせて実現します。
基本の考え方
LEN関数
=LEN(文字列)
文字列の長さ（文字数）を返します。
SUBSTITUTE関数
=SUBSTITUTE(文字列, 置換前, 置換後)
文字列の中の指定文字を別の文字に置き換えます。ここで置換後を空文字 "" にすると削除になります。
組み合わせ
=LEN(A1)-LEN(SUBSTITUTE(A1,"a",""))
- LEN(A1)：元の文字列の長さ
- SUBSTITUTE(A1,”a”,””)：文字列から「a」をすべて削除
- LEN(…)：削除後の長さ
- 引き算：元の長さ − 削除後の長さ = 「a」の個数
具体例
「apple」に含まれる「a」の数
=LEN("apple")-LEN(SUBSTITUTE("apple","a",""))
結果は1。
「banana」に含まれる「a」の数
=LEN("banana")-LEN(SUBSTITUTE("banana","a",""))
結果は3。
セル参照で使う
=LEN(A2)-LEN(SUBSTITUTE(A2,"x",""))
A2の文字列に含まれる「x」の数を返します。
応用テンプレート
任意の文字を数える
=LEN(A1)-LEN(SUBSTITUTE(A1,"指定文字",""))
複数文字を数えたい場合
1文字ずつ別セルで数えるか、SUMで合計します。
=(LEN(A1)-LEN(SUBSTITUTE(A1,"a",""))) + (LEN(A1)-LEN(SUBSTITUTE(A1,"b","")))
大文字小文字を区別しない場合
UPPERやLOWERで統一してから数えます。
=LEN(UPPER(A1))-LEN(SUBSTITUTE(UPPER(A1),"A",""))
特定記号の出現回数を数える
=LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))
ハイフンの数を数える。
よくあるつまずきと対策
全角と半角の違い
「a」と「ａ」は別文字です。全角半角を統一してから数えましょう。
大文字小文字の違い
「a」と「A」は別文字です。区別したくない場合はLOWERやUPPERで揃えます。
数字や記号も同じ方法で数えられる
「1」「-」「@」なども同じ式で数えられます。
例題
問題1: A2セルの文字列に含まれる「a」の数をB2に表示してください。
解答例:
=LEN(A2)-LEN(SUBSTITUTE(A2,"a",""))
問題2: C2セルの文字列に含まれる「x」の数をD2に表示してください。
解答例:
=LEN(C2)-LEN(SUBSTITUTE(C2,"x",""))
問題3: E2セルの文字列に含まれる「A」と「a」を区別せずに数え、F2に表示してください。
解答例:
=LEN(UPPER(E2))-LEN(SUBSTITUTE(UPPER(E2),"A",""))
問題4: G2セルの文字列に含まれるハイフン「-」の数をH2に表示してください。
解答例:
=LEN(G2)-LEN(SUBSTITUTE(G2,"-",""))
問題5: I2セルの文字列に含まれる「b」と「c」の合計数をJ2に表示してください。
解答例:
=(LEN(I2)-LEN(SUBSTITUTE(I2,"b",""))) + (LEN(I2)-LEN(SUBSTITUTE(I2,"c","")))
まとめ
LENとSUBSTITUTEを組み合わせることで「指定文字の出現回数」を簡単に数えられます。
- LENで文字数を取得
- SUBSTITUTEで指定文字を削除
- 差分が出現回数
大文字小文字や全角半角の違いに注意し、必要に応じてUPPERやLOWER、SUBSTITUTEを組み合わせると実務でも安定して使えます。講師として強調したいのは「Excelには直接“文字数を数える関数”はないが、工夫次第で実現できる」という点です。これを覚えれば文字列処理の幅がぐっと広がります。