7

Possible Duplicate:
VBA function to convert column number to letter?

I want to have the column letters past the Z column, there are many techniques for getting it before Z column but after Z, nothing seems to work Is there a way to do so?

Community
  • 1
  • 1
Viladimir
  • 137
  • 1
  • 2
  • 9

3 Answers3

17

Another method:

Public Function ColumnLettersFromRange(rInput As Range) As String

    ColumnLettersFromRange = Split(rInput.Address, "$")(1)

End Function
Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
7

This should do the job:

Function ColumnName(rng As Range) As String
    Dim s As String
    s = rng.Address(False, False)
    ColumnName = Left(s, Len(s) - Len(Format(rng.Row, "0")))
End Function
Peter Albert
  • 16,917
  • 5
  • 64
  • 88
1

Using worksheet functions to calculate the column letter isn't really a good plan. Using Peter Albert's VBA method is a much nicer way to do it!

I had a go at making one using worksheet functions, just for fun :/

A-ZZ

=IF(A1<27,CHAR(64+A1),IF(A1<703,CHAR(64+INT(A1/26))&CHAR(A1-INT(A1/26)+64),"TOO BIG!"))

A-XFD (doesn't work)

=IF(A1<27,CHAR(64+A1),IF(A1<703,CHAR(64+INT(A1/26))&CHAR(A1-(INT(A1/26)*26)+64),CHAR(64+INT(A1/676))&CHAR(64+(INT(A1-(INT(A1/676)*676))/26))&CHAR(64+INT(A1-((INT(A1-(INT(A1/676)*676))/26)*26)))))

The final one falls over trying to work out the 3rd character in the address, I just cant be bothered as using it is not a good plan!

NickSlash
  • 4,758
  • 3
  • 21
  • 38
  • Thats the formula I ended up with (works till XFD): `=IF(QUOTIENT(COLUMN(A1),26^2),CHAR(64+QUOTIENT(COLUMN(A1),26^2)),"")& IF(QUOTIENT(COLUMN(A1),26^1),CHAR(64+QUOTIENT(COLUMN(A1)-QUOTIENT(COLUMN(A1),26^2)*26^2,26^1)),"")& CHAR(64+MOD(COLUMN(A1),26)) ` – Peter Albert Dec 09 '12 at 18:30
  • 2
    Try `=SUBSTITUTE(ADDRESS(1,A1,4),1,"")` – barry houdini Jan 11 '13 at 14:50