At times you may require to know the Column Number or Column Letter of an associated cell. Thankfully Google Sheets has provided us with simple ways to get this done.
You’ll be easily able to generate column numbers or column letters after completing this tutorial.
Table of Contents
Getting Column Index or Column Numbers in Google Sheet
We stand lucky here as Google Sheet provides us with a handy function, COLUMN() to get the column index.
Simply enter the editing mode of any cell(by pressing F2) and type “= COLUMN(Cell Reference)”. Cell reference is nothing but the cell whose column number you want to find.
Lets see the example below. We want to find the Column number/index of Cell C4.
The simple formula used to get to it was “=COLUMN(C4)” which gives the output 3, indicating it’s the third column from the left.
Getting Column Letter in Google Sheets
If in the previous example, you also wished to find out the Column Letter, ‘C’ along with the Column index 3, then this section is for you.
Unfortunately there’s no direct formula to do this, hence we’ll improvise.
Method 1: Using RegexExtract and Address
We’ll reuse the prior example. To keep things simple we’ll go one step at a time.
Step 1: First Step is to get the column number which we have already looked at before.
Step 2: Second step is to use the ADDRESS function. ADDRESS Function is meant return the cell reference of the supplied row and column index. For Example ADDRESS(1,1) will return A1, ADDRESS(2,1) will return B1 and so on.
Step 3: Now we apply Regexextract. It just extracts out all the alphabets from the the output of Address() Function. Argument [A-Z]+ implies that we want to extract any type of Alphabets.
The Final result post Step 3 is ‘C’ which is what we were looking for.
Method 2: Using Substitute and Address
This is a nearly similar approach to the previous one. Step 1 & 2 will remain the same.
Step 3: Add argument of 4 to ADDRESS(). It will Trim-off the unnecessary $$ signs. New ouput is C1 and not $C$1.
Step 4: Use Substitute to replace 1 with null(“”). What this does is removes 1 and replaces it by “”. Hence all that is left is ‘C’ which is all that we were looking for.
Note that we have created multiple formula with mulitple dependencies on different cells. In case you want to do it at one go then use the below formula.
=SUBSTITUTE(ADDRESS(1,COLUMN(C4),4),"1","")That’s it on this topic. If you still have any questions, then drop a comment below. Keep browsing SheetsInfo for more such useful information 🙂
