Convert Letters to Numbers?

  • Thread starter Thread starter DS
  • Start date Start date
D

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
 
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
 
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)
 
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
 
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
 
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)
 
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
 
Actually, this is probably the better way to write my function...

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