?
Solved

Quickest way to return a letter based on a number

Posted on 2003-11-06
24
Medium Priority
?
264 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 280 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 280 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

764 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