# Quickest way to return a letter based on a number

I want to produce a function or sub to return a letter instead of a number. This will be used for exporting data into excel

eg 1=A, 2=B,.........27=AA,......to.......256=IV

At the moment I'm using a Select Case an only going from 1=A to 26=Z

Any Idea?

Thanks
LVL 2
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi NevHolland,

Use Asc(UCase(strCharacter)) - 64

Where strCharacter holds your character. Ascii value for A is 65 so subtracting 64 gives you 1 and so on. UCase ensures that you get the correct value regardless of the case of the letter.

Tim Cottee MCSD, MCDBA, CPIM
Brainbench MVP for Visual Basic
http://www.brainbench.com
0
Author Commented:
I'll start there what would happen when I get to Column 27 or in Excel AA?
0
Ahh didn't read the whole question, hold on a moment:

Function GetNumberFromString(ByVal NumberString As String)
Dim intNumber As Integer
If Len(NumberString) > 1 Then
'Get the value for the first letter
intNumber = (Asc(Left(UCase(NumberString),1)) - 64)  * 26 'Multiply by 26 to get the correct value
End If
intNumber = intNumber + Asc(Right(UCase(NumberString),1)) - 64 'Getting right hand char is fine even if a single char string
GetNumberFromString = intNumber
End Function

Just call the function:

Msgbox GetNumberFromString("AA") for example

0
Author Commented:
We have the reverse of what I'm looking for...

I have a number and I need to turn it to a Letter

In an Excel Sheet you have Columns A to IV. I have numbers 1 to 256 where A=1 B=2 AA=27 IV=256

I need to plug in a number between 1 and 256 and return a value between A and IV

Thanks for the pointers, any other ideas?
0
Commented:
wooo you can do it in 2 lines of code:

MyChars = IIf(Len(MyChars) = 1, "@" & MyChars, MyChars)
MyVal = 26 * (Asc(Left(MyChars, 1)) - 64) + (Asc(Right(MyChars, 1)) - 64)
'MsgBox MyVal

Haydn.
0
Commented:
Where MyChars is the variable holding the string A, AA, IV etc

Haydn.
0
Author Commented:
HaydnH,

Am I right in saying that the your code will return a Value it being 1 or greater than one?

If so then I'm trying to perform the reverse. I already have the number I need to turn that into a Letter.

eg
Input = 1
Output = A

Input = 27
Output = AA

thanks Nev
0
Function GetStringFromNumber(ByVal Number As Integer) As String
Dim strNumber As String
If Number > 26 Then
'Get the value for the first letter
If Number Mod 26 = 0 Then
Number = Number - 26
End If
strNumber = Chr((Int(Number / 26)) + 64)
Number = ((Number - 1) Mod 26) + 1
End If
strNumber = strNumber & Chr(Number + 64)
GetStringFromNumber = strNumber
End Function

Should do it though perhaps not the most efficient way.
0
Commented:
The reverse is easier, just 1 line of code:

MyChars = Chr(64 + (MyVal \ 26)) & Chr(64 + (MyVal Mod 26))

Where MyVal is the value (1-256) and MyChars is the returned character value.

Regards,

Haydn.
0
Commented:
"Should do it though perhaps not the most efficient way."

Do I get a prize if I find the most efficient way? A cup of coffee would be nice =P

Haydn.
0
Commented:
hmmm just to be picky you don't need 2 of the parenthesis there:

MyChars = Chr(64 + MyVal \ 26) & Chr(64 + MyVal Mod 26)

Haydn.
0
Haydn I am happy to buy/make you a cup of coffee or even a beer if you are ever in Southampton area. You know what it is like when you start down one route and cannot see the wood for the trees. Same solution just much neater!
0
Commented:
Hey wait - that won't work - grrrrr... I really do need that coffee - brb
0
\  |  (  |  )  /  /
_________________
|                            |
|                            |
|                             /--\
|                             |  |
\                           /\--/
\______________/

Here you go
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
ok, this is it - I've pulled the \ out of the line to avoid doing it twice:

MyDiv = (MyVal + 1) \ 27
MyChars = IIf(MyDiv = 0, Null, Chr(64 + MyDiv)) & Chr(65 + (MyVal - 1) Mod 26)

Haydn.
0
Commented:
Thanks tim... that coffee obviously did the trick =P
0
Author Commented:
Ok then.

The final

Dim MyVal As Integer
Dim MyChars As Variant

MyVal = InputBox("Enter number between 1 & 256")

MyChars = Chr(64 + MyVal \ 26) & Chr(64 + MyVal Mod 26)

MsgBox MyVal & " " & MyChars

End Sub

Works excellent

Original question worth 70 points, increasing to 140 then 70 each
0
Commented:
ARGH whered that \27 come from? since when did the alphabet have 27 letters?? I'm going insane!!

MyDiv = (MyVal - 1) \ 26   ' <-- NOT chinese alphabet!!
MyChars = IIf(MyDiv = 0, Null, Chr(64 + MyDiv)) & Chr(65 + (MyVal - 1) Mod 26)
0
Commented:
Nev - that doesn't work with values 1 - 26 as "Chr(64 + MyVal \ 26)" returns @... so A-Z would be returned as @A-@Z..

use my last post =)

Haydn.
0
Commented:
So the working final solution is:

Dim MyVal As Integer
Dim MyChars As Variant

MyVal = InputBox("Enter number between 1 & 256")

MyDiv = (MyVal - 1) \ 26
MyChars = IIf(MyDiv = 0, Null, Chr(64 + MyDiv)) & Chr(65 + (MyVal - 1) Mod 26)

MsgBox MyVal & " -> " & MyChars

Regards,

Haydn.

0
Author Commented:
Done.

Yessssssssssssssssssssssssssssssssss!!!

Thanks Guys
0
Commented:

Sorry Haydn that wont work either, try 26 and 52.

You need to consider how you treat the "zero" , e.g. in Decimal the next number after 9 is 10, in this "base 26" the next number after Z is AA not A0.

If you treat AA as 'Zero' then the following would work however the  transformation does not commence in the single alpha characters only in the double characters.

GetStringFromNumber = Chr(65 + inNumber \ 26) & Chr(65 + inNumber Mod 26)

Regards

Wobblynut
0
Commented:
Sorry

Must refresh more often...
0
Commented:
"Must refresh more often..."

Don't worry -  I have that problem all the time.

Haydn <--- Me.Refresh ;P
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.