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
, you will see the column number of the mentioned cell, which is column number*Enter*for cell*2*.*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
, you will see the column number of the current working cell.*Enter* - In our example, which is number
for cell*3*.*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
, we will select some column headers of an*B**Excel*worksheet randomly, and in columnwe will find out their column numbers.*C*

**Step 2:**

- Secondly, write the following formula in cell
, which is the combination of*C5**the**COLUMN**function*and*the**INDIRECT**function*.

`=COLUMN(INDIRECT(B5&"1"))`

- We will input this formula in cell
to determine the column number of column*C5*which is in cell*A*.*B5*

**Step 3:**

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

**Formula Breakdown**

- First of all,
**INDIRECT(“B5”)**means it will take the cell value ofwhich is**B5**.*A* - Then,
**INDIRECT(B5&1)**receives the previous output and connectsto the previous code’s output to make it**1**.**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
write down the following formula.*C5*

`=MATCH(B5&"1", ADDRESS(1, COLUMN($1:$1), 4), 0)`

**Step 2:**

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

**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**” etc. until the worksheet ends.**B1** - 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 isand returns where it matches in the previous arrangement.**A1** - 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
shows the cell number in column letter which is*C5*.*B5* - We will convert the column letter to number by altering the reference cycle.

**Step 2:**

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

- Then, select the
command.*Options*

**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
, in which the columns of the worksheet will transform into numeric values.*R1C1 reference style*

### 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
command from the*Visual Basic*group in the*Code*tab.*Developer*

**Step 3:**

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

**Step 4:**

- Fourthly, copy the following
**VBA Code**and paste it into the.*Module* - Here, we will insert the column letter
to find out its column number.*XFD*

```
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
which is*XFD*.*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**