A Quick Guide to Convert Colum Numbers to Column Letters in Google Sheets

August 30, 2021 Twinkle Kapoor How To

There’s a possibility that you can easily convert column numbers to corresponding column letters, or convert column numbers to A1 notion cell references in Google Sheets as well as Microsoft Excel.

We might need to put in some efforts for MS excel but Google Sheets includes built-in functions for converting cell reference in A1 notion to row and column numbers and another function for converting column alphabets (like AA) into the column index.

When we use the function:

=ADDRESS ( 33, 38, 4 )

This function will return the A1 Style notion of the cell whose row number is 33 and column number is 38.

On using the function:

=COLUMN ( C9 )

It returns the column number of the specific cell C9 where column A corresponds to 1 and column AA responds to 27.

Related Post: Use Data Functions in Google Sheets

Getting A1 Notion with JavaScript

You may also need to calculate the A1 notion style reference of a cell whose row and column numbers are known in the JSON data of the sheet if you’re working with the Google Sheets API.

You can use the getA1Notion ( ) method to get the range address in A1 notion, when using container-bound Google Sheets.

const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange(1, 2);
Logger.log(range.getA1Notation());

In case you are not using the Spreadsheet service, you can also calculate the A1 notion reference of a cell using simple JavaScript.

/**
*
@param {number} row – The row number of the cell reference. Row 1 is row number 0.
@param {number} column – The column number of the cell reference. A is column number 0.
@returns {string} Returns a cell reference as a string using A1 Notation
*
@example
*
getA1Notation(2, 4) returns “E3”
getA1Notation(2, 4) returns “E3”
*
*/
const getA1Notation = (row, column) => {
const a1Notation = [${row + 1}];
const totalAlphabets = “Z”.charCodeAt() – “A”.charCodeAt() + 1;
let block = column;
while (block >= 0) {
a1Notation.unshift(
String.fromCharCode((block % totalAlphabets) + “A”.charCodeAt())
);
block = Math.floor(block / totalAlphabets) – 1;
}
return a1Notation.join(“”);
};

This is equivalent to =ADDRESS ( ) function of Google Sheets.

Steps to Get Column Number from A1 Notion

The function we are going to learn using now takes the cell reference in A1 notion and returns the column number and row number of a cell in the spreadsheet.

/**
*
@param {string} cell – The cell address in A1 notation
@returns {object} The row number and column number of the cell (0-based)
*
@example
*
fromA1Notation(“A2”) returns {row: 1, column: 3}
*
*/
const fromA1Notation = (cell) => {
const [, columnName, row] = cell.toUpperCase().match(/([A-Z]+)([0-9]+)/);
const characters = “Z”.charCodeAt() – “A”.charCodeAt() + 1;
let column = 0;
columnName.split(“”).forEach((char) => {
column *= characters;
column += char.charCodeAt() – “A”.charCodeAt() + 1;
});
return { row, column };
};

This is equivalent to the =ROW ( ) and =COLUMN ( ) functions available in the Google Sheets.

We have reached the end of this guide to convert column numbers to column letters in Google sheets. I hope this guide helps you understand the functions and use them for a successful conversion.

Author

Twinkle Kapoor

View all posts