Excel formula to count letters only from a given cell




=SUMPRODUCT((MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)>="A")*(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)<="Z"))

 For Example

If A1 = "Wor12d".~!@#$%^&*()_+{}|":<>?

then this formula would return the result as 4 since there are only four letters in the cell.

Comments

Post a Comment

Popular posts from this blog

Excel formula on finding the length of numbers, letters, small letters, capital letters and special characters

We know that excel can find length of cell using LEN () function. How about finding Length of letters/Numbers/special characters only? To find the length of capital letters and small letters try the below formula, where A1 is your data =SUMPRODUCT(--ISNUMBER(LOOKUP(CODE(MID(A1,ROW($1:$500),1)),{65,91,97,123},{1,"N",2,"N"}))) Length of Capital letters only =SUMPRODUCT(--ISNUMBER(LOOKUP(CODE(MID(A1,ROW($1:$500),1)),{65,91,97,123},{1,"N","N","N"}))) Length of small letters only =SUMPRODUCT(--ISNUMBER(LOOKUP(CODE(MID(A1,ROW($1:$500),1)),{65,91,97,123},{"N","N",2,"N"}))) Length of numbers used in cell =SUMPRODUCT(--ISNUMBER(LOOKUP(CODE(MID(A1,ROW($1:$500),1)),{48,58},{1,"N"}))) Length of cell apart from letters and numbers which is Special characters =LEN(A1)-SUMPRODUCT(--ISNUMBER(LOOKUP(CODE(MID(A1,ROW($1:$500),1)),{48,58,65,91,97,123},{1,"N",1,"N...
Read more