Counting The Number Of Specific Characters Or Strings Of Characters In A Cell
The following formula will return the number of times that the text in B1 occurs in the text in A1. This is not case sensitive so, for example, ‘A’ is treated the same as ‘a’.
=IF(LEN(B1)=0,0,(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER(B1),”")))/LEN(B1))
If you want to use a case-sensitve match where for example ‘A’ is treated differently than ‘a’, use the following formula:
=IF(LEN(B1)=0,0,(LEN(A1)-LEN(SUBSTITUTE(A1,B1,”")))/LEN(B1))
Counting The Number Of Letters In A Cell
The following formula counts the number of letters (A to Z, in either upper or lower case) in cell A1.
=IF(LEN(A1)=0,0,SUM((CODE(UPPER(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)))>=CODE(“A”))*(CODE(UPPER(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)))<=CODE(“Z”))))
This formula is an array formulas so you must press CTRL SHIFT ENTER rather than just ENTERwhen you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula in the formula bar enclosed in curly braces { }.
CAclubindia
