Convert Numbers to Sequential Letters with VBA

These VBA functions convert positive numbers to sequential alphabetic identifiers and back again.

The sequence starts with A = 1, B = 2 and Z = 26. After Z it continues with AA = 27, AB = 28 and so on. The pattern is familiar from Excel column letters, but the function is not limited to Excel's worksheet columns.

One practical use is creating relatively short alphabetic IDs from large sequential numbers.

Number to sequential letters

The function accepts a VBA Long value and converts it to the corresponding sequence of letters.

Parameter

givenInt: a positive number between 1 and 2,147,483,647.

Returns

A sequential alphabetic identifier corresponding to the supplied number.

Function Num2String(givenInt As Long) As String
'Number to string:
'-------------------------------------------
'Converts given numbervalue to corresponding
'sequential string consisting of letters
'Author: Mika Oukka, www.onlinetuki.com
'May 8, 2014
'-------------------------------------------

    Dim tempVal As Double
    Dim letterVal As String

    If givenInt <> 0 Then
        tempVal = givenInt

        Do
            If Int(tempVal) Mod 26 = 0 Then
                letterVal = "Z"
            Else
                letterVal = Chr(64 + Int(tempVal) Mod 26)
            End If

            Num2String = letterVal + Num2String

            If Left(Num2String, 1) = "Z" Then
                tempVal = tempVal - 1
            End If

            tempVal = tempVal / 26

        Loop Until tempVal <= 1

    End If

End Function

Examples

Input	Returns
1	A
2	B
26	Z
27	AA
16384	XFD
1000000	BDWGN

Sequential letters to number

The reverse function converts the alphabetic identifier back to its corresponding numeric value.

Function String2Number(givenString As String) As Long
'Converting String to number:
'-------------------------------------------
'Converts given string consisting of letters
'to corresponding numeric value
'Author: Mika Oukka, www.onlinetuki.com
'May 12, 2020
'-------------------------------------------

    Dim numberPlace As Long, letterIndex As Long, i As Long

    letterIndex = 0

    For i = Len(givenString) To 1 Step -1

        String2Number = String2Number + _
            (Asc(Mid(givenString, i, 1)) - 64) * 26 ^ letterIndex

        letterIndex = letterIndex + 1

    Next

End Function

Examples

Input	Returns
A	1
B	2
Z	26
AA	27
XFD	16384
BDWGN	1000000

How the sequence works

The sequence behaves similarly to a base-26 numbering system, except that there is no zero character. The letters A to Z represent values 1 to 26. This is why 26 is Z and the next value, 27, becomes AA.

Although the same notation is used for Excel column letters, these functions can continue well beyond Excel's XFD column and can therefore also be used for compact sequential identifiers.