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!

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!

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!

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!

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!

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!

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!

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 -