How to Count How Many Times a Character Appears in a Cell in Excel

Count a specific character inside a cell with LEN and SUBSTITUTE — case-sensitive or not — and turn it into a defect rate.

You will learn to

• Count one character with LEN − LEN(SUBSTITUTE())
• Count regardless of case
• Turn the count into a percentage

How it works

Excel has no COUNTCHAR function, but there is a classic trick: remove the character with SUBSTITUTE and see how much shorter the text gets. =LEN(B2)-LEN(SUBSTITUTE(B2,"F","")) returns the number of F characters in B2. It works for any single character — commas in a list, slashes in a path, @ signs in a field.

SUBSTITUTE is case-sensitive, so "F" will not remove "f". To count both, convert the text to one case first: =LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),"F","")). Divide the count by LEN(B2) to get a share, such as the percentage of failed units in an inspection string.

=LEN(B2)-LEN(SUBSTITUTE(B2,"F",""))