Excel formula: Count specific characters in a cell

Generic formula

=LEN(a1)-LEN(SUBSTITUTE(a1,"a",""))

Explanation

To count how many times a specific character appears in a cell, you can use a formula based on the SUBSTITUTE and LEN functions. In the generic form of the formula above, A1 represents the cell address, and "a" represents the character you want to count.

In the example, the active cell contains this formula:

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

How this formula works

This formula works by using SUBSTITUTE to first remove all of the characters being counted in the source text. Then the length of the text (with the character removed) is subtracted from the length of the original text. The result is the number of characters that were removed with SUBSTITUTE, which is equal to the count of those characters.

Upper and lower case

SUBSTITUTE is a case sensitive function, so it will match case when running a substitution. If you need to count both upper and lower case occurrences of a specific character, use the UPPER function inside SUBSTITUTE to convert the text to uppercase before running the substitution. Then supply an uppercase character as the text that's being substituted.

=LEN(B3)-LEN(SUBSTITUTE(UPPER(B3),C3,""))

Author

Dave Bruns

Related formulas

Count specific words in a cell

If you need to count how many times a specific a word (or any substring) appears inside a cell, you can use a formula that uses SUBSTITUTE and LEN. In the generic form of the formula above, a1 represents a cell that contains text, and txt represents...

Count total characters in a cell

If you need to count the total characters in a cell, you can do so using the LEN function. In the generic form of the formula above, a1 represents a cell that contains text. In the example, the formula in the active cell is: = LEN ( B3 ) The LEN...

Count total characters in a range

If you want to count the total characters in a range of cells, you can do with a formula that uses LEN, along with the SUMPRODUCT function. In the generic form of the formula above, rng represents a range of cells that contain text. In the example,...

Count specific characters in a range

If you need to count specific characters in a range of cells, you can do so with a formula that uses LEN and SUBSTITUTE, along with the SUMPRODUCT function. In the generic form of the formula (above), rng represents a range of cells that contain...

Related functions

Excel LEN Function

The Excel LEN function returns the length of a given text string as the number of characters. LEN will also count characters in numbers, but number formatting is not included.

Excel SUBSTITUTE Function

The Excel SUBSTITUTE function replaces text in a given string by matching. For example =SUBSTITUTE("952-455-7865","-","") returns "9524557865"; the dash is stripped. SUBSTITUTE is case-sensitive and does not support wildcards.

Excel UPPER Function

The Excel UPPER function returns a upper-case version of a given text string. Numbers and punctuation are not affected.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.