Skip to Content

How to Get the Column Letter in Google Sheets

Sometimes when working in spreadsheet programs like Google Sheets, you may want to use a formula to get the column letter.

Some formulas in Google Sheets use a column index number, and it can be useful to convert this number to the column letter of the column you are working on.

In this tutorial, I will show you how to get the column letter in Google Sheets.

Get Column Letter

To get the column letter in Google Sheets, there are a couple of different formulas we can use.

The first thing you need to do is learn how to use the ADDRESS function.

The ADDRESS function will return the cell reference as a string when you input a row and column number.

The syntax of the ADDRESS function is:

=ADDRESS(row, column, [absolute_relative_mode])

  • row – this is the row number of the cell reference
  • column – this is the column number of the cell reference. (A is column 1, B is column 2, etc…)
  • absolute_relative_mode – this is an optional argument that will be set to 1 by default. 1 is absolute row and column ($A$1), 2 is row absolute, and column relative (A$1), 3 is row relative and column absolute ($A1), and 4 is row and column relative (A1)

Here is an example of how this can be used in a spreadsheet:

Address function example

In this example, the third argument is the formula (4) means relative row and column. The address then is simply the row of my first argument (1), and the column letter of the second argument (1, 2, 3, 4).

So you can now see how we can begin to use this formula to convert a column number to a column letter. The only thing we need to do now is get rid of the row number from the formula and just return the letter.

To do this, we will be using the SUBSTITUTE function. I am not going to cover this function in too much detail in this post, you can read my previous guide on the SUBSTITUTE function. But what you need to know is that the SUBSTITUTE function will take an existing text in a string and replace it to output a new string.

So what I will be doing is replacing the row number in the previous example, so that only the column letter is remaining.

Here is how this is done:

SUBSTITUTE row number

The formula used in this example is:

=SUBSTITUTE(A2,1,””)

What this formula does is it will take the string in cell A1, and replace the “1” in the string with a blank value (“”). So essentially what this does is remove the row number and leave the column letter.

Now that we have learned how to use the ADDRESS and SUBSTITUTE functions individually, we can combine them to create one formula that will convert a column number to a column letter.

This is what the formula will be:

=SUBSTITUTE(ADDRESS(1,column_number,4),1,””)

This formula is simply combining the previous formulas we used to do everything in one step. You simply need to put the column number or the cell reference to a cell containing a column number in the formula.

Here is how this will look in a spreadsheet:

Column Number to Column Letter

As you can see, with this formula now I can just input a column number into column A and in column B it will output the letter for the column.

Closing Thoughts

Combining the ADDRESS and SUBSTITUTE functions together as I have shown is the best way to get the column letter in Google Sheets.

This formula seems a little complicated at first, but if you understand each function individually, then learning how to combine them is easy to understand.

If you still need help learning this, make sure to watch the video and it should clear things up!

More Google Sheets Tutorials:
How to Get the Last Value in a Column