How to Find Column Letters in Google Sheets [Easy Guide]

In this article, we will learn how to find column letters in google sheets. What are column letters? We have indexes written in rows and columns for our ease, we have numbers written for rows to quickly pick the row numbers, and alphabets are written on the column headers to quickly pick up the cell address (row + column) to use in formulas or cell references. This is by default we have in our google sheets file.

But getting the column letter in a cell? It’s something different and not very common, but somehow, we can encounter a situation where we do need to refer to a cell. So, in this article, we will talk about some formulas or functions in google sheets to find out the column letters, or column numbers in google sheets.

Use cases of finding Column Letters in Google Sheets

Sometimes we need to collect the cell references in the cells to get used in other formulas or functions. We might need to use them as references or for some kind of course material for google sheets beginners. Therefore, today we are going to learn how to find column letters in google sheets. The first thing is to know that the column letters are nothing but the cell reference. You must have faced a small problem when using a formula or function that required the row and column number to pass and you are sitting and counting the alphabets to know the number of columns on let us say column F.

This tutorial will also solve this problem. You can make easy references for columns in numbers or alphabets. We will learn multiple formulas to get things done. Let’s move forward to learn how to find column letters in google sheets.

How to Find Column Letters in Google Sheets

In this section, we will step by step learn how to find column letters in google sheets. Firstly, the default environment of the google sheets file automatically shows the headers for the rows and columns. Alphabets for columns and numbers for rows like you can see in the below screenshot

Find Column Letters in Google Sheets – Get Full Cell Address

In this first section, we will learn how to find column letters in google sheets and will extract the full cell address of any cell in the worksheet. For this, we need to use a function. It is an ADDRESS function that will return to us the cell address of a specified cell.

Let’s see the syntax of the function

Syntax

ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet])

Here,

row: it is the row number to be specified, such as 1,2,3,

column, It is the column number to be specific, such as 1,2,3 (not A, B, C)

absolute_relative_mode: it is an optional parameter to specify in which notation you want the function to return the cell address.

There are four possible values for this parameter

1. absolute row absolute column ($A$1)
2. absolute row and relative column (A$1)
3. relative row and absolute column ($A1)
4. relative row and relative column (A1)

use_a1_notation: It is also an optional parameter to specify the style notation. True means return A1 style notation and false means return R1C1 style notation.

R1C1 = Row1Column1 notation (not used in google sheets normally)

sheet: It’s also an optional parameter to define your sheet name from which you want to extract this cell address. It is 99% skipped.

Now knowing the syntax. I believe you have got an idea of how to find column letters in google sheets. Let’s see some practical examples

Step 1

Write Address Function in any cell

Step 2

Pass the first argument (row number)

Step 3

Pass the second argument (column number)

Step 4

Pass optional argument if you want to

Step 5

Press Enter and you have got the cell address.

Step 6 (Optional)

We can return the cell address in all possible notations and styles, let’s see how can we do this by tweaking the third (optional) argument of the ADDRESS function

6.1

Absolute row Absolute column ($A$1)

6.2

Absolute row and Relative column (A$1)

6.3

Relative row and Absolute column ($A1)

6.4

Relative row and Relative column (A1)

Step 7

We have another option to return R1C1 style notation by tweaking the fourth argument of the ADDRESS function

7.1

A1 Notation

7.2

R1C1 Notation

This is how we can use so many arguments optionally to get the desired and the most specific result as our need.

But our problem statement is not solved completely, as we needed to find the column letters in a cell, not the entire address of the cell.

Till now, we have seen how you can get the cell addresses, but we need the column letters, not the entire cell address, right so what can we do now? We are now moving forward to further break the cell address into a single digit (Column Letter only).

Find Column Letters in Google Sheets – Column Letters from Address

We have already learned how to find a cell address using an address function, we can easily get the cell address in a cell in many available notations and style notations. Now we need to have only a column letter, so basically our goal is to remove the row number from A1 notation to get the column letter. Here, we can use a lot of methods to remove row numbers from a cell address. We have functions like LEFT, REPLACE, SUBSTITUTE, MID, and many more to remove the row number. Let’s see them practically.

Using SUBSTITUTE FUNCTION

First, let’s see the easiest way to get the column letter from a cell address.

I am using the SUBSTITUTE function here

The formula

=SUBSTITUTE(ADDRESS(3,10,4),3,””)

Explanation

SUBSTITUTE will replace the value 3 with ” ” an empty string so that it will be removed.

Using LEFT FUNCTION

Now let’s see another easy way to get the column letter from a cell address.

I am using the LEFT function here

The formula

=LEFT(ADDRESS(4,3,4),1)

Explanation

LEFT will cut out the right value for 1 time since the left side of the String will be remaining which is the column Letter

Using MID FUNCTION

Let’s see another way to get the column letter from a cell address.

This time, I am using the MID function

The formula

=MID(ADDRESS(4,5,4),1,1)

Explanation

LEFT will get the value from the address function’s result and will look for position 1, and return 1 number which means the last character will be removed.

Using REPLACE FUNCTION

This is yet another function to get the column letter from a cell address.

This time, I am using the REPLACE function

The formula

=REPLACE(ADDRESS(4,7,4),2,1,””)

Explanation

REPLACE will get the value from the address result, and will look for position 2, and length will be 1 and replace this with an empty string. So, we will ultimately get the column letter

This is how we have plenty of ways to get a column letter from the cell address. Cell address can be easily gotten from the address function and then we have multiple ways to extract column letters from it.

I hope you have understood all the functions and you find this article helpful.

Important Notes

Frequently Asked Questions

Why do we need cell addresses in cells?

With some formulas and functions in google sheets, we need to have the actual cell reference within the cell. We need to pass the values of the cell addresses as the cell reference not as a value. A function like INDIRECT is fully based on this logic. It will react differently if a cell address you pass in is written in some other cell or not. It will also react differently if the value of a cell passed into the function has a normal value or a cell reference in A1 notation. This is why we need to get the cell references/addresses of the cells into actual cells.

Why do we need only column letters in cells?

As we needed the cell addresses to use in formulas or functions, we may need only the column letters to use somewhere else it depending on the condition.

Conclusion

Wrapping up how to Find Column Letters in Google Sheets. We have learned what are column letters, why we need them, and how we can find them. We have some of the most important functions to find the cell address of a given row and column number in a cell. We further see how to manipulate a cell address but slicing, and extracting only the column letter from it. We comprehensively learned about the ADDRESS function with all the possible use cases and optional parameters. Since we have already covered slicing functions in this series of articles, so I did not emphasize the slicing functions like LEFT, SUBSTITUTE, MID, and REPLACE.

I hope you find this article helpful and that you have learned something new from it. I will see you again very soon with another helpful article. Till then take care, and keep learning with Office Demy.