Count Specified Characters in a Cell

Have you ever wanted to know how many specified characters are in a cell? You can do this with a fairly simple formula.

Required Functions

  • LEN – LEN(str): Length of string
  • SUBSTITUTE(str, new_str, old_str) – Replaces all specified string with another specified string

Create a string within a cell.

To find out how many “A” are in the text, as in the example above, use the following formula:

=LEN(B4)-LEN(SUBSTITUTE(B4,"","A")

What is happening in the Formula

Len(B4) is counting the total number of characters in the string, for this instance it is 6.

Substitute(B4,””,”A”) is replacing the text “A” in cell B1 with an empty string, thus removing the text from the string. We are then running the length function on the substituted text “BBCCC” thus returning 5.

The formula subtracts the substituted length from the original length of the string, returning the difference which is 1.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s