Convert Column Letters to Column Numbers

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 letter and converts it to an Integer number.

To use it, pass the col_let argument the letter or letters you want to convert (e.g. “A”, “AI”, “ABC”) you want to convert. The letter “C” will be converted to the number 3 and so on. If you accidentally enter lower case characters, the function will automatically convert them to upper case before proceeding to convert them.

If you pass a string that cant be converted or causes an error, the function will return 0, which is not a valid column number. This is so the programming that called the function can pick up there was an issue.

Function ColLetToNum(col_let As String) As Integer
    
    ' Purpose -->       Converts a column letter to its equivalent number
    ' Arguments -->     col_let (String)
    '                   - The letter(s) to be converted into a number
    ' Returns -->       (Integer)
    '                   - Returns the column number if found
    '                   - Returns 0 if the number isn't found (as an error capture)
    ' Created by -->    Carl Nixon, Excel-Expert.co.uk
    
    ' Set up variables
    ' ================
    
    ' Holds the calculated column number
    Dim column_number As Integer
    
    ' Set to 0 as a default
    column_number = 0
    
    ' Convert passed string to upper case and trim it to make it safer
    col_let = Trim(UCase(col_let))
    
    ' Check it is safe to run
    ' =======================
    
    ' If there is no string to work on then finish
    If col_let = "" Then GoTo Finished

    ' Convert the letter(s) to a number
    ' =================================
    
    ' Build a range using the column letter then split it
    ' back out so the column number can be extracted
    column_number = Range(col_let & "1").Column
    
    ' Finish up
    ' =========
Finished:

    ' Return the calculated column number
    ColLetToNum = column_number

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 *