# vba code to convert number into a column letter

This is a discussion on vba code to convert number into a column letter within the Excel Questions forums, part of the Question Forums category; I am using some code which currently converts a number into the corrisponding column letter. eg 1 = A, 2 ...

1. ## vba code to convert number into a column letter

I am using some code which currently converts a number into the corrisponding column letter. eg 1 = A, 2 = B, 27 = AA, 56 = BD etc.

Here is the code:

If mycolumn > 26 Then
Mcl = Chr(Int((mycolumn - 1) / 26) + 64) & Chr(Int((mycolumn - 1) Mod 26) + 65)
Else
Mcl = Chr(mycolumn + 64)
End If

Where "mycolumn" = number & "Mcl" = letter.

This code will only calculate correctly for numbers up to 702 as this is ZZ.
How can i add onto this code to calculate for 3 letter column. eg AAA = 703, 4206 = EFT.

I cant seem to get my head around it.

2. ## Re: vba code to convert number into a column letter

Welcome to the board.

One way:

Code:
```Function ColLtr(iCol As Long) As String
If iCol > 0 And iCol <= Columns.Count Then
ColLtr = Evaluate("substitute(address(1, " & iCol & ", 4), ""1"", """")")
End If
End Function```

3. ## Re: vba code to convert number into a column letter

Why do you need to do this? You can work with column numbers directly in VBA.

But here you go:
Code:
```
Function ColLtr(iCol As Long) As String
If iCol > 0 And iCol <= Columns.Count Then ColLtr = Replace(Cells(1, iCol).Address(0, 0), 1, "")
End Function```

4. ## Re: vba code to convert number into a column letter

This would get you up to ZZZ:

Code:
Sub colLtr()
Dim mycolumn
mycolumn = 150
If mycolumn > 52 Then
Mcl = Chr(Int((mycolumn - 1) / 52) + 64) & Chr(Int((mycolumn - 27) / 26) + 64) & Chr(Int((mycolumn - 27) Mod 26) + 65)
ElseIf mycolumn > 26 Then
Mcl = Chr(Int((mycolumn - 1) / 26) + 64) & Chr(Int((mycolumn - 1) Mod 26) + 65)
Else
Mcl = Chr(mycolumn + 64)
End If
End Sub

But as previously noted, you can work with column numbers by using the Cells index method.

5. ## Re: vba code to convert number into a column letter

I am not sure on how to use the index method as i have not run functions before only subs. How would i be able to use that code to take a number in one cell and return the column letter in another?

JLGWhiz - The code you wrote is on the right lines but does not work correctly. eg for number 703 should return AAA however it returns MZA. Also it should start with:

If mycolumn > 702 Then

6. ## Re: vba code to convert number into a column letter

Here is the first hit I got on google:

http://support.microsoft.com/kb/833402

I wonder why they didn't test it properly before publishing it?

7. ## Re: vba code to convert number into a column letter

This would get you to column FXSHRXW (for Excel 2050):

Code:
```Function ColLtr(ByVal iCol As Long, Optional sCol As String = "") As String
' shg 2012
If iCol = 0 Then
ColLtr = sCol
Else
sCol = Chr(65 + (iCol - 1) Mod 26) & sCol
iCol = (iCol - 1) \ 26
ColLtr = ColLtr(iCol, sCol)
End If
End Function```
E.g., =ColLtr(2147483647)

8. ## Re: vba code to convert number into a column letter

Originally Posted by AlphaRisk
I am not sure on how to use the index method as i have not run functions before only subs. How would i be able to use that code to take a number in one cell and return the column letter in another?

JLGWhiz - The code you wrote is on the right lines but does not work correctly. eg for number 703 should return AAA however it returns MZA. Also it should start with:

If mycolumn > 702 Then

Yes, I noticed that my math was in error and am working on that. I probably have the correct code tucked away somewhere but had not been active in the forums for a while and forgot where I kept stuff. If I get it, I will post it. There is a simple way to do it but my memory lapse is preventing me from producing it.

9. ## Re: vba code to convert number into a column letter

Actually ...

Code:
```Function ColLtr(ByVal iCol As Long) As String
' shg 2012
If iCol Then ColLtr = ColLtr((iCol - 1) \ 26) & Chr(65 + (iCol - 1) Mod 26)
End Function```

10. ## Re: vba code to convert number into a column letter

Originally Posted by AlphaRisk
I am not sure on how to use the index method as i have not run functions before only subs. How would i be able to use that code to take a number in one cell and return the column letter in another?

JLGWhiz - The code you wrote is on the right lines but does not work correctly. eg for number 703 should return AAA however it returns MZA. Also it should start with:

If mycolumn > 702 Then

OK, I came to my senses. Here is the code:

Code:
Sub colLtr()
Dim mycolumn
mycolumn = 1450
Mcl = Left(Cells(1, mycolumn).Address(1, 0), InStr(1, Cells(1, mycolumn).Address(1, 0), "\$") - 1)
MsgBox Mcl
End Sub

Page 1 of 3 123 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•