vba code to convert number into a column letter

AlphaRisk

New Member
Joined
Mar 22, 2012
Messages
3
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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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

Thanks for your help guys!
 
Upvote 0
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)
 
Upvote 0
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

Thanks for your help guys!

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.
 
Upvote 0
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
 
Upvote 0
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

Thanks for your help guys!


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
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top