REAL-TIME

VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Effortlessly
Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

Excel VBA Column Number to Name example macro will convert column number to Excel alphabetic Letter character column name. Most of the time while automating many tasks using Excel VBA, it may be required. Please find the following details about conversion of number to name. You can also say column number to column string. Or number to letter using Excel VBA. Please find the following different strategic functions to get column number to column name.

Column Number to Column Name

Column Number to Column Name: Easiest Approach by Replacing a Cell Address

Please find the following details about conversion from Column Number to Column Name in Excel VBA. Here we are referring a cell address.

'Easiest approach by Replacing a Cell address
Function fnColumnToLetter_CellAdressReplace(ByVal ColumnNumber As Integer)
    fnColumnToLetter_CellAdressReplace = Replace(Replace(Cells(1, ColumnNumber).Address, "1", ""), "$", "")
End Function

Column Number to Column Name: Example and Output

Please find the below example. It will show you how to get column name from column number using Excel VBA. In the below example ‘fnColumnToLetter_CellAdressReplace’ is a function name, which is written above. And “105” represents the row number of ‘fnColumnToLetter_CellAdressReplace’ function parameter.

Sub sbNumerToLetter_ExampleMacro()
    MsgBox "Column Name is : " & fnColumnToLetter_CellAdressReplace(105)
End Sub 

Output: Press ‘F5’ or click on Run button to run the above procedure. Please find the following output, which is shown in the following screen shot.

Column Number to Column Name1

Column Number to Column Name: By Referring Excel Range and Using SPLIT Function

Please find the following details about conversion from Column Number to Column Name in Excel VBA. Here we are referring Excel range and ‘Split’ function.

'By refering Excel Range and using SPLIT function
Function fnColumnToLetter_Split(ByVal intColumnNumber As Integer)
    fnColumnToLetter_Split = Split(Cells(1, intColumnNumber).Address, "$")(1)
End Function

Column Number to Column Name: Example and Output

Please find the below example. It will show you how to get column name from column number using Excel VBA. In the below example ‘fnColumnToLetter_Split’ is a function name, which is written above. And “15” represents the row number of ‘fnColumnToLetter_Split’ function parameter.

Sub sbNumerToLetter_ExampleMacro1()
    MsgBox "Column Name is : " & fnColumnToLetter_Split(15)
End Sub

Output: Press ‘F5’ or click on Run button to run the above procedure. Please find the following output, which is shown in the following screen shot.

Column Number to Column Name2

Column Number to Column Name: Using Chr Function and Do While loop

Please find the following details about conversion from Column Number to Column Name in Excel VBA. Here we are referring a ‘Chr’ function and Do While loop.

'Using chr function and Do  while loop
Function fnColumnToLetter_DoLoop(ByVal intColumnNumber As Integer) As String
    Dim bColDenom As Byte
    fnColumnToLetter_DoLoop = ""
    Do
        bColDenom = ((intColumnNumber - 1) Mod 26)
        fnColumnToLetter_DoLoop = Chr(bColDenom + 65) & fnColumnToLetter_DoLoop
        intColumnNumber = (intColumnNumber - bColDenom)  26
    Loop While intColumnNumber > 0
End Function

Column Number to Column Name: Example and Output

Please find the below example. It will show you how to get column name from column number using Excel VBA. In the below example ‘fnColumnToLetter_DoLoop’ is a function name, which is written above. And “10” represents the row number of ‘fnColumnToLetter_DoLoop’ function parameter.

Sub sbNumerToLetter_ExampleMacro2()
    MsgBox "Column Name is : " & fnColumnToLetter_DoLoop(10)
End Sub

Output: Press ‘F5’ or click on Run button to run the above procedure. Please find the following output, which is shown in the following screen shot.

Column Number to Column Name3

Column Number to Column Name: Using Recursive Approach

Please find the following details about conversion from Column Number to Column Name in Excel VBA. Here we are using recursive approach.

'Using Recursive Approach
Function fnColumnToLetter_Rec(intColumnNumber As Integer) As String
    If intColumnNumber > 26 Then
        fnColumnToLetter_Rec = fnColumnToLetter_Rec((intColumnNumber - ((intColumnNumber - 1) Mod 26)) / 26) + Chr((intColumnNumber - 1) Mod 26 + 65)
    Else
        fnColumnToLetter_Rec = Chr(intColumnNumber + 64)
    End If
End Function

Column Number to Column Name: Example and Output

Here is the example macro procedure. It will show you how to get column name from column number using Excel VBA. In the below example “fnColumnToLetter_Rec” is a function name, which is written above. And “4” represents the row number of “fnColumnToLetter_Rec” function parameter.

Sub sbNumerToLetter_ExampleMacro3()
    MsgBox "Column Name is : " & fnColumnToLetter_Rec(4)
End Sub

Output: Press ‘F5’ or click on Run button to run the above procedure. Please find the following output, which is shown in the following screen shot.

Column Number to Column Name

More about Column Name to Column Number Conversion

Here is the link to more about how to convert column name to column number using VBA in Excel.

Column Name to Number Conversion

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: Excel VBATags: , , Last Updated: June 17, 2022

5 Comments

  1. arun sharma September 14, 2015 at 2:33 PM

    ‘=IF(MID(ADDRESS(3,COLUMN(),,,),3,1)=”$”,MID(ADDRESS(3,COLUMN(),,,),2,1),MID(ADDRESS(3,COLUMN(),,,),2,2))

    Hi ,

    I dont know excel much , but i tried to solve this by using simple formulas.

    hope you find this OK.

    Thanks
    Arun

  2. Alan Elston October 25, 2015 at 5:34 AM

    Hi arun Sharma,
    I do not understand your formula and could not get it to work. Can you explain it?
    I have tried to explain a few VBA Codes here:
    http://www.excelforum.com/tips-and-tutorials/1108643-vba-column-letter-from-column-number-explained.html

    Alan

  3. Dale March 12, 2016 at 2:16 AM

    Another alternative: Application.ConvertFormula(Formula,FromReferenceStyle,ToReferenceStyle)

    For example, Application.ConvertFormula(“C215”,xlR1C1,xlA1) returns:$HG:$HG and
    Application.ConvertFormula(“$HG:$HG”,xla1,xlr1c1) returns: C215

    Cheers, Dale

  4. mskkiddo July 14, 2016 at 5:47 PM

    Place the column number which you need to know the character in the cell “B1”
    And use the below formula rest of any cells
    =LEFT(REPLACE(CELL(“address”,OFFSET(A1,1,B1-1)),1,1,”),FIND(“$”,REPLACE(CELL(“address”,OFFSET(A1,1,B1-1)),1,1,”))-1)

  5. Akhtar January 11, 2023 at 3:54 AM

    It’s Good
    and help full

Leave A Comment