@ > Home > Contents > Count letters in a cell Excel 97+

Task: 

Count valid letters (or characters) in one cell.

Step-by-step solution: (see the final formula as the result)

=SUMPRODUCT(--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>96))

count a to z and any other western European letters above. But also all non-letters like {, } and so on. Slowly we proceed:

=SUMPRODUCT(
--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>96),
--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<123))

uses an interval. We now count a to z, but not A to Z. Let us use LOWER() for this:

=SUMPRODUCT(
--(CODE(LOWER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))>96),
--(CODE(LOWER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))<123))

Now we have a to z and A to Z. But the interval makes the formula unnecessarily long. Any way for a short cut? See this:

---+----+----+----+----+-- before
  100  105  110  115  120

--|----|----|----|----|--# after
 -10   -5   0    +5  +10
We locate the interval symmetrically around zero shifting by -109. "ABS()<13" allows us to include 25 integer values without a second condition. The 26th value (expressed by the # here) we get additionally by doing the following: we shift half a position more, -109.5:

=SUMPRODUCT(
--(ABS(CODE(LOWER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))-109.5)<13))

We changed nothing at our countable letters (a to z and A to Z), but we succeeded in a general question: Normalization of an interval, making formulas nearly half as long!

SUMPRODUCT() here adds only one vector. But it can do an array as well. We quickly benefit from this by defining further intervals of letters in the standard latin and latin-1 character sets:

=SUMPRODUCT(
--(ABS(CODE(LOWER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))-{109.5\154\156\158\223\235\251.5})<{13\1\1\1\1\12\4}))

Now we got all 8 bit letters. And it is easy to return to the formula before in case that only the first 7 bit of the set is allowed.

Lucky we are, we use all 8 function-nesting levels Excel allows us before xl2007, and no more!