Tips & Tricks 21 - Count only Alphabets in a String

By |Saturday, June 21st, 2014|Categories: Tips and Tricks|Tags: , , |0 Comments

Suppose you have a string "Ab?gh123def%h*" and you want to count only Aphabets.

Suppose your string is in A1, put following formula for this.

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW(INDIRECT("65:90"))),"")))

OR

=SUMPRODUCT(--(ABS(77.5-CODE(MID(UPPER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1)))<13))

OR

=SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=
{"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"}))