How to Convert Column Letter to Number Chart in Excel (4 Ways)

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"))

excel column letter to number chart

  • Now press Enter. You will have the column number of the first letter.

excel column letter to number chart

  • 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)

excel column letter to number chart

  • Now press Enter. At this instant, you will have the first letter converted into a number in your Excel spreadsheet.

excel column letter to number chart

  • 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


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)

excel column letter to number chart

  • And press Enter. Now you will have the column number of the first entry.

excel column letter to number chart

  • Similarly, select cell C6 and write down the following formula manually.

=COLUMN(F5)

excel column letter to number chart

  • 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.

excel column letter to number 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.

excel column letter to number chart

  • 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)

excel column letter to number chart

  • 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.

excel column letter to number chart

This is how you can use VBA to define a custom function that can convert column letter to number chart 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

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo