Excel関数で１セル内にある特定の文字列の個数を調べる方法

この記事では、ひとつのセルの中に特定の文字列がいくつあるか、関数のみを使ってカウントする方法を解説します。

イメージしやすいように、以下のようなワークシートを例にして解説します。

• セルA1に「あああいいうええおおお」と入力されている。
• セルA2に検索したい文字列を入力する。

解説する方法を使うことで、セルA1に入力された「あああいいうええおおお」の中に、検索したい文字列がいくつあるかをカウントすることができます。

たとえば、

• セルA2に「い」と入力　→　「2」が返る。
• セルA2に「うえ」と入力　→　「1」が返る。
• セルA2に「あいう」と入力　→　「0」が返る

といったような結果になります。

では、具体的な方法を見ていきましょう。

セル内の特定の文字数をカウントする関数

結論としては、以下の関数を使用します。

具体的な仕組みは後述しますが、とりあえず関数を使いたいという方は、上記をコピペしていただければと思います。

「A1」「A2」の部分（それぞれ2箇所ずつ）をご自身のワークシートに合わせて変更すれば、そのまま使用することができます。

解説｜なぜこの関数で指定した文字列を数えられるのか

ここから、具体的な仕組みの解説をしたいと思います。

大まかにいうと、上記の関数では以下のようなことをしています。

1. 探したい文字列を空白に置き換える
2. 空白に置き換える前から何文字減ったかを数える
3. 減った文字数を、探したい文字列の長さで割る
   

ひとつひとつ見ていきましょう。

1. 探したい文字列を空白に置き換える

まず、

のうち、

の部分から見ていきましょう。

SUBSTITUTE関数とは？

SUBSTITUTE関数は、文字列置換をするための関数です。

３つの引数を渡しますが、それぞれ

• 第一引数：検索対象文字列
• 第二引数：変換前の文字列
• 第三引数：変換後の文字列

となります。

これだと少しわかりづらいので具体例をあげます。

例）
SUBSTITUTE(“東京・埼玉・神奈川”,”埼玉”,”千葉”)　→　東京・千葉・神奈川

第一引数の「東京・埼玉・神奈川」という文字列の中から、「埼玉」の文字列を探し、それを「千葉」に置き換えています。

このように、SUBSTITUTE関数を使用すると、文字列の一部を別の文字列に置き換えることができるのです。

SUBSTITUTE関数を使って何をしている？

では、SUBSTITUTE(A1,A2,””)に戻りましょう。

A1、A2はそれぞれ、以下の通りでした。

• セルA1に「あああいいうええおおお」と入力されている。
• セルA2に検索したい文字列を入力する。

SUBSTITUTE(A1,A2,””)では、「あああいいうええおおお」の中からA2セルに入力された値を探し出し、それを空白に置き換えています。

なので、

• A2に「い」と入力　→　「あああうええおおお」
• A2に「うえ」と入力　→　「あああいいえおおお」

となります。

関数の動きとしてはシンプルですが、なぜこのようなことをしているのでしょうか？

それは、この変換によって何文字減ったのかを数えるためです。

2. 空白に置き換える前から何文字減ったかを数える

元の関数に戻りましょう。

「SUBSTITUTE(A1,A2,””)」の部分はすでに解説しました。
見やすくするために、この部分を「〜」に置き換えると、元の関数は以下の通りとなります。

次は、前半の「(LEN(A1)-LEN(〜))」の部分を見ていきましょう。

LEN関数とは？

まず、使用されているLEN関数について説明します。

LEN関数は引数をひとつだけ受け取り、その文字列の長さを数える関数です。

例を挙げると、

• LEN(“あ”)　→　1
• LEN(“あいうえお”)　→　5

といった感じになります。

LEN関数を使って何をしている？

LEN(A1)-LEN(〜)では、元の文字列であるA1の長さから、探している文字列を空白に置き換えた文字列の長さを引いています。つまり、探している文字列を空白に置き換えたことによって何文字減ったのかを数えているのです。

なので、

• A2に「い」と入力　→　2（「いい」の2文字減った）
• A2に「うえ」と入力　→　2（「うえ」の2文字減った）

という結果が返ります。

これによって、探している文字列を空白に置き換えると、何文字減ったのかがわかります。

3. 減った文字数を、探したい文字列の長さで割る

「(LEN(A1)-LEN(SUBSTITUTE(A1,A2,””)))」までで、探したい文字列を空白に置き換えると何文字減るのかがわかりました。

あとは、減った文字数が、探したい文字列何個分に相当するのかがわかればOKです。

これは、減った文字数を、探したい文字列の文字数で割れば求められます。

ですので、(LEN(A1)-LEN(SUBSTITUTE(A1,A2,””)))で求めた文字数を、探したい文字列の文字数LEN(A2)で割ることで、探したい文字列の個数を求めることができます。

まとめ

ひとつのセル内にある特定の文字列の個数を数える関数について解説してみました。

Excelの関数は、複数組み合わせることで、いろいろなことができるようになります。

やりたいことを直接実現する関数がなかったとしても、複数の関数を組み合わせることで実現できることもあります。

ぜひ、活用してみてください。