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

[snd]

Excel 2007

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	Original Data​	New Data​	Digits​	Letters​
2​	XYz123456	XYZ123456	6​	3​
3​	aB1234567	AB1234567	7​	2​
4​	123456780​	123456780​	9​	0​
5​
6​
7​
8​	Notes:
9​	- Letters + Digits = 9 characters, always.
10​	- Number of digits are always between 6 and 9 (inclusive).
11​	(so, number of letters are always between zero and three).
12​	- Letters come before numbers.
13​
14​	1- I need to change the small letters to capital.
15​	2- I need to know how many numbers, and how many letters (if any) in each cell.
16​
17​	Notes:
18​	Please, worksheet functions only.

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

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