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","")



Gary Brown wrote:

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


'/==============================================/
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
'/==============================================/

--

Dave Peterson