Understanding a formula

[Guest]

Can anyone tell me why this formula works. It was given to me as a solution
and it works I just dont understand why.

I was trying to count how many charaters in a cell are letters.

For example JXR1234 = 3

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

I understand how sumproduct works, just not in this instance.

Thanks for any help you can send my way.

Confused in Phoenix.

 

[PCLIVE]

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.

I'm sure someone else will probably post a better explanation as well as a
corrected formula.

HTH,
Paul

 

[Bob Umlas]

If you select the SUBSTITUTE(...) part of the formula and press F9, you'll
see:
{"JXR1234";"JXR234";"JXR134";"JXR124";"JXR123";"JXR1234";"JXR1234";"JXR1234"
;"JXR1234";"JXR1234"}
Note the 2nd item is missing a 1, the 3rd item is missing a 2, 4th is
missing a 3 and 5th is missing a 4. The rest all all there.
now the LEN part returns
{7;6;6;6;6;7;7;7;7;7}
the SUMPRODUCT now returns 66 (add 'em up)
and from this, 9*LEN(A22) or 9*7 or 63 is subtracted. So you have 66-63, or
3, the number of letters.
If you change the text from JXR1234 to something else, like J1234567 & do
the above steps it'll get clearer still.

Nice formula, by the way!

Bob Umlas
Excel MVP

 

[Harlan Grove]

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?
....

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)

 

[PCLIVE]

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.

 

[Guest]

Thanks for all the help. I understand the sumproduct now and how the
substitute is working. Very Cool.

I still dont get why - 9 and * the length works.

Still confused in Phoenix. Thanks for all the help, I really appreciate it.

 

[Harlan Grove]

PCLIVE wrote...

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.

....

Fair point. To count only letters, use

=26*LEN(A22)-SUMPRODUCT(LEN(SUBSTITUTE(UPPER(A22),
MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",ROW(INDIRECT("1:26")),1),"")))