Converting a Column Number to a Column Letter

**Rasm** · 04-07-2011, 03:26 AM

Code:

Astr = Split(Cells(1, Along).Address, "$")(1)

Where
Along = Column number to be converted to letter

Astr = column letter returned

**Rick Rothstein** · 03-22-2012, 08:01 AM

Originally Posted by Rasm

Code:

Astr = Split(Cells(1, Along).Address, "$")(1)

Where
Along = Column number to be converted to letter
Astr = column letter returned

That code line looks awfully long to me.

You can shorten your typing by an entire character if you do it this way

...

Code:

Astr = Split(Columns(Along).Address, "$")(2)

**Preeti Verma** · 05-22-2012, 11:19 AM

Option Explicit

'
'UDF to obtain column field for any cell(i,j)
'

Code:

Function ColFld(ByVal num1 As Long, ByVal num2 As Long) As String
Dim str As String, i As Long
str = Replace(Cells(num1, num2).Address, "$", "")
For i = 0 To 9
str = Replace(str, CStr(i), "")
Next i
ColFld = str
End Function

'
'UDF to obtain cell address field for any cell(i,j)
'

Code:

Function CellFld(ByVal v1 As Long, ByVal v2 As Long) As String
Dim st As String, j As Long
For j = 0 To 9
st = Replace(Cells(v1, v2).Address, "$", "")
Next j
CellFld= st
End Function

'
'UDF to obtain row field for any cell(i,j)
'

Code:

Function RowFld(ByVal num1 As Long, ByVal num2 As Long) As String
Dim str As String, i As Long
str = Replace(Cells(num1, num2).Address, "$", "")
For i = 65 To 90
str = Replace(str, Chr(i), "")
Next i
RowFld = str
End Function

**KingTamo** · 12-15-2012, 01:48 AM

When testing the code I have got an error

Code:

Sub Test()
Astr = Split(Cells(1, Along).Address, "$")
MsgBox Astr
End Sub

**Rick Rothstein** · 12-15-2012, 02:03 AM

Originally Posted by KingTamo

When testing the code I have got an error

Code:

Sub Test()
Astr = Split(Cells(1, Along).Address, "$")
MsgBox Astr
End Sub

Split returns an array of values, not a single value, so you have to specify which member of the array you want...

MsgBox Astr(1)

**KingTamo** · 12-17-2012, 01:59 AM

Thanks for reply
But I have an error too when testing the code

Code:

Sub Test()
Astr = Split(Cells(1, Along).Address, "$")
MsgBox Astr(1)
End Sub

**Rick Rothstein** · 12-17-2012, 02:29 AM

Originally Posted by KingTamo

Thanks for reply
But I have an error too when testing the code

Code:

Sub Test()
Astr = Split(Cells(1, Along).Address, "$")
MsgBox Astr(1)
End Sub

Did you notice the explanation for "Along" from the first message (from where the code snippet came)? Namely...

"Where
Along = Column number to be converted to letter"

The 1 inside the Cells call is the row and Along is a variable that should be assigned (or fully replaced by) a column letter.