Tips & Tricks 117 – VBA – How to Count a particular character in a String

When we use formulas inside Excel, we have following stock formula to count the number of times a character appears in a string –

=LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"a",""))

In this, we are trying to count the occurrence of character "a" in A1. Let's assume that A1 = "Abraham Arthurway". Hence, the answer which we would get will be 5.

To count the same in a range of cells –

=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(LOWER(A1:A10),"a","")))

Now, how to do the same thing in VBA. In VBA, you can use following expression to count this –

UBound(Split(LCase(Range("A1")), "a"))

To count the same in a range of cells –

UBound(Split(LCase(Join(WorksheetFunction.Transpose(Range("A1:A10")))), "a"))