Reputation: 282
I have searched through this site and googled for a formula. I need to calculate an Excel column number FROM the letter such as:
A=1 B=2 .. AA=27 AZ=52 ... AAA=703
The code seems to be 1 digit off after random cycles of the alphabet(AZ -> BA == off digit). It also will seemingly randomly produce the same integer from two different inputs:
GetColumnNumber(xlLetter : Text) : Integer //Start of function
StringLength := STRLEN(xlLetter);
FOR i := 1 TO StringLength DO BEGIN
Letter := xlLetter[i];
IF i>1 THEN
Count += ((xlLetter[i-1]-64) * (i-1) * 26) - 1;
Count += (Letter - 64);
END;
EXIT(Count); //return value
My code example is written in C/AL which is used for Dynamics NAV, but I can write C# or vb.net as well so I wouldn't mind if an example was in either of those languages.
Upvotes: 3
Views: 5761
Reputation: 1
In VBA: (Recursive Function)
Function Get_Col_Number(strColName As String, dRunningNo As Integer) As Double
Dim dCurrentColNo As Double
Dim dMultipleValue As Double
strColName = Ucase(strColName)
If (dRunningNo <= 0) Then Get_Col_Number = 0: Exit Function
dCurrentColNo = ((Asc(Mid(strColName, dRunningNo, 1)) - Asc("A") + 1))
dMultipleValue = 26 ^ (Len(strColName) - dRunningNo)
Get_Col_Number = (dCurrentColNo * dMultipleValue) + Get_Col_Number(strColName, (dRunningNo - 1))
End Function
Use this Function as below.
Sub Main()
Dim StrGetNoForThisColumnName As String
StrGetNoForThisColumnName = "Xfd"
Msgbox "Final Result : " & Get_Col_Number(StrGetNoForThisColumnName, Len(StrGetNoForThisColumnName))
End Sub
Upvotes: 0
Reputation: 8501
In VBA:
Function ColLetter(C As Integer) As String
If C < 27 Then
ColLetter = Chr(64 + C)
Else
ColLetter = ColLetter((C - 1) \ 26) & ColLetter((C - 1) Mod 26 + 1)
End If
End Function
Upvotes: 3
Reputation: 166980
In VBA:
Public Function GetCol(c As String) As Long
Dim i As Long, t As Long
c = UCase(c)
For i = Len(c) To 1 Step -1
t = t + ((Asc(Mid(c, i, 1)) - 64) * (26 ^ (Len(c) - i)))
Next i
GetCol = t
End Function
Upvotes: 3