CODEWITHSUNDEEP
excel
user7039042
user7039042

Reputation:

How to convert Excel column name to number and vice versa?

In Excel the column names go like A, B, C, and when it goes past 26 it goes to AA for 27, AB, etc. When it hits AZ then it comes BA, and so on.

How can I convert between the names and numbers easily? I'm looking for a calculator that can switch between the two.

Ex: When I punch in "28", it should return "AB". If I punch in "AB", "28". I'd prefer an online calculator already made.

Upvotes: 0

Views: 383

Answers (2)

QHarr
QHarr

Reputation: 84465

Some user defined functions for code and sheet

Option Explicit
Public Sub TEST()
    Debug.Print GetColNumber("AB")
    Debug.Print GetColLetter(28)
End Sub
Public Function GetColNumber(ByVal columnHeader As String) As Long
     GetColNumber = Evaluate("CELL(""col"", " & columnHeader & "1)")
End Function

Public Function GetColLetter(ByVal columnNumber As Long) As String
     GetColLetter = Split(Evaluate("ADDRESS(1," & columnNumber & ",4)"), 1)(0)
End Function

Upvotes: 1

user4039065
user4039065

Reputation:

For AB from 28 try,

dim cola as string

cola = split(cells(1, 28).address, "$")(1)

debug.print cola

For 28 from AB try,

dim coli as long

coli = cells(1, "AB").column

debug.print coli

On the worksheet type AB in A1 and use this formula in B1 to get 28,

=column(indirect(a1&":"&a1))

On the worksheet type 28 in A1 and use this formula in B1 to get AB,

=substitute(address(1, a1, 4, 1), 1, "")

Upvotes: 1

Related Questions