return Column as string

  • Thread starter Thread starter Eliezer
  • Start date Start date
E

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!
 
Hi
why not use INDIRECT with the second parameter set to 'FALSE'
Enter your reference in the R1C1 style
 
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))
 
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
 
Back
Top