Counting Upper and Lower case chars in a Cell
 

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!

Counting Upper and Lower case chars in a Cell
 

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!

Counting Upper and Lower case chars in a Cell
 

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!

Counting Upper and Lower case chars in a Cell
 

One way using a UDF (see

http://www.mvps.org/dmcritchie/excel/getstarted.htm

if you're not familiar with macros/UDFs):

You also don't say what should happen with non-alphabetic characters -
I'll assume the function should return a #VALUE! error:

Public Function UpperLower(sText As String) As Variant
Dim vResult As Variant
Dim i As Long
Dim sTest As String
If sText Like "*[a-z,A-Z]*" Then 'contains text
For i = 1 To Len(sText)
sTest = Mid(sText, i, 1)
If sTest Like "[a-z,A-Z]" Then
vResult = vResult + 0.5 * (1 - (Asc(sTest) < 97))
Else
vResult = CVErr(xlErrValue)
Exit For
End If
Next i
Else
vResult = CVErr(xlErrValue)
End If
UpperLower = vResult
End Function

Call as

D1: =UpperLower(A1)


In article ,
Mad Axeman <Mad wrote:

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!

Counting Upper and Lower case chars in a Cell
 

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!

Counting Upper and Lower case chars in a Cell
 

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!

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!

Counting Upper and Lower case chars in a Cell
 

Another solution, works for all lowercase and uppercase letter.
Replace A6 with the address of whichever cell has the string in
question, and enter as array formula:

=SUM(N(CODE(MID(A6,ROW(INDIRECT("A1:A"&LEN(A6))),
1))=CODE("A"))*N(CODE(MID(A6,ROW(INDIRECT("A1:A"& LEN(A6))),
1))<=CODE("Z"))*1,N(CODE(MID(A6,ROW(INDIRECT("A1:A "&LEN(A6))),
1))=CODE("a"))*N(CODE(MID(A6,ROW(INDIRECT("A1:A"& LEN(A6))),
1))<=CODE("z"))*0.5)

I'm doing this in Excel 2007, but I think that there's no more than 7
levels of function nesting in there anywhere.

On Jul 30, 8:10 am, Mad Axeman
wrote:
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(MI D(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(S*UBSTITUTE(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!- Hide quoted text -

- Show quoted text -