In english   |   suomeksi

Homepage
IT Services
Consulting
Training
Get help now
Application Development
Support and Downloads
Contact Us
Send us a comment or question
Applications
MS Access
MS Excel
Coding and Macros
Excel VBA

Conversions between number and string of sequential letters

Number to string of sequential letters

The following VBA function converts a number value to corresponding alphapetical sequence of letters.

Parameters:


givenInt: between 1 and 2,147,483,647

Returns:


Return value is a string depending on the given parameter.



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
			

			

Example:

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

String of sequential letters to number

The following VBA function converts an alphapetical sequence of letters to corresponding number 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
			

			

Example:

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

Last modified on October 02 2024

Back to Support and Downloads

News
 
Retrieve Your favorite (NYSE) Stocks with our Excel tool
Microsoft Excel, Access VBA Developing Services
IT Training
WEB Design and applications
Published advice on related questions
Populate a Multi-column Combobox with a 2D array on Access
Multiple Search Text at the same time using Forms in Microsoft Access Database
VBA comboBox multicolumn remove blank row and specific value listed
Articles
Time parameters for Google search
 

Search from our site:



Registered Microsoft Partner

Phone: +358 50 566 6858
Helsinki, Finland
mika.oukka@netti.fi