Solved

Quickest way to return a letter based on a number

Posted on 2003-11-06
24
249 Views
Last Modified: 2010-05-18
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
Comment
Question by:NevHolland
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 5
  • 5
  • +1
24 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 9692811
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
 
LVL 2

Author Comment

by:NevHolland
ID: 9692904
I'll start there what would happen when I get to Column 27 or in Excel AA?
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 9692950
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 2

Author Comment

by:NevHolland
ID: 9693106
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
 
LVL 8

Expert Comment

by:HaydnH
ID: 9693256
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
 
LVL 8

Expert Comment

by:HaydnH
ID: 9693261
Where MyChars is the variable holding the string A, AA, IV etc

Haydn.
0
 
LVL 2

Author Comment

by:NevHolland
ID: 9693426
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
 
LVL 43

Expert Comment

by:TimCottee
ID: 9693474
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
 
LVL 8

Expert Comment

by:HaydnH
ID: 9693493
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
 
LVL 8

Expert Comment

by:HaydnH
ID: 9693500
"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
 
LVL 8

Expert Comment

by:HaydnH
ID: 9693507
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
 
LVL 43

Expert Comment

by:TimCottee
ID: 9693516
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
 
LVL 8

Expert Comment

by:HaydnH
ID: 9693519
Hey wait - that won't work - grrrrr... I really do need that coffee - brb
0
 
LVL 43

Accepted Solution

by:
TimCottee earned 70 total points
ID: 9693585
      \  |  (  |  )  /  /
  _________________
  |                            |
  |                            |
  |                             /--\
  |                             |  |
   \                           /\--/
    \______________/

Here you go
0
 
LVL 8

Expert Comment

by:HaydnH
ID: 9693599
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
 
LVL 8

Expert Comment

by:HaydnH
ID: 9693600
Thanks tim... that coffee obviously did the trick =P
0
 
LVL 2

Author Comment

by:NevHolland
ID: 9693616
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
 
LVL 8

Expert Comment

by:HaydnH
ID: 9693617
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
 
LVL 8

Expert Comment

by:HaydnH
ID: 9693625
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
 
LVL 8

Assisted Solution

by:HaydnH
HaydnH earned 70 total points
ID: 9693633
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
 
LVL 2

Author Comment

by:NevHolland
ID: 9693640
Done.

Yessssssssssssssssssssssssssssssssss!!!

Thanks Guys
0
 
LVL 2

Expert Comment

by:wobblynut
ID: 9693662

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
 
LVL 2

Expert Comment

by:wobblynut
ID: 9693681
Sorry

Must refresh more often...
0
 
LVL 8

Expert Comment

by:HaydnH
ID: 9693889
"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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question