Sometimes, you need to know the column number or the letter associated with a cell in Google Sheets. Luckily, Google Sheets provides us with simple ways to achieve this.
After going through this tutorial, you’ll be able to easily generate column numbers or column letters.
Getting Column Index or Column Numbers in Google Sheets
We’re fortunate here because Google Sheets provides us with a handy function, COLUMN(), to obtain the column index.
Simply enter edit mode for a cell (by pressing F2) and type “=COLUMN(Cell Reference)”. The cell reference is simply the cell you want to find the column number for.
Here’s an example. Let’s find the column index of cell C4.
The simple formula used to achieve this is “=COLUMN(C4)”, which results in 3, indicating that it’s the third column from the left.
Getting the Column Letter in Google Sheets
If in the previous example, you also want to know the column letter, ‘C’, along with the column index 3, then this section is for you.
Unfortunately, there’s no direct formula to do it, so we’ll have to improvise.
Method 1: Using RegexExtract and Address
We’ll reuse the previous example. To simplify things, we’ll proceed step by step.
Step 1: The first step is to get the column number, which we’ve already looked at.
Step 2: The second step is to use the ADDRESS function. The ADDRESS function is supposed to return the cell reference of the given 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 simply extracts all the letters from the output of the Address() function. The argument [A-Z]+ implies that we want to extract any type of letters from the alphabet.
The final result after step 3 is ‘C’, which is what we were looking for.
Method 2: Using Substitute and Address
This is an almost similar approach to the previous one. Steps 1 and 2 will remain the same.
Step 3: Add the argument 4 to ADDRESS(). This will remove the unnecessary $$ signs. The new result is C1 instead of $C$1.
Step 4: Use Substitute to replace 1 with nothing (“”). This removes the 1 and replaces it with “”, so only ‘C’ remains, which is what we were looking for.
Note that we have created multiple formulas with dependencies on different cells. If you want to do it in one go, use the following formula:
=SUBSTITUTE(ADDRESS(1,COLUMN(C4),4),"1","")
That’s all on this topic. If you have any further questions, feel free to leave a comment below. Continue exploring Crawlan.com for more useful information :)
