Excel Column To Number

Excel Column to Number helps users to identify the column name or column letter in a dataset, and find the column number in the worksheet. Since Excel has 16,384 columns, represented by alphabetic characters in Excel, it is challenging to find the required column name.

We have an Excel inbuilt function called COLUMN in excel, which can tell the exact column number we are in right now or find the column number of the supplied argument.

For example, enter the formula =COLUMN() in cell D7, and press “Enter”. The output is 4, i.e., column D, the 4^th column, as shown below.

• The Excel Column to Number helps us retrieve the number of the column name of the selected cell or cell range.
• We can use the Column or the Column functions to find the column number of the cell.
• In the Column function, we will get a definite output if we enter an argument or not. However, in the Columns function, an argument, i.e., a cell reference or a cell range, is mandatory. Or else we will get an error.
• When we select a cell range, we get different outputs for different functions, namely,
  • The Column Function numbers the cell range, as shown below.

• The Columns Function gives the count of the selected columns of the cell range, as shown below.

We can find the Excel Column to Number using the Excel Column function in various ways.

One way is by directly entering the inbuilt formula in the worksheet as follows:

• Select an empty cell for which we want to find the column number.
• Type =COLUMN() in the cell. [Alternatively, we can type =C or =Col and select the COLUMN function from the Excel suggestions, as shown below.

• Press the “Enter” key to get the column letter’s number.

This article must help understand Excel Column to Number with its formulas and examples. You can download the template here to use it instantly.

We will consider some examples to convert an Excel Column to Number.

Example #1

Example #2

We can get the column number of the different cells by using the COLUMN function in Excel.

The steps to know the column number of cell CP5 are,

• We can write the COLUMN function, and pass the specified cell value in any cell. Therefore, in cell D6, enter the formula =Column(CP5), as shown below.

• Then, press the “Enter” key. It will return the column number of CP5 as 94, as shown below.

We have applied the COLUMN formula in cell D6, and passed the argument as CP5, i.e., cell reference of CP5 cell. However, unlike normal cell reference, it will not return the value in the cell CP5. Rather, it will return the column number of CP5.

So, the column number of the cell CP5 is 94.

Example #3

We can get how many columns are selected in the range by using the COLUMNS function in Excel.

We have learned how to get the current cell column number and specified cell column number in Excel. But, how do you tell how many columns are selected in the range?

We have another built-in function called the COLUMNS function in excel, which can return the number of columns selected in the formula range.

The steps to know how many columns are from the range C5 to N5 are,

• We can open the formula COLUMNS in cell B2, and select the range C5 to N5.

• Press the “Enter” key to get the desired result.

So totally, we have selected 12 columns in the range C5 to N5.

In this way, by using the COLUMN and COLUMNS functions in Excel, we can get the two different kinds of results, which can help us calculate or identify the exact column when dealing with huge datasets.

Example #4

We can change the cell reference form to R1C1 references in Excel.

By default, we have cell references, all the rows are represented numerically, and all the columns are represented alphabetically.

It is the usual spreadsheet structure we are familiar with. The cell reference is started with the column alphabet and then followed by row numbers.

As we learned earlier in the article, we need to use the COLUMN function to get the column number. How about changing the column headers from the alphabet to numbers like our row headers, as shown in the image below?

It is called ROW-COLUMN reference in Excel. Now, take a look at the below image and the reference type.

Unlike our standard cell reference, reference starts with a row number followed by a column number, not an alphabet.

The steps to change it to the R1C1 reference style are as follows:

• We must first go to the “File” and “Options.”

• Next, go to “Formulas” under “Options.”

• Working with “Formulas,” select the checkbox “R1C1 reference style”, and click “OK.”

Once we click “OK,” cells will change to R1C1 references.

• The R1C1 cell reference is the rarely followed cell reference in Excel. As a result, we may get confused easily at the start.
• We see the column alphabet first and the row number next in normal cell references. But in R1C1 cell references, the row number will come first and the column number.
• The COLUMN function can return the current column number and the supplied column number.
• The R1C1 cell reference makes it easy to find the column number easily.

This article has been a guide to Excel Column to Number. Here we find column number using column letter, COLUMN(), COLUMNS(), examples & downloadable excel template. You may learn more about Excel from the following articles: -

• Excel Rows Function
• Excel Rows vs. Columns
• Excel Rows and Columns
• Concatenate Excel Columns