Quick Q. Return letter of column

[spacebouncer]

Hi, is there an easy way to return the letter of a column, rather than its number? Thanks

 

[bbott]

Something like this:

=CHAR(COLUMN(A1)+64)

will return "A" for cell A1. Just sub in a cell from the desired column.

 

[johnmpl]

Hi!

Try This formula:

=SUBSTITUTE(ADDRESS(ROW(),COLUMN(),4),ROW(),"")

Or

=SUBSTITUTE(ADDRESS(ROW(A1),COLUMN(A1),4),ROW(A1),"")

This formula works in all the columns in Excel.

The another formula post for bbott

=CHAR(COLUMN(A1)+64)

Only works until column Z.

Blessings!

 

[spacebouncer]

Thanks guys.

I went for this in the end:

Function columnletter(colnumber As Integer) As String

If colnumber <= 26 Then
    columnletter = Chr(((colnumber + 1) Mod 26) + 63)
End If

If colnumber > 26 And colnumber <= 26 * 26 Then
    columnletter = Chr((Int(colnumber / 26) + 64))
    columnletter = columnletter & Chr(((colnumber + 1) Mod 26) + 63)
End If

End Function

Works for the first 676, that'll do for me! Thanks John, thats gonna be a lot less like reinventing the wheel. Cheers both

 

[NewOrderFac33]

How about this - it's a bit shorter?

Function GCL(MyRange As Range) As String
   GCL = Split(MyRange.Address, "$")(1)
End Function

 

[spacebouncer]

Sorry for the late reply. That's spot on! Thanks Rooney!