Convert Excel Column Number into Column Name

Update:

Sorter version of the VBA function:

Function Number2Char(c As Integer) As String
   Number2Char = Split(Cells(1, c).Address, "$")(1)
End Function

Courtesy of The Plaid Cow.

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

Courtesy of Stan Scott.

Original post:

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

Use this function as formula in the Excel formula bar, ex: =NUMBER2CHAR(52)

FIN.

Posts Related to Convert Excel Column Number into Column Name

Get Position of Last Column Containing Data with Excel VBA
If you read my latest update on how we can get position of last row containing data, then most probably you already have a glimpse ...
How to ceiling a decimal number in Excel?
Ceiling, or changing a specific decimal number into become its upper number, for example if we ceiling 3.25 we will got 4, or if we ...
Auto Expanding VLOOKUP Table Array
Ok, this time I want to talk about how to create a vlookup table array that automatically expanding in accordance with the numbers of available ...
How to get an Acronym using Excel VBA?
The purpose of the following example of Excel VBA macro is to get the acronym or an abbreviation of each of the first letter of ...
How To Find the Last Row That Contain Data in Excel?
UPDATE June 13, 2008: Another alternative way to find the last row with data : Function LastRow() As Long Dim ix As Long ix = ...

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

18 Responses to Convert Excel Column Number into Column Name

Andre Eugenio says:

November 27, 2008 at 9:49 pm

Save me a couple of hours !!

Thank you for sharing the code.

Andre.
admin says:

November 30, 2008 at 11:31 pm

you are welcome andre

Stan Scott says:

February 4, 2009 at 6:34 pm

This version is a bit shorter:

Poer says:

February 4, 2009 at 8:00 pm

Waw, thanks Stan >:D<
zung says:

March 12, 2009 at 5:43 am

Great macro, thanks for sharing
diskedit says:

March 12, 2009 at 1:38 pm

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.
hwsris says:

June 15, 2009 at 11:11 am

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

And thank Admin for your kick of this topic.
GarykPatton says:

June 16, 2009 at 10:21 am

How soon will you update your blog? I’m interested in reading some more information on this issue.
Excel VBA Macro says:

June 16, 2009 at 11:22 am

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

April 27, 2010 at 12:07 pm

Hi Admin,

I am trying to understand your code above and it fits in perfectly with what I am looking for. But I was wondering if line 13 is incomplete ?
iDiv = iDiv 26

Thanks,
Salma
Poer says:

May 19, 2010 at 12:10 am

Salma, there was an error when displaying html code. it should be iDiv \ 26.
The Plaid Cow says:

May 20, 2010 at 3:43 am
Even shorter…
```
Function Number2Char(c As Integer) As String
  Number2Char = Split(Cells(1, c).Address, "$")(1)
End Function
```
.-= The Plaid Cow´s last blog .. A Strangely Silent Microsoft =-.
Poer says:

May 20, 2010 at 8:03 am

Thanks The Plaid Cow
Pingback: Elektrische Zahnbuerste
Pingback: debt consolidation companies
Pingback: what is reverse mortgage
Pingback: debt consolidation companies
Pingback: what is a reverse mortgage