Abrar-ur-Rahman Niloy
While working on a large dataset or a series of datasets where those takes up so many rows in a spreadsheet, it is a very common situation where we need to find the column number we are in. Such as we want the number “235” to know instead of column “IA”. If that is the case, you have come to the right place. This tutorial will show you how to convert column letter to number chart in Excel, which you can also use to find out column numbers of random cells.
Download Practice Workbook
Download the workbook with all the formulas and VBA code from the link below. Try downloading and practicing while you go through the steps.
4 Easy Ways to Convert Column Letter to Number Chart in Excel
We are mainly going to use formulas combining different functions that will help us determine the column numbers. We can use different combinations for that. Also, we can create our customized function using VBA that can convert column letter to number chart in Excel.
To demonstrate every method, we are going to convert the column letter to a number from the following chart in Excel.
1. Combining COLUMN and INDIRECT Functions
In the first method, we are going to use a formula with the combination of the COLUMN and INDIRECT functions. The COLUMN function takes a reference cell as an argument and returns the column number. This is the main function that will help us convert column letter to number chart in Excel. Meanwhile, the INDIRECT function is generally applied to store a cell reference and then use the reference value in other functions to perform multiple operations.
Follow these steps to see how you can use the formula for this dataset.
Steps:
- First of all, select cell C5.
- Then write down the following formula in the cell.
=COLUMN(INDIRECT(B5&"1"))
- Now press Enter. You will have the column number of the first letter.
- After that, select the cell again. Then click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cell.
Now you will have all column letters converted to a number chart in Excel.
🔍 Breakdown of the Formula
COLUMN(INDIRECT(B5&”1″))
👉 INDIRECT(“B5”) takes the cell value of B5 and returns A.
👉 While INDIRECT(B5&1) takes the previous output and concatenates 1 to the string to make it A1.
👉 Finally, COLUMN(INDIRECT(B5&”1″)) returns the column number of cell A1, which is 1.
Read More: How to Convert Column Number to Letter in Excel (3 Ways)
2. Applying MATCH, ADDRESS and COLUMN Functions
We can also combine the MATCH and ADDRESS functions with the previously used COLUMN function to achieve the same result. The MATCH function takes the lookup value and the lookup array as its primary arguments and match type as optional arguments. It returns the relative position of the match in the array. While the ADDRESS function creates cell reference as text from the given row number and column number. The function takes these two numbers as its arguments. And as mentioned earlier, the COLUMN function takes a cell reference as an argument and returns the column number the cell is in.
Follow these steps to see you you can use the combination to convert excel column letter to number in a chart with this dataset.
Steps:
- First of all, select cell C5.
- Then write down the following formula.
=MATCH(B5&"1", ADDRESS(1, COLUMN($1:$1), 4), 0)
- Now press Enter. At this instant, you will have the first letter converted into a number in your Excel spreadsheet.
- Next, select the cell again and then click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cell.
🔍 Breakdown of the Formula
MATCH(B5&”1″, ADDRESS(1, COLUMN($1:$1), 4), 0)
👉 Firstly, COLUMN($1:$1) indicates all the columns in the first row.
👉 Secondly, ADDRESS(1, COLUMN($1:$1), 4) creates an array of texts with the first row and all the columns such as {“A1”, “B1”,…..} till the end.
👉 Finally, MATCH(B5&”1″, ADDRESS(1, COLUMN($1:$1), 4), 0) matches the cell value from B5 converted into cell reference, i.e. A1 and returns where it matches in the previous array. 0 indicates an exact match.
Read More: How to Reference Cell by Row and Column Number in Excel (4 Methods)
Similar Readings
- How to Find Column Index Number in Excel (2 Methods)
- VBA to Use Range Based on Column Number in Excel (4 Methods)
- How to Count Columns until Value Reached in Excel
- Excel VBA: Set Range by Row and Column Number (3 Examples)
- How to Transpose Multiple Columns to Rows in Excel
3. Using Column Function
Instead of combining the COLUMN function with different functions to create a formula, the function itself can convert column names into numbers. But keep in mind that, It has to take the column name directly as an argument. In the previous methods, we were doing that indirectly anyway. But those were suitable for the dataset in question.
To use the COLUMN function solely to convert column names to numbers, our dataset must look something like this instead.
Follow these steps to convert every letter to a number in the chart in Microsoft Excel.
Steps:
- First of all, select cell C5.
- Then write down the following formula.
=COLUMN(A1)
- And press Enter. Now you will have the column number of the first entry.
- Similarly, select cell C6 and write down the following formula manually.
=COLUMN(F5)
- And then press Enter.
- In a similar way, fill out the rest of the cells manually to find out numbers for the rest of the chart.
This is another way you can convert column letter to a number chart in Excel.
Read More: [Fixed] Excel Column Numbers Instead of Letters (2 Solutions)
4. Applying User-Defined Function
As you have seen from the previous methods, you can’t use a single function to directly convert letter to number chart in Excel. You either have to use formulas using a combination of functions, or use cell reference (the whole cell name) in the column property of a range. This is where the Microsoft Visual Basic for Applications (VBA) comes in handy.
With the help of VBA, we can easily define and create our own custom functions that can do certain jobs for us. And creating one that can convert column letter to number chart in Excel is no exception. But first, you need to enable the Developer tab to use the code.
Once you have the Developer tab showing on your ribbon, follow these steps to create your customized function that can convert letter to number chart in Excel.
Steps:
- First of all, go to the Developer tab on your ribbon.
- Then select Visual Basic from the Code group.
- As a result, the VBA window will open up separately. Now click on the Insert tab.
- And select Module from the drop-down list.
- Thus a new module will open up in the VBA window. Now select the module and write down the following code in it.
Public Function LetterToNumber(letter As String) As Long
LetterToNumber = Columns(letter).Column
End Function- Once you are done, close the VBA window.
- Then select cell C5 in the Excel spreadsheet.
- After that, write down the following formula in it.
=LetterToNumber(B5)
- Now press Enter. The first letter will be converted to a number.
- Next, select the cell again and click and drag the fill handle icon bar to the end of the column to replicate the formula for the rest of the cells.
This is how you can use VBA to define a custom function that can convert column letter to number chart in Excel.
Read More: How to Find Column Number Based on Value in Excel
💬 Things to Remember
👉 The COLUMN function can only take the whole-cell reference as an argument. Entering just column letters will result in an error.
👉 If you only use the COLUMN function to convert a letter to a number, add a random number at the end of the letter in the argument. As all row numbers eventually will be in the same column number, it won’t mess up the result. Instead, it will help to eliminate the error.
👉 Using the COLUMN function without any argument will only return the column number of the cell you are entering the formula into.
👉 Although only defining a function using VBA is relatively safe, it is always a good idea to save your worksheet before working with VBA codes.
Conclusion
These were all the methods you can use to convert Excel column letter to number chart. Hopefully, you can convert your own chart easily from letters to numbers now. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know below.
For more guides like this, visit ExcelDemy.com.
Similar Readings
- How to Change Column Name from ABC to 1 2 3 in Excel
- Excel VBA: Get Row and Column Number from Cell Address (4 Methods)
- How to Return Column Number of Match in Excel (5 Useful Ways)
- VBA to Convert Column Number to Letter in Excel (3 Methods)
- How to Skip Every Other Column Using Excel Formula (3 Methods)
