get column cell location as a numeric value

Jonathan Brotto 1,066 Reputation points

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?

An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
1,976 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
2,197 questions
No comments
{count} votes

Accepted answer
  1. LesHay 6,751 Reputation points

    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
        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
        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

1 additional answer

Sort by: Most helpful
  1. Jiachen Li-MSFT 11,801 Reputation points Microsoft Vendor

    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.