Column Letter to Number Converter in Excel (5 Easy Examples)

While working in Excel, sometimes we need to determine the current or specific column number. It may be easy for a smaller worksheet to find out the column number easily. But when someone is working on a larger worksheet with lots of data and formulas, then it is time-consuming to figure out the column letter manually. In this article, we will show you some solutions for column letter to number converter in Excel.


Download Practice Workbook

You can download the free Excel workbook from here and practice on your own.


5 Useful Examples of Column Letter to Number Converter in Excel

For this article, we will use the data set in the following. Here, you will see the different column headings of an Excel worksheet. To complete the procedure, we will demonstrate five different approaches. Here, we will use the COLUMN function and will combine the COLUMN function and the INDIRECT function, apply a non-volatile formula, alter the reference style, and lastly, apply Visual Basics for Application (VBA).

Suitable Examples of Column Letter to Number Converter in Excel

1. Using COLUMN Function

In our first approach, we will use the COLUMN function of Excel to make a column letter to number converter. Go through the following steps to understand the process.

Step 1:

  • First of all, we will use the following data set for our working purpose.
  • Here, we will use the COLUMN function for two conditions.
  • First one is for finding out the column number of a specific cell and the second one is for the current working cell.

Suitable Examples of Column Letter to Number Converter in Excel

Step 2:

  • Secondly, to find out the column number of a specific cell, use the following formula of the COLUMN function mentioning the cell.
  • For example, here we will use cell B5.
=COLUMN(B5)

Suitable Examples of Column Letter to Number Converter in Excel

  • Then, after pressing Enter, you will see the column number of the mentioned cell, which is column number 2 for cell B5.

Suitable Examples of Column Letter to Number Converter in Excel

Step 3:

  • Next, to determine the column number of the current cell on which you are working, use the following formula of the COLUMN function.
=COLUMN()

Suitable Examples of Column Letter to Number Converter in Excel

  • After pressing Enter, you will see the column number of the current working cell.
  • In our example, which is number 3 for cell C6.

Suitable Examples of Column Letter to Number Converter in Excel


2. Combining COLUMN and INDIRECT Functions

In our first approach, you saw the use of a single function to make a column letter to number converter. In the second method, we will combine the COLUMN function with the INDIRECT function. To understand the whole procedure, follow the following steps.

Step 1:

  • First of all, take the following data set for applying the formulas.
  • Here, in column B, we will select some column headers of an Excel worksheet randomly, and in  column C  we will find out their column numbers.

Suitable Examples of Column Letter to Number Converter in Excel

Step 2:

  • Secondly, write the following formula in cell C5, which is the combination of the COLUMN function and the INDIRECT function.
=COLUMN(INDIRECT(B5&"1"))
  • We will input this formula in cell C5 to determine the column number of column A which is in cell B5.

Suitable Examples of Column Letter to Number Converter in Excel

Step 3:

  • Then, press Enter and you will see the column number of column A in cell C5 which is 1.
  • Finally, use AutoFill to drag down the formula for other cells.
  • Consequently, you will see all the column numbers in their respective cells.

Suitable Examples of Column Letter to Number Converter in Excel

Formula Breakdown
  • First of all, INDIRECT(“B5”) means it will take the cell value of B5 which is A.
  • Then, INDIRECT(B5&1) receives the previous output and connects 1 to the previous code’s output to make it A1.
  • Finally, COLUMN(INDIRECT(B5&”1″)) will show the column number of cell A1, which is 1.

3. Applying Non-Volatile Formula

If you are working with a huge data set in Excel, then using the second method would be time-consuming. Because the INDIRECT function is a volatile function, which makes the workbook slower. In that case, you can use the MATCH function and the ADDRESS function with the COLUMN function as they are both volatile. See the following steps to know more about the process.

Step 1:

  • First of all, in cell C5 write down the following formula.
=MATCH(B5&"1", ADDRESS(1, COLUMN($1:$1), 4), 0)

Suitable Examples of Column Letter to Number Converter in Excel

Step 2:

  • Then, press Enter and you will see the column number of the respected column.
  • Finally, drag the formula to the lower cells of the column to get the column numbers for all the column letters.

Suitable Examples of Column Letter to Number Converter in Excel

Formula Breakdown
  • First of all, COLUMN($1:$1) represents all the columns of the first row in the worksheet.
  • Secondly, ADDRESS(1, COLUMN($1:$1), 4) will make an arrangement of texts with the first row and all the columns of the worksheet, for example, “A1”, “B1” etc. until the worksheet ends.
  • Finally, the formula MATCH(B5&”1″, ADDRESS(1, COLUMN($1:$1), 4), 0) matches the cell value given in cell B5 converted into cell reference, which is A1 and returns where it matches in the previous arrangement.
  • At last, 0 is given to find an exact match in the formula.

4. Altering Reference Style

This process of converting column letters to column numbers in Excel is relatively easy and simple. To know more about this process, follow the below-given steps.

Step 1:

  • First of all, see the following data set.
  • Here, the reference in the formula of cell C5 shows the cell number in column letter which is B5.
  • We will convert the column letter to number by altering the reference cycle.

Suitable Examples of Column Letter to Number Converter in Excel

Step 2:

  • To do that, first press the File tab on the ribbon.

Suitable Examples of Column Letter to Number Converter in Excel

  • Then, select the Options command.

Suitable Examples of Column Letter to Number Converter in Excel

Step 3:

  • Thirdly, after choosing the command, you will find a dialogue box naming Excel Options.
  • In there, from the Formulas tab, mark the option named R1C1 reference style.
  • Lastly, press OK.

Suitable Examples of Column Letter to Number Converter in Excel

Step 4:

  • Finally, you will see that your worksheet will have the R1C1 reference style, in which the columns of the worksheet will transform into numeric values.

Suitable Examples of Column Letter to Number Converter in Excel


5. Applying VBA to Convert Column Letter to Number in Excel

In our last method, we will apply Visual Basics for Application (VBA). By applying VBA, we can determine the column number after inserting the column letter in the code. Go through the following steps to understand the procedure.

Step 1:

  • Firstly, we will use the following data set to apply VBA.

Sample Data Set

Step 2:

  • Then, choose the Visual Basic command from the Code group in the Developer tab.

Sample Data Set

Step 3:

  • Thirdly, the box for inserting the code will open after choosing the Visaul Basic command.
  • From the box, choose the Module command in the Insert tab.

Sample Data Set

Step 4:

  • Fourthly, copy the following VBA Code and paste it into the Module.
  • Here, we will insert the column letter XFD to find out its column number.
Sub Excel_Column_Letter_to_Number_Converter()
'Convert a specific Column Letter into it's corresponding Numeric Reference
Dim ColumnNumber As Long
Dim ColumnLetter As String
'Call the Column Letter
ColumnLetter = "XFD"
'Convert To the Column Number
ColumnNumber = Range(ColumnLetter & 1).Column
'Show the Final Result
MsgBox "Column " & ColumnLetter & " = " & ColumnNumber
End Sub

Sample Data Set

  • Then save the above code and press the play button to activate it.

Sample Data Set

Step 5:

  • Finally, after playing the code you will see the corresponding column number of XFD which is 16384.

Sample Data Set

  • You can run this code for other column letters as well and find their respective column number.

Sample Data Set


Conclusion

That’s the end of this article. I hope you find this article helpful. After reading this article, you will be able to create an Excel column letter to number converter by using any of the methods. Please share any further queries or recommendations with us in the comments section below.

Araf

Araf

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo