How To Count A Specific Character In A Cell (Case Sensitive)

2017-08-16

Example

=LEN(B3)-LEN(SUBSTITUTE(B3,C3,””))

Generic Formula

=LEN(Text)-LEN(SUBSTITUTE(Text,Character,””))
  • Text – This is the text string in which you want to count the occurrences of a given single character.
  • Character – This is the character which you want to count.

What It Does

This formula will count the number of occurrences of a given single character within a text string in a single cell. This formula is case sensitive and will consider upper and lower case letters to be different characters.

How It Works

The formula takes the difference between these two items:

  • Character count of the full text string. This is given by the LEN(Text) part of the formula.
  • Character count of the text string with all the characters we’re trying to count removed. This is given by the LEN(SUBSTITUTE(Text,Character,””)) part of the formula.

In our example LEN(“Apples and bananas”) results in 18 since there are 18 characters in the string.

In our example SUBSTITUTE(“Apples and bananas”,”a”,””) results in the text string “Apples nd bnns”, where each occurrence of “a” has been removed. The character count of this new text string is then found by LEN(“Apples nd bnns”) and this results in 14 since there are 14 characters in this string.

The difference of LEN(Text)-LEN(SUBSTITUTE(Text,Character,””)) is just the count of all our removed characters from the text string. In our example this is 18-14, so there are 4 “a” characters in the text string!

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Subscribe

Advertisement

Related Articles

Comments

0 Comments

Get The Latest News

Follow Us

Follow us on social media to stay up to date with the latest tips in Excel!

Pin It on Pinterest

Share This