Excel Function To Convert Column Number To Alphabetical Letter

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.

open VB editor to write a function that will convert excel column number to column letter

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.

Insert a new module in excel VBA

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.

Write the code in VB to convert column to excel 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.

Use user defined function in excel to test the function

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.

excel function has converted the column number to column letter

Hope this helped.

Share The Knowledge

Random Posts

Leave a Reply