Convert Excel Column Number into Column Name

Posted on the October 29th, 2008 under Workbook and Worksheet by Poer @ Excel VBA/Macro

Converting Excel column number into column name, for example 1 into A or 52 into AZ, is needed occasionally, and the following function will do the convertion for us.

Function Number2Char(ByVal vNumber)
    Dim iDiv As Double, iMod As Integer
    If vNumber < 1 Then Exit Function

    iDiv = vNumber
    While iDiv > 26
        iMod = iDiv Mod 26
        If iMod = 0 Then
            iMod = 26
            iDiv = iDiv - 1
        End If
        Number2Char = Chr(64 + iMod) & Number2Char
        iDiv = iDiv  26
    Wend

    Number2Char = Chr(64 + iDiv) & Number2Char
End Function

FIN.

Related Entries

External Resources

Microsoft Excel 2003/2007 Video Tutorials
Step-by-step video guide to mastering Charts, PivotTable, Data Analysis and Macro programming in Microsoft Excel in 5 hours.
101 Secrets of Microsoft Excel
Discover 101 of Excels little-known secrets that have been hiding right under your nose.

Tagged with: column number into column name

9 Responses to 'Convert Excel Column Number into Column Name'

RSS

November 27, 2008 at 9:49 pm
Andre Eugenio

Save me a couple of hours !!

Thank you for sharing the code.

Andre.
November 30, 2008 at 11:31 pm
admin

you are welcome andre

February 4, 2009 at 6:34 pm
Stan Scott

This version is a bit shorter:

Function Number2Char(ByVal vNumber)

    Dim addr As String
    If vNumber  256 Then Exit Function

    addr = Range("A1").Offset(0, vNumber - 1).Address
    Number2Char = Replace(Replace(addr, "$", ""), Range(addr).Row, "")

End Function

February 4, 2009 at 8:00 pm
Poer

Waw, thanks Stan >:D<
March 12, 2009 at 5:43 am
zung

Great macro, thanks for sharing
March 12, 2009 at 1:38 pm
diskedit

Good job, Stan. I am going to use this with only slight changes. One suggestion – since Excel 2007 supports 16384 columns, I changed the If line to

If vNumber > Cells.Columns.Count Then Exit Function

That makes Excel count the number of columns in the worksheet and then Exits if vNumber is bigger than that.
June 15, 2009 at 11:11 am
hwsris

Thanks Stan Scott your code is very helpfull and shorter than Admin.

And thank Admin for your kick of this topic.
June 16, 2009 at 10:21 am
GarykPatton

How soon will you update your blog? I’m interested in reading some more information on this issue.
June 16, 2009 at 11:22 am
Excel VBA Macro

Hai Gary, at least once a week, can be more. What kind of information do need more?