Determining the Column Number in Excel Based on Column Name



To insert the column number from Cell C1 into Cell B1, I utilize the following
VBA code

Sub AdressColumn()
Sheet1.Range("B1").Value = Sheet1.Range("C1").Column
End Sub

In this instance, the appearance of the outcome on my spreadsheet is as follows:

    A     B    C
1         3

All this works fine so far.

Instead of using


, I would opt to use the letter of the column, specifically


, which should be inserted into Cell B1.

I attempted to use the formula provided, but it was unsuccessful in my situation as I do not have a specific number to use. Instead, I utilize the


function with reference to a Column.

Which modifications should I make to my formula in order for it to function properly?

Solution 1:

Extract the currency symbol from an absolute value of MSDT with the identifier
cell address

Sub AdressColumn()
    Sheet1.Range("B1").Value = split(Sheet1.Range("C1").address, "$")(1)
End Sub

… or separate the colon from the relative column address in its entirety.

Sub AdressColumn()
    Sheet1.Range("B2").Value = Split(Sheet1.Range("C1").EntireColumn.Address(0, 0), ":")(0)
End Sub

Solution 2:

user4039065 is nearly there, but the subscript should be


at the end of the line. For example, 677 corresponds to column “ZA” and column 16384 corresponds to “XFD” according to the function provided below:

    Const MAX_COL_NUMBER = 16384
    Function columnNumberToColumnString(col As Integer) As String
      If col > MAX_COL_NUMBER Or col < 1 Then
        columnNumberToColumnString = "ERROR":     Exit Function
        columnNumberToColumnString = Split(Columns(col).Address, "$")(2)
      End If
' delete code block below after seeing how Split works
msg = "Split <" & Columns(col).Address & ">"
    For i = 0 To UBound(Split(Columns(col).Address, "$"))
        msg = msg + Chr(13) & Chr(10) & "Substring " & i & _
              " is <" & Split(Columns(col).Address, "$")(i) & ">"        
MsgBox msg       
  End Function

By substituting




, column 26 yields


instead of just


, as elaborated below.

The function


returns an array of length 3, showcasing the step-by-step process leading to the final result when a valid
Excel column
address is utilized.

The displayed results for 256, as shown in the code block labeled


, are:

Address of column number 256 is <$IV:$IV>
Substring 0 is <>           (since first $ is first character, strip it and all after)
Substring 1 is         (since second $ is after the :, strip it and all after)
Substring 2 is          (since : above is a usual delimiter, strip it)


“Returns a zero-based, one-
dimensional array
that contains all substrings of the given expression (first argument), when the third argument is omitted.”

Frequently Asked Questions