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.
Column Letter to Number Converter in Excel: 5 Easy Examples
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 Basic for Application (VBA).
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.
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)
- Then, after pressing Enter, you will see the column number of the mentioned cell, which is column number 2 for cell B5.
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()
- After pressing Enter, you will see the column number of the current working cell.
- In our example, which is number 3 for cell C6.
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.
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.
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.
- 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.
Read More: How to Change Column Name from ABC to 1 2 3 in Excel
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)
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.
- 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.
Read More: How to Convert Column Number to Letter in Excel
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.
Step 2:
- To do that, first press the File tab on the ribbon.
- Then, select the Options command.
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.
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.
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.
Step 2:
- Then, choose the Visual Basic command from the Code group in the Developer tab.
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.
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
- Then save the above code and press the play button to activate it.
Step 5:
- Finally, after playing the code you will see the corresponding column number of XFD which is 16384.
- You can run this code for other column letters as well and find their respective column number.
Read More: Find Value in Row and Return Column Number Using VBA in Excel
Download Practice Workbook
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.
Related Articles
- How to Return Column Number of Match in Excel
- How to Find Column Number Based on Value in Excel
- How to Find Column Index Number in Excel
- VBA to Convert Column Number to Letter in Excel
- How to Use VBA Range Based on Column Number in Excel
<< Go Back to Column Number | Columns in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
