Get Column Letter from Column Number

[Guest]

Does anyone know of an easy way of getting the Column Letter if you know the
Column Number?
ie: a function that returns "D" if I input 4.
I've written a UDF but is there a better way?
I've listed my UDF below.
TIA,
--
Gary Brown
(e-mail address removed)

'/==============================================/
Private Function ColumnLetterFromNumber(iColNumber As Long) _
As String
'this function converts column number into letters
'this is designed to only work thru ZZ (702 columns),
' however, currently Excel only goes to IV (256 columns)
'Syntax: =ColumnLetterFromNumber(4)
' returns 'D'
'Gary Brown 05/11/2000

Dim str1stLetter As String, str2ndLetter As String

Application.Volatile True

ColumnLetterFromNumber = ""

On Error GoTo err_Function

If iColNumber <= 26 Then
str1stLetter = ""
Else
str1stLetter = Chr(Int(iColNumber / 26.001) + 64)
End If

str2ndLetter = _
Chr((iColNumber - _
(26 * Int(iColNumber / 26.001))) + 64)

ColumnLetterFromNumber = str1stLetter & str2ndLetter

exit_Function:
Exit Function

err_Function:
Debug.Print "Error: " & Err.Number & " - " & Err.Description
ColumnLetterFromNumber = ""
GoTo exit_Function

End Function
'/==============================================/

 

[N Harkawat]

=char(a1+64)
where A1 hold the value 4

 

[Dave Peterson]

This is from Chip Pearson:

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

And as a worksheet function (with the number in A1):

=SUBSTITUTE(ADDRESS(1,A1,4),"1","")

 

[Guest]

Brilliant :O>
Thanks.

 

[Guest]

I knew there had to be a better way.
Leave it to Chip.
Thanks!

 

[Guest]

Nice! I like the worksheet formula. I've seen some long ugly formula to
convert numbers to column letters and vice-versa.

Jason

 

[Myrna Larson]

Remember that will only work if you are talking about columns A:Z. It will
fail on AA and beyond.

 

[Dave Peterson]

That's pretty high praise coming from you. Thanks.

Or even a blind squirrel finds a nut now and then!