Posted to microsoft.public.excel.misc
|
|
Counting Upper and Lower case chars in a Cell
Peo,
This works a treat - you have saved me many hours keeping two spreadsheets
in line
I thank you!
Regards
"Peo Sjoblom" wrote:
This will total A1:A6 for any letters
=SUM(INDEX(FREQUENCY(CODE(MID(A1:A6,COLUMN($1:$1), 1)&"^^"),{96,123}),2)*0.5,INDEX(FREQUENCY(CODE(MID (A1:A6,COLUMN($1:$1),1)&"^^"),{64,91}),2))
--
Regards,
Peo Sjoblom
"Peo Sjoblom" wrote in message
...
This would sum the total in A1:A6
=SUMPRODUCT((LEN(A1:A6)-LEN(SUBSTITUTE(A1:A6,"a","")))*0.5+LEN(A1:A6)-LEN(SUBSTITUTE(A1:A6,"A","")))
--
Regards,
Peo Sjoblom
"Peo Sjoblom" wrote in message
...
Assuming that's the case
=(LEN(A1)-LEN(SUBSTITUTE(A1,"a","")))*0.5+LEN(A1)-LEN(SUBSTITUTE(A1,"A",""))
--
Regards,
Peo Sjoblom
"Peo Sjoblom" wrote in message
...
Are you using the whole alphabet or just A and a?
--
Regards,
Peo Sjoblom
"Mad Axeman" <Mad wrote in message
...
Do you know of anyway of converting the following
a = 0.5
A = 1.0
Aa = 1.5
Aaa = 2.0
AA = 2.0
aAA
= 2.5
EG : If cell A1 contains a lower case letter - Cell D1 will show a
numeric
value of 0.5
If cell A2 contains an upper case letter - Cell D2 will show a numeric
value of 1
In effect an Upper case letter has a value of 1, a lower case = 0.5
Then, if a cell contains a mixture of both - the cell value is summed
together
EG Cell A3, 1 upper and 1 lower - Therefore D3 - 1.5
etc as in the above example
This has been melting my brain for a day or so now - so I need the help
of
an expert!
|