Don't get me wrong. The space should be counted as a "character"...but
based on the initial question, they wanted to know how many charaters in a
cell are "Letters". Since a space is not a letter, and the formula is not
written to omit or substitute spaces, then the result will be incorrect if
referenced cell contains a space or spaces.




"Harlan Grove" wrote in message
oups.com...
PCLIVE wrote...
This will not answer your question, but it is the LEN command that counts
the number of characters. Unfortunately, numbers are considered to be
characters, and therefore the extra SUBSTITUTE command is necessary. I'm
not quite sure about the rest of this formula, but I did find one flaw.
If
your entry has a space in it, it will count that as a charater.
...

Why shouldn't spaces be counted as characters?

"Jordan" wrote in message
...
For example JXR1234 = 3

=SUMPRODUCT(LEN(SUBSTITUTE(A22,{0;1;2;3;4;5;6;7 ;8;9},"")))-9*LEN(A22)
...

The array SUBSTITUTE call returns the lengths of A22 with each of the
decimal numerals removed separately. Subtract each of these results
from LEN(A22) and the result will be an array of the number of times
each decimal numeral appears in the string. Sum that array, and the
result is the total number of decimal numerals in the string. That
would be

=SUMPRODUCT(LEN(A22)-LEN(SUBSTITUTE(A22,{0;1;2;3;4;5;6;7;8;9},"")))

Then subtract this from LEN(A22) to give the number of other characters
in A22, so

=LEN(A22)-SUMPRODUCT(LEN(A22)-LEN(SUBSTITUTE(A22,{0;1;2;3;4;5;6;7;8;9},"")))

But addition is associative, so this could be rearranged as

=LEN(A22)-10*LEN(A22)
-(SUMPRODUCT(-LEN(SUBSTITUTE(A22,{0;1;2;3;4;5;6;7;8;9},"")))

and this reduces to

=SUMPRODUCT(LEN(SUBSTITUTE(A22,{0;1;2;3;4;5;6;7;8; 9},"")))-9*LEN(A22)

If you didn't want to count spaces, just change this to

=SUMPRODUCT(LEN(SUBSTITUTE(A22,{0;1;2;3;4;5;6;7;8; 9;"
"},"")))-10*LEN(A22)

note that the constant term in the last expression (9 or 10) is one
less than the number of entries in the array constant. You could adapt
this to put all characters to exclude into a string.

=SUMPRODUCT(LEN(SUBSTITUTE(A22,MID("0123456789 ",
ROW(INDIRECT("1:"&LEN("0123456789 "))),1),"")))
-(LEN("0123456789 ")-1)*LEN(A22)