Convert Column Numbers to Column Letters

Navigating and iterating over values in a spreadsheet is one of the most common things a VBA programmer has to do. And while doing this you will find needs to refer columns by their letter or by their number. However there is no inbuilt way of switching between the two in Excel.

This function takes a column number and converts it to a column letter. So the number 3 will be converted to the letter “C” and so on.

To use it, pass the col_num argument the number you want to convert to a letter.

If you pass a number that cant be converted to letter (a decimal number, a negative number or a number over the maximum number of columns in Excel) it will return an empty string.

Function ColNumToLet(col_num As Integer) As String
    
    ' Purpose -->       Converts a column number to its equivalent letter
    ' Arguments -->     col_num (Integer)
    '                   - The number to be converted into a column letter(s).
    ' Returns -->       (String)
    '                   - Returns a string containing the letter(s) if found
    '                   - Returns an empty string if there is any kind of error
    ' Created by -->    Carl Nixon, Excel-Expert.co.uk

    ' Set up variables
    ' ================
    
    ' Holds the calculated letter(s)
    Dim column_letter As String
    
    ' Default the calculated letter(s) to a blank string
    column_letter = ""

    ' Check safe to run
    ' =================
    
    ' If number is outside the permitted number of columns then exit
    If col_num < 0 Or col_num > 16384 Then GoTo Finished
    
    ' If column number is not a whole number then exit
    If Int(col_num) <> col_num Then GoTo Finished

    ' Convert number to letter(s)
    ' ===========================
    
    ' Build a range using the column number then split it
    ' back out so the column letter can be extracted
    column_letter = Split(Cells(1, col_num).Address, "$")(1)
    
    ' Finish up
    ' =========
Finished:

    ' Return the calculated column letter
    ColNumToLet = column_letter

End Function

All of the code featured on this site is available to download on my GitGub page. Click here to visit it.

Carl Nixon

Carl Nixon is a VBA developer with 20 years experience, 10 years as a freelancer. He has worked for clients around the world, ranging in size from micro businesses to global brands. In his 20 years developing VBA projects he has helped businesses to automate and simplify processes, resulting in massive time and money savings. He is also the author of 3 top selling books on Excel. Visit my LinkedIn profile or my GitHub page

Leave a Reply

Your email address will not be published. Required fields are marked *