• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

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
0
NevHolland
Asked:
NevHolland
  • 12
  • 5
  • 5
  • +1
2 Solutions
 
TimCotteeCommented:
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
 
NevHollandAuthor Commented:
I'll start there what would happen when I get to Column 27 or in Excel AA?
0
 
TimCotteeCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
NevHollandAuthor 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
 
HaydnHCommented:
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
 
HaydnHCommented:
Where MyChars is the variable holding the string A, AA, IV etc

Haydn.
0
 
NevHollandAuthor 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
 
TimCotteeCommented:
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
 
HaydnHCommented:
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
 
HaydnHCommented:
"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
 
HaydnHCommented:
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
 
TimCotteeCommented:
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
 
HaydnHCommented:
Hey wait - that won't work - grrrrr... I really do need that coffee - brb
0
 
TimCotteeCommented:
      \  |  (  |  )  /  /
  _________________
  |                            |
  |                            |
  |                             /--\
  |                             |  |
   \                           /\--/
    \______________/

Here you go
0
 
HaydnHCommented:
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
 
HaydnHCommented:
Thanks tim... that coffee obviously did the trick =P
0
 
NevHollandAuthor 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
 
HaydnHCommented:
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
 
HaydnHCommented:
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
 
HaydnHCommented:
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
 
NevHollandAuthor Commented:
Done.

Yessssssssssssssssssssssssssssssssss!!!

Thanks Guys
0
 
wobblynutCommented:

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
 
wobblynutCommented:
Sorry

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

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

Haydn <--- Me.Refresh ;P
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 12
  • 5
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now