return Column as string

[Eliezer]

Using the COLUMN() function, I can get a number. Is there
any function which will return the letter value of a
column? I'm trying to make a somewhat complex INDIRECT()
reference, so I can't use the ADDRESS() function. Thanks!

 

[Frank Kabel]

Hi
why not use INDIRECT with the second parameter set to 'FALSE'
Enter your reference in the R1C1 style

 

[Don Guillett]

See if this helps
Sub whatcolletter()
MsgBox Left(ActiveCell.Address(0, 0), 2 + (i < 27))
End Sub

or put this formula somewhere. If a1 has 2, you get B
=LEFT(ADDRESS(1,A1,2),1+(A1>26))

 

[Gord Dibben]

Would you settle for a UDF?


Function GetColNum(myColumn As String) As Integer
GetColNum = Columns(myColumn & ":" & myColumn).Column
End Function

Function GetColLet(ColNumber As Integer) As String
GetColLet = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber > 26))
End Function

Gord Dibben Excel MVP