Convert Letters to Numbers?

[DS]

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

 

[Gary Keramidas]

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

 

[Chip Pearson]

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)

 

[Gary Keramidas]

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

 

[Bernard Liengme]

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

 

[DS]

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]

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)

 

[Rick Rothstein]

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 Rothstein]

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

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