Column Number to Column Name

Home/Excel VBA/Column Number to Column Name

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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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

LIMITED TIME OFFER
By |June 28th, 2015|Excel VBA|4 Comments

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

4 Comments

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

    =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 - Reply

    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:

    Alan

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

    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 - Reply

    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)

Leave A Comment


Related pages


vbscript to delete files in a foldervba programming in excel examplesvba code to close a formexcel vba saveas fileformatexcel macro to merge cellsturn on developer tab in excel 2010excel macros basicsmodules in excel vbahow to unhide sheets in excelaccess vba odbc connection string sql serverlearn vlookup in excel 2007dynamic graph in excelmsgbox in accessexecute sql query from excelmicrosoft excel chart wizardcreate text file vbaexcel shortcut chartvba repeat untilsql interview questions and answers for experienced pdfexcel vba message boxxcelsius tutorial for beginnersvba for next stepexcel vba select columnvba excel copy rangetask planner excel templatecreating a pie chart in excel 2007excel vba do until loopexcel checkbox valuecomplex macros in excelremove duplicates formulaexcel formula sum ifvba insert rowsexcel macro recordingaccess vba odbc connection string sql serverfileexists vbaexcel vba functionoptimisation in excelexcel project gantt chart templatesqlconnection examplemicrosoft excel substitutevba excel savevba import data from another excel fileexcel hyperlink to pdfswot analysis excel templateshow merged cells in excelhow to learn vba excelunhide hidden columns in exceluserform designexcel vba and macroshow to use lookup function in excel 2010vba copy to clipboardformula for finding average in excelsaving macros in excelexcel paste special transposeexcel insert row macrovba active cellloops in excel vbaredim excelvba insert row excelaccess macro run queryadvanced vba tutorial pdfvlookup stepsapplication calculation xlcalculationmanuallock excel worksheethow to find duplicates in an excel columnlock worksheet excelexcel cell contains textlistbox vba accessexcel gotodelete blanksmacro in excel with exampleuseful excel add insdelete blank cells excelexcel spreadsheet vbahow to do vlookup in excel 2007sumif and or functionalphabetical ascending order example