vba code to convert number into a column letter

[AlphaRisk]

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.

 

[JLGWhiz]

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?

It works fine up through Column IV, which is where the xl95 - xl03 limit was. It will actually go to IZ before it hiccups, but anything after that is subject to error. SHG posted a code that appears to work without a glitch.

The last code that I posted was from an old method that relies on the cell address rather than manipulating the ASCII characters based on the column number. Anyhow, you now have a couple of choices that will work.

 

[Norie]

This has always worked for me.

Function AlphaColumn(colNumber As Long) As String
    
    AlphaColumn = Split(Cells(1, colNumber).Address, "$")(1)
    
End Function

 

[AlphaRisk]

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

That is exactly what i was looking for! Great job! Thanks for your help!

 

[Colin Legg]

It works fine up through Column IV, which is where the xl95 - xl03 limit was. It will actually go to IZ before it hiccups, but anything after that is subject to error.

No it doesn't. It works fine up to column AZ. On column 53 it returns "A[" instead of "BA", so it's pretty useless.

 

[TomDavis]

Actually ...

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

I have taken the time to register so I can tell you how delighted I was to discover your recursive code after slogging through so many incredibly verbose solutions. Thank you.

 

[Scott Huish]

My answer actually checks to see if it is a valid column in the version of Excel that you are using, but other than that the basics of my answer is this:

ColLtr = Replace(Cells(1, iCol).Address(0, 0), 1, "")

This seems less VERBOSE than the answer you used.

and Norie's is about the same length:

AlphaColumn = Split(Cells(1, colNumber).Address, "$")(1)

 

[TomDavis]

My answer actually checks to see if it is a valid column in the version of Excel that you are using, but other than that the basics of my answer is this:

ColLtr = Replace(Cells(1, iCol).Address(0, 0), 1, "")

This seems less VERBOSE than the answer you used.

and Norie's is about the same length:

AlphaColumn = Split(Cells(1, colNumber).Address, "$")(1)

Are you offended? Shall I commend your code as well?

There are no "incredibly verbose solutions" in this thread. The principal example to which I referred was Microsoft's. I found shg's post most interesting because it does so elegantly what Microsoft tried to do.

But I did not use shg's code. I used yours instead.

 

[Scott Huish]

It doesn't really matter, it just sounded like you were criticizing everybody's posts as being verbose. You didn't mention Microsoft in your comment. I'm sorry, I misunderstood.

 

[Mitnageek]

Actually ...

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

Now, that is a thing of beauty! Thanks Shg

 

[tongcy]

Now, that is a thing of beauty! Thanks Shg

How to rewrite function so it can convert column letter to number