How many digits and letters a cell has; change small letters to capital.

[snd]

Excel 2007
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH]_Row\^Col[/TH]
[TH]

A​

[/TH]
[TH]

B​

[/TH]
[TH]

C​

[/TH]
[TH]

D​

[/TH]
[TH]

E​

[/TH]
[TH]

F​

[/TH]
[TH]

G​

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

1​

[/TD]
[TD]

Original Data​

[/TD]
[TD]

New Data​

[/TD]
[TD]

Digits​

[/TD]
[TD]

Letters​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

2​

[/TD]
[TD]XYz123456[/TD]
[TD]XYZ123456[/TD]
[TD]

6​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

3​

[/TD]
[TD]aB1234567[/TD]
[TD]AB1234567[/TD]
[TD]

7​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

4​

[/TD]
[TD]

123456780​

[/TD]
[TD]

123456780​

[/TD]
[TD]

9​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

8​

[/TD]
[TD]Notes:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

9​

[/TD]
[TD]- Letters + Digits = 9 characters, always.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

10​

[/TD]
[TD]- Number of digits are always between 6 and 9 (inclusive).[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

11​

[/TD]
[TD] (so, number of letters are always between zero and three).[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

12​

[/TD]
[TD]- Letters come before numbers.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

13​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

14​

[/TD]
[TD]1- I need to change the small letters to capital.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

15​

[/TD]
[TD]2- I need to know how many numbers, and how many letters (if any) in each cell.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

16​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

17​

[/TD]
[TD]Notes:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

18​

[/TD]
[TD]Please, worksheet functions only.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]

Thank you guys for helping me out.

 

[nonexistence]

Numbers

=LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""))

Letters

=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""))

Uppercase

=UPPER(A2)

 

[snd]

Numbers

=LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""))

Thank you very much nonexistence for your help.

 

[István Hirsch]

Give this a try:

Excel Workbook
	A	B	C	D
1	XYz123456	XYZ123456	6	3
2	aB1234567	AB1234567	7	2
3	123456780	123456780	9	0
Sheet

 

[mikerickson]

Number of non-numeric characters in A1 =SUMPRODUCT(--ISERROR(0+("0"&MID(A1,ROW(1:255),1))))

Number of numeric digits in A1 =SUMPRODUCT(--ISNUMBER(0+(MID(A1,ROW(1:255),1))))

 

[snd]

Dear Istvan, amazing one! Thank you very much.

 

[pgc01]

Hi

Anoter option, in D1:

=SUM(1-ISNUMBER(-MID(A1,{1,2,3},1)))

 

[snd]

Thank you Mike very much. If I may ask another one: Where can I find more info about "Sumproduct". (didn't understand the zero before the MID Function).

 

[snd]

Thank you PGC. Amazing one, really.

 

[edvwvw]

Hi Ppgc01

I am always interested in your formulae so looked more closely at your suggestion for D1 - surely to capture all of the potential "numbers" it should have been:

=SUM(1-ISNUMBER(-MID(A1,{1,2,3,4,5,6,7,8,9},1)))

edvwvw