Convert column ID into a letter 1

[jobur]

Hi everyone

I am sure someone must have asked this question before but I cannot find anything relating to it. In Excel I want to convert a Column ID into its respective letter. For example 2 returns "B", 82 retuns "CD" and so on

Any hints?

Thanks

Regards

JB

 

[xlbo]

any reason ??
what is the point ??

To reference a cell use:

cells(rowNumber,ColNumber)

to reference a column use:

Columns(ColNumber)

to get the cell address:

cells(RowNumber,ColNumber).address

...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[FaneDuru]

Try this:
1. Using Excel formula:
If you put in "E1" the number of columm to be converted the formula is:
"=LEFT(ADDRESS(1,E1,4),LEN(ADDRESS(1,E1,4))-1)"
2. Using VBA:

Sub Name_for_Col()
 MsgBox ColName(230)
End Sub
Public Function ColName(intNum As Integer) As String
Dim Adr As String
    If intNum > 256 Then
        MsgBox "256 is the maximum for Excel Columns", _
                vbExclamation + vbOKOnly, "GetColName"
        Exit Function
    End If
    Adr = Cells(1, intNum).Address
    ColName = Mid(Adr, 2, Len(Adr) - 3)
End Function

Fane Duru

 

[PHV]

Another way (xl2k or above):
MsgBox "ColumnID " & ColumnID & " = '" & Split(Columns(ColumnID).Address(False, False), ":")(0) & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[macropod]

Yet another way:

Sub GetCol()
Dim colName As String
colName = ActiveCell.Address(, 0)
MsgBox "Column: " & Left(colName, InStr(colName, "$") - 1)
End Sub


Cheers

 

[SkipVought]

BTW,

I agree with Geoff's 2 question.

However, in an attempt to contribute to the exaustive nature of this response...

Function ColLetter(iCol As Integer)
    ColLetter = Chr(64 + iCol Mod 26)
    If iCol > 26 Then
        ColLetter = Chr(64 + Int((iCol - 1) / 26)) & ColLetter
    End If
End Function

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}