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.

 

[mikerickson]

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

The +0 was there to create an error if Mid returned a letter, but be a number if it returned a numeral (thanks to Excels automatic type conversions)

 

[pgc01]

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

Hi edvwvw

snd specified in post #1

 ... number of letters are always between zero and three ...

so just {1,2,3} is enough in this case.

 

[pgc01]

... and a simpler one:

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

 

[snd]

I couldn't delete the above question that's why I'm replying. After I searched, now I understand, the +0 is just like the double minus sign (to convert Ts and Fs into 1s and 0s). Thanks a lot guys.

 

[snd]

Thanks again Mike, got it.