Solved

Quickest way to return a letter based on a number

Posted on 2003-11-06
24
236 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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

803 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