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