Get Column Index & Column Letter In Google Sheets

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.

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.

COLUMN Function in Google Sheet

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.

ADDRESS Function in Google Sheet

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.

Getting Column Letter in Google Sheet

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.

Getting Column Letter in Google Sheet

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.

Getting Column Letter in Google Sheet

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 🙂

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

Liked the content?? Then share the word!!!

Rishav

I love Google Sheets and heavily rely upon it on a day to day basis. SheetsInfo is my attempt to share my learning with all of you. Hope you find the articles easy to read and simple to understand.

Leave a Reply