Convert Letters to Numbers?
 

I'm hoping someone might be able to suggest an easy way for the following.

I receive a variable as a letter - e.g. A, B, C etc. I need to convert this
to a number, so A = 1, B = 2 etc. Currently I use a Select Case, but
obviously that can get quite long (the letter can be anything from A-O, so
there are 15 separate cases to state).

Anyone got any ideas?

TiA,
DS

Convert Letters to Numbers?
 

not sure what you want or where the letters are, but if A or a was in A1:

range("b1").value = Hex(Asc(UCase(Range("A1").Value))) - 40
--


Gary

"DS" wrote in message
...
I'm hoping someone might be able to suggest an easy way for the following.

I receive a variable as a letter - e.g. A, B, C etc. I need to convert this
to a number, so A = 1, B = 2 etc. Currently I use a Select Case, but
obviously that can get quite long (the letter can be anything from A-O, so
there are 15 separate cases to state).

Anyone got any ideas?

TiA,
DS

Convert Letters to Numbers?
 

Try a function like the following:

Function Test(Letter As String) As Long
Test = Asc(UCase(Letter)) - Asc("A") + 1
End Function

This assumes that Letter is a single character between A and Z. You
might want to put some validation logic in the code.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Tue, 21 Oct 2008 08:26:01 -0700, DS
wrote:

I'm hoping someone might be able to suggest an easy way for the following.

I receive a variable as a letter - e.g. A, B, C etc. I need to convert this
to a number, so A = 1, B = 2 etc. Currently I use a Select Case, but
obviously that can get quite long (the letter can be anything from A-O, so
there are 15 separate cases to state).

Anyone got any ideas?

TiA,
DS

Convert Letters to Numbers?
 

i tested mine and it doesn't work for all of your cases. sorry.

--


Gary

"DS" wrote in message
...
I'm hoping someone might be able to suggest an easy way for the following.

I receive a variable as a letter - e.g. A, B, C etc. I need to convert this
to a number, so A = 1, B = 2 etc. Currently I use a Select Case, but
obviously that can get quite long (the letter can be anything from A-O, so
there are 15 separate cases to state).

Anyone got any ideas?

TiA,
DS

Convert Letters to Numbers?
 

If A1 have the letter, and B1 has the formula =TRYTHIS(A1), then B1 will
have the value you want

Hers is the UDF code

Function trythis(myletter)
trythis = Asc(myletter) - 64
End Function

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"DS" wrote in message
...
I'm hoping someone might be able to suggest an easy way for the following.

I receive a variable as a letter - e.g. A, B, C etc. I need to convert
this
to a number, so A = 1, B = 2 etc. Currently I use a Select Case, but
obviously that can get quite long (the letter can be anything from A-O, so
there are 15 separate cases to state).

Anyone got any ideas?

TiA,
DS

Convert Letters to Numbers?
 

That's perfect Chip, thanks for such a quick response.

I only need A-O as things stand, so it works like a charm.

Many Thanks,
DS

"Chip Pearson" wrote:

Try a function like the following:

Function Test(Letter As String) As Long
Test = Asc(UCase(Letter)) - Asc("A") + 1
End Function

This assumes that Letter is a single character between A and Z. You
might want to put some validation logic in the code.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Tue, 21 Oct 2008 08:26:01 -0700, DS
wrote:

I'm hoping someone might be able to suggest an easy way for the following.

I receive a variable as a letter - e.g. A, B, C etc. I need to convert this
to a number, so A = 1, B = 2 etc. Currently I use a Select Case, but
obviously that can get quite long (the letter can be anything from A-O, so
there are 15 separate cases to state).

Anyone got any ideas?

TiA,
DS

Convert Letters to Numbers?
 

If a formula is needed (rather than code callable in VBA) just use

=CODE(A1)-64

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Tue, 21 Oct 2008 12:40:44 -0300, "Bernard Liengme"
wrote:

If A1 have the letter, and B1 has the formula =TRYTHIS(A1), then B1 will
have the value you want

Hers is the UDF code

Function trythis(myletter)
trythis = Asc(myletter) - 64
End Function

best wishes

Convert Letters to Numbers?
 

Some may find this alternative function of some interest (it is also case
insensitive)...

Function LetterToNumber(Letter As String) As Long
LetterToNumber = Cells(1, Letter).Column
End Function

--
Rick (MVP - Excel)


"Chip Pearson" wrote in message
...
Try a function like the following:

Function Test(Letter As String) As Long
Test = Asc(UCase(Letter)) - Asc("A") + 1
End Function

This assumes that Letter is a single character between A and Z. You
might want to put some validation logic in the code.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Tue, 21 Oct 2008 08:26:01 -0700, DS
wrote:

I'm hoping someone might be able to suggest an easy way for the following.

I receive a variable as a letter - e.g. A, B, C etc. I need to convert
this
to a number, so A = 1, B = 2 etc. Currently I use a Select Case, but
obviously that can get quite long (the letter can be anything from A-O, so
there are 15 separate cases to state).

Anyone got any ideas?

TiA,
DS

Convert Letters to Numbers?
 

Actually, this is probably the better way to write my function...

Function LetterToNumber(Letter As String) As Long
LetterToNumber = Columns(Letter).Column
End Function

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Some may find this alternative function of some interest (it is also case
insensitive)...

Function LetterToNumber(Letter As String) As Long
LetterToNumber = Cells(1, Letter).Column
End Function

--
Rick (MVP - Excel)


"Chip Pearson" wrote in message
...
Try a function like the following:

Function Test(Letter As String) As Long
Test = Asc(UCase(Letter)) - Asc("A") + 1
End Function

This assumes that Letter is a single character between A and Z. You
might want to put some validation logic in the code.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Tue, 21 Oct 2008 08:26:01 -0700, DS
wrote:

I'm hoping someone might be able to suggest an easy way for the
following.

I receive a variable as a letter - e.g. A, B, C etc. I need to convert
this
to a number, so A = 1, B = 2 etc. Currently I use a Select Case, but
obviously that can get quite long (the letter can be anything from A-O,
so
there are 15 separate cases to state).

Anyone got any ideas?

TiA,
DS