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

how to Find Column Letters in Google Sheets 5

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.

See also  Version History in Google Sheets (Complete Beginner's Guide)

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

how to Find Column Letters in Google Sheets 1

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

how to Find Column Letters in Google Sheets 2

Step 2

Pass the first argument (row number)

how to Find Column Letters in Google Sheets 3

Step 3

Pass the second argument (column number)

how to Find Column Letters in Google Sheets 4

Step 4

Pass optional argument if you want to

how to Find Column Letters in Google Sheets 5

how to Find Column Letters in Google Sheets 6

Step 5

Press Enter and you have got the cell address.

how to Find Column Letters in Google Sheets 7

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)

how to Find Column Letters in Google Sheets 8

how to Find Column Letters in Google Sheets 9

6.2

Absolute row and Relative column (A$1)

how to Find Column Letters in Google Sheets 10

how to Find Column Letters in Google Sheets 11

6.3

Relative row and Absolute column ($A1)

how to Find Column Letters in Google Sheets 12

how to Find Column Letters in Google Sheets 13

6.4

Relative row and Relative column (A1)

how to Find Column Letters in Google Sheets 14

how to Find Column Letters in Google Sheets 15

Step 7

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

7.1

A1 Notation

how to Find Column Letters in Google Sheets 16

how to Find Column Letters in Google Sheets 17

7.2

R1C1 Notation

how to Find Column Letters in Google Sheets 18

how to Find Column Letters in Google Sheets 19

how to Find Column Letters in Google Sheets 20

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).

See also  Keyboard Shortcuts in Google Sheets [Top 100]

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,””)

how to Find Column Letters in Google Sheets 21

how to Find Column Letters in Google Sheets 22

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)

how to Find Column Letters in Google Sheets 23

how to Find Column Letters in Google Sheets 24

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)

how to Find Column Letters in Google Sheets 25

how to Find Column Letters in Google Sheets 26

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,””)

how to Find Column Letters in Google Sheets 27

how to Find Column Letters in Google Sheets 28

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.

See also  How to Add Date & Time in Google Sheets [2 Methods]

Important Notes

  • We can use so many functions to slice the part of the address and get the substring from it.
  • Similarly, we can find the row numbers, but row numbers are already written so we don’t need them.
  • Column letters can be required because they are assigned with alphabets not with numbers so we may need the numbers for them to pass in functions or formulas.
  • To get the column letters inside a cell address is not very useful, but to be an advanced and professional google sheet user, you must know about such tricks and methods to be ahead in your competition

See also  How to Add Subscripts and Superscripts in Google Sheets (Easy Methods)

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.

See also  How to Change Decimal Place in Google Sheets [2 Methods]

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.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

OfficeDemy.com
Logo
Enable registration in settings - general