Get Column Letter from Column Number
 

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

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

"Gary Brown" m wrote in
message ...
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
'/==============================================/

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

Brilliant :O
Thanks.
--
Gary Brown

Please rate this posting if it is helpful to you.


"N Harkawat" wrote:

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

"Gary Brown" m wrote in
message ...
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
'/==============================================/

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

Please rate this posting if it is helpful to you.


"Dave Peterson" wrote:

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

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

Jason

"Dave Peterson" wrote:

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

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

On Fri, 10 Jun 2005 06:42:04 -0700, "Gary Brown"
m wrote:

Brilliant :O
Thanks.

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

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



Jason Morin wrote:

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

Jason

"Dave Peterson" wrote:

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


--

Dave Peterson