[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Quickest way to return a letter based on a number

Posted on 2003-11-06
24
Medium Priority
?
268 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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 …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

649 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