How to Count Specific Characters in a Column in Excel – 4 Methods

Formula Breakdown

SUBSTITUTE(C5:C9, “v”,””) —> clears v in C5:C9.
Output: {“Alice in Wonderland”;”Adentures of Tom Sawyer”;”Pride and Prejudice”;”Gullier’s Traels”;”Adentures of Sherlock Holmes”}

LEN(C5:C9)-LEN(SUBSTITUTE(C5:C9, “v”,””))
{19;24;19;18;29}-{19;23;19;16;28} —> subtracts the length of C5:C9 from the updated length after replacing v.
Output: {0;1;0;2;1}

SUMPRODUCT(LEN(C5:C9)-LEN(SUBSTITUTE(C5:C9, “v”,””)))
SUMPRODUCT({0;1;0;2;1}) —> sums the values and returns the result.
Output: 4

Formula Breakdown

–EXACT(“Pride and Prejudice”,C5:C9) —> finds the exact match in C5:C9.
Output: {0;0;1;0;0}

SUMPRODUCT(–EXACT(“Pride and Prejudice”,C5:C9))
SUMPRODUCT({0;0;1;0;0}) —> sums the values and returns the result.
Output: 1

Formula Breakdown

FIND(“T”,C5:C9) —> finds T in C5:C9.
Output: {#VALUE!;15;#VALUE!;12;#VALUE!}

–ISNUMBER(FIND(“T”,C5:C9))
–ISNUMBER({#VALUE!;15;#VALUE!;12;#VALUE!}) —> returns the number of matched values.
Output: {0;1;0;1;0}

SUMPRODUCT(–ISNUMBER(FIND(“T”,C5:C9)))
SUMPRODUCT({0;1;0;1;0}) —> sums the values and returns the result.
Output: 2

Formula Breakdown

SUBSTITUTE(C5:C9,”i”,””) —> clears i in C5:C9.
Output: {“Alce n Wonderland”;”Adventures of Tom Sawyer”;”Prde and Prejudce”;”Gullver’s Travels”;”Adventures of Sherlock Holmes”}

LEN(C5:C9)-LEN(SUBSTITUTE(C5:C9, “v”,””))
{19;24;19;18;29}-{17;24;17;17;29} —> subtracts the length of C5:C9 with the updated length after replacing i.
Output: {2;0;2;1;0}

Formula Breakdown

(LEFT(C5:C9,1)=”A”)*1 —> returns books starting with A in C5:C9.
Output: {1;1;0;0;1}

SUM((LEFT(C5:C9,1)=”A”)*1)
SUM({1;1;0;0;1})—> sums the values and returns the result.
Output: 3