In this post we will see how to write a function in excel to convert a column number to its alphabetical letter e.g. column number 100 is actually column CV.
I am going to explain how we could write a function in excel VBA to convert column number to its alphabetical equivalence and since we don’t have any excel function to do this, we will write this function in excel VBA that will convert column number to column letter.
Step 1
We will create a macro in excel to auto insert an image inside a shape.
Open VB editor from Developer tab in excel as shown in the figure.
Step 2
Insert a module in which we will write a small macro to write a function to get the column letter from column number.
Step 3
Now double click the newly created module to open that and paste the below code in the module.
Option Explicit
Function Col_Letter(lngCol As Long) As String
Dim vArr
vArr = Split(Cells(1, lngCol).Address(True, False), “$”)
Col_Letter = vArr(0)
End Function
This program defines a function that will take column number as input and will return column letter.
Step 4
Run the program by pressing F5 or by play button.
Step 5
You can see that after the program is run, we have created a user defined function that we can use in excel sheet just like any other excel function to get column letter from column number.
In excel sheet type the function name after “=” sign and you will observe that we are seeing the formula that we just created.
Step 6
I have put column no equal to 100 in the function and you can see in the picture below that we have got column letter equivalent to column number in excel.
Hope this helped.
