CODEWITHSUNDEEP
vbscripttype-conversion
Xeric
Xeric

Reputation: 53

How can I convert numbers to letters in VBS?

I want to take a number and convert it into lowercase a-z letters using VBScript.

For example:

In particular I am having trouble converting numbers after 26 when converting to 2 letter cell names. (aa, ab, ac, etc.)

Upvotes: 3

Views: 4183

Answers (3)

SchoolBusDriver
SchoolBusDriver

Reputation: 21

Neither of the solutions above work for the full Excel range from A to XFD. The first example only works up to ZZ. The second example has boundry problems explained in the code comments below.

// Function ColumnNumberToLetter(ColumnNumber As Integer) As String

' convert a column number to the Excel letter representation

Dim Div As Double
Dim iMostSignificant As Integer
Dim iLeastSignificant As Integer
Dim Base As Integer
Base = 26
' Column letters are base 26 starting at A=1 and ending at Z=26
' For base 26 math to work we need to adjust the input value to
' base 26 starting at 0
Div = (ColumnNumber - 1) / Base
iMostSignificant = Int(Div)
' The addition of 1 is needed to restore the  0 to 25 result value to 
' align with A to Z
iLeastSignificant = 1 + (Div - iMostSignificant) * Base
' convert number to letter
ColumnNumberToLetter = Chr(64 + iLeastSignificant)
' if the input number is larger than the base then the conversion we
' just did is the least significant letter
' Call the function again with the remaining most significant letters
If ColumnNumber > Base Then
    ColumnNumberToLetter = ColumnNumberToLetter(iMostSignificant) & ColumnNumberToLetter
End If

End Function //

Upvotes: 2

some1
some1

Reputation: 867

try this

function converts(n)
  Dim i, c, m
  i = n
  c = ""
  While i > 26
    m = (i mod 26)
    c = Chr(m+96) & c
    i = (i - m) / 26
  Wend
  c = Chr(i+96) & c
  converts = c
end function

WScript.Echo converts(1000)

Upvotes: 0

Clijsters
Clijsters

Reputation: 4256

You should have a look at the Chr(n) function.

This would fit your needs from a to z:

wscript.echo Chr(number+96)

To represent multiple letters for numbers, (like excel would do it) you'll have to check your number for ranges and use the Mod operator for modulo.

EDIT:

There is a fast food Copy&Paste example on the web: How to convert Excel column numbers into alphabetical characters

Quoted example from microsoft:

For example: The column number is 30.

The column number is divided by 27: 30 / 27 = 1.1111, rounded down by the Int function to "1".

i = 1

Next Column number - (i * 26) = 30 -(1 * 26) = 30 - 26 = 4.

j = 4

Convert the values to alphabetical characters separately,

i = 1 = "A"
j = 4 = "D"

Combined together, they form the column designator "AD".

And its code:

Function ConvertToLetter(iCol As Integer) As String
   Dim iAlpha As Integer
   Dim iRemainder As Integer
   iAlpha = Int(iCol / 27)
   iRemainder = iCol - (iAlpha * 26)
   If iAlpha > 0 Then
      ConvertToLetter = Chr(iAlpha + 64)
   End If
   If iRemainder > 0 Then
      ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
   End If
End Function

Upvotes: 5

Related Questions