Counting numbers & characters in a cell

[jas27]

Does anyone know how to devise an Excel formula that will count number
and uppercase/lowercase characters in a text string in a cell. I nee
to create a formula that will evaluate a textstring in a cell an
perform a count. The number of characters/numbers and their positio
in the cell will vary. For example:


Cell A1 Jk784mn
Cell A2 9888TjJ7rh


Formula to count the numbers in cell. (A1 Result = 3, A2 Result = 5)
Formula to count the uppercase characters in cell. (A1 Result = 1, A
Result = 2)
Formula to count the lowercase characters in cell. (A1 Result = 3, A
Result = 3)

Any help would be greatly appreciated

 

[Ryan Poth]

jas27,

All these should be array entered (ctrl-shift-enter)

For numerals, use:
=SUM((CODE(MID($A1&REPT("
",10-LEN($A1)),{1,2,3,4,5,6,7,8,9,10},1))>=48)*(CODE(MID($A1&REPT("
",10-LEN($A1)),{1,2,3,4,5,6,7,8,9,10},1))<=57))

For upper case characters, use:
=SUM((CODE(MID($A1&REPT("
",10-LEN($A1)),{1,2,3,4,5,6,7,8,9,10},1))>=64)*(CODE(MID($A1&REPT("
",10-LEN($A1)),{1,2,3,4,5,6,7,8,9,10},1))<=91))

For lower case characters, use:
=SUM((CODE(MID($A1&REPT("
",10-LEN($A1)),{1,2,3,4,5,6,7,8,9,10},1))>=96)*(CODE(MID($A1&REPT("
",10-LEN($A1)),{1,2,3,4,5,6,7,8,9,10},1))<=123))

For simplicity, I have made the assumption that the maximum length of any
string is 10 characters. If your strings are longer, increase the "REPT("
",10..." portion of the formulas to the maximum length and add more digits to
the "{1,2,3,..." portion of the formulas.

HTH,
Ryan

 

[Biff]

Hi!

These may be better.

All are array formulas and must be entered with the key combo of
CTRL,SHIFT,ENTER:

For numbers:

=SUM(LEN(C$3)-LEN(SUBSTITUTE(C$3,CHAR(ROW(INDIRECT("48:57"))),"")))

For uppercase letters:

=SUM(LEN(C$3)-LEN(SUBSTITUTE(C$3,CHAR(ROW(INDIRECT("65:90"))),"")))

For lowercase letters:

=SUM(LEN(C$3)-LEN(SUBSTITUTE(C$3,CHAR(ROW(INDIRECT("97:122"))),"")))

Biff

 

[Ryan Poth]

Biff,

Those are indeed better. I knew there must have been a more dynamic way of
doing it, I just couldn't think of it.

Also, my formula have incorrect ansi codes for upper/lower case character
ranges, which I see you have also corrected.

Thanks,
Ryan