get column cell location as a numeric value

Question

I know excel has columns marked as letters but is there a way to get the column location if it is something like column AC as integer 29?

Answer 1

Hi
Here is an alternative. I am unsure if you are talking about using VB.NET code to do this. If so then the two functions shown below may help. One gets the Integer value of a column addressed as a single or a double character string. The second gets the single or double character string from an Integer column number. There is a STOP where you can hover inspect values as a means of testing.

Option Strict On
Option Explicit On
Public Class Form1
    Dim a As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        ' column address can only be a single or double
        ' character string from A-Z case insensitive

        Dim a1 As Integer = GetNumCol("A") '=1
        Dim a2 As Integer = GetNumCol("Z") '=26
        Dim a3 As Integer = GetNumCol("AC") '=29
        Dim a4 As Integer = GetNumCol("XZ") '=650
        Dim a5 As Integer = GetNumCol("YA") '=651
        Dim a6 As Integer = GetNumCol("YZ") '=676
        Dim a7 As Integer = GetNumCol("ZA") '=677
        Dim a8 As Integer = GetNumCol("ZY") '=701
        Dim a9 As Integer = GetNumCol("ZZ") '=702
        Dim a0 As Integer = GetNumCol("ac") '=29

        ' from 1 to 702 (any above will return ZZ)
        Dim s1 As String = GetCharCol(1) '= A
        Dim s2 As String = GetCharCol(26) '= Z
        Dim s3 As String = GetCharCol(29) '= AC
        Dim s4 As String = GetCharCol(650) '= XZ
        Dim s5 As String = GetCharCol(651) '= YA
        Dim s6 As String = GetCharCol(676) '= YZ
        Dim s7 As String = GetCharCol(677) '= ZA
        Dim s8 As String = GetCharCol(701) '= ZY
        Dim s9 As String = GetCharCol(702) '= ZZ
        Stop
    End Sub
    Function GetNumCol(s As String) As Integer
        Dim C() As Char = s.ToUpper.ToArray
        Dim one As Integer = a.IndexOf(C(0)) + 1
        If C.Count = 1 Then Return one
        Dim two As Integer = a.IndexOf(C(1)) + 1
        Return one * 26 + two
    End Function
    Function GetCharCol(i As Integer) As String
        If i < 1 Then Return "?"
        If i < 27 Then Return a.Substring(i - 1, 1)
        Dim b As Integer = i \ 26
        If b > 26 Then Return "ZZ"
        Dim c As Integer = i Mod 26
        Dim ad1 As Integer = CInt(IIf(i Mod 26 = 0, 1, 0))
        Dim ad2 As Integer = CInt(IIf(i Mod 26 = 0, -25, 1))
        Dim n1 As String = a.Substring(b - 1 - ad1, 1)
        Dim n2 As String = a.Substring(c - ad2, 1)
        Return (n1 & n2).ToUpper
    End Function
End Class

Answer 2

Hi @Jonathan Brotto ,
You can optionally use the function below to convert column names and column numbers.

'Convert by dividing by 26 loop  
Function Name2Num1(ByVal ColName As String) As Long  
    Dim i As Integer  
    Dim intLen As Integer  
    Name2Num1 = 0  
    intLen = Len(ColName)  
    If intLen > 0 Then  
        ColName = UCase$(ColName)  
        For i = 1 To intLen  
            Name2Num1 = Name2Num1 + (Asc(Mid(ColName, i, 1)) - 64) * 26 ^ (intLen - i)  
        Next i  
    End If  
End Function  
  
'Use the cell's Column to return the column number  
   
Function Name2Num(ByVal ColName As String) As Long  
    Name2Num = 0  
    On Error Resume Next  
    Name2Num = Cells(1, ColName).Column  
End Function    

Hope the code above could be helpful.
Best Regards.
Jiachen Li

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.