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.