Convert column index into corresponding column letter

Question

I need to convert a Google Spreadsheet column index into its corresponding letter value, for example, given a spreadsheet:

I need to do this (this function obviously does not exist, it's an example):

getColumnLetterByIndex(4);  // this should return "D"
getColumnLetterByIndex(1);  // this should return "A"
getColumnLetterByIndex(6);  // this should return "F"

Now, I don't recall exactly if the index starts from 0 or from 1, anyway the concept should be clear.

I didn't find anything about this on gas documentation.. am I blind? Any idea?

Thank you

Triadelphous

How come this isn't builtin ?? – Maple

Duplicate: #12699530 – Script

X = (n) => (a=Math.floor(n/26)) >= 0 ? X(a-1) + String.fromCharCode(65+(n%26)) : ''; – Merl

@PascalDeMilly Could you explain how to use that? Does X receive the string? Where does the column index go? – Conjoined

I am aware this doesn't answer the question, but for us XY problem people... programatically accessing letter designated columns can often be done using R1C1 notation = Row 1 Column 1 etc. – Igenia

Answer 1

I wrote these a while back for various purposes (will return the double-letter column names for column numbers > 26):

function columnToLetter(column)
{
  var temp, letter = '';
  while (column > 0)
  {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

function letterToColumn(letter)
{
  var column = 0, length = letter.length;
  for (var i = 0; i < length; i++)
  {
    column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
  }
  return column;
}

Exhibitive

Is the max less than ZZ? – Henke

Just used this to increment column numbers: var column = letterToColumn(AA); columnToLetter(column + 1);. Might be helpful to someone. – Transmittance

I suggest to change column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1); to column += (letter.toUpperCase().charCodeAt(i) - 64) * Math.pow(26, length - i - 1); to make it work if letter contains lower-case letters, otherwise for a it would output 33 instead of 1 – Canula

I think String.fromCharCode doesn't work on Apps Script – Canoe

You sure there is nothing to do that? Several functions have a "B14" syntax and a (14, 2) syntax. I'm surprised there is no letter to number auto conversion... See @Tomi Heiskanen answer – Phillisphilly

Answer 2

This works good

=REGEXEXTRACT(ADDRESS(ROW(); COLUMN()); "[A-Z]+")

even for columns beyond Z.

Simply replace COLUMN() with your column number. The value of ROW() doesn't matter.

Ics

you are using formulas, not GAS – Triadelphous

This works really well. Even though this isn't using GAS (with which the question is tagged), built-in functions are almost always preferable given that they don't need to be written at all and, in my experience, run significantly faster. – Uniflorous

Answer 3

No need to reinvent the wheel here, use the GAS range instead:

 var column_index = 1; // your column to resolve
 
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];
 var range = sheet.getRange(1, column_index, 1, 1);

 Logger.log(range.getA1Notation().match(/([A-Z]+)/)[0]); // Logs "A"

Pecan

This should be the accepted answer. You can also strip the number via replace(/\d+/, ''). – Crusty

I'm getting Exception: The parameters (String,number,number,number) don't match the method signature for SpreadsheetApp.Spreadsheet.getRange. – Demonetize

developers.google.com/apps-script/reference/spreadsheet/… The method's signature is integer for the first parameter, you are passing a string, thus the error. Convert it into an integer first or check that you are passing the correct variable as parameter. – Pecan

Works pretty well wihtout the ".match(/([A-Z]+)/)". Is this a "just-in-case" security check to avoid random errors or am I missing something important here ? – Ziska

@Ziska it is to remove the line number from the notation, e.g. getA1Notation() returns something like "AZ1" and to get the plain column index "AZ", you need to strip the line number – Pecan

Answer 4

=SUBSTITUTE(ADDRESS(1,COLUMN(),4), "1", "")

This takes your cell, gets it's address as e.g. C1, and removes the "1".

How it works

• COLUMN() gives the number of the column of the cell.
• ADDRESS(1, ..., <format>) gives an address of a cell, in format speficied by <format> parameter. 4 means the address you know - e.g. C1.
  • The row doesn't matter here, so we use 1.
  • See ADDRESS docs
• Finally, SUBSTITUTE(..., "1", "") replaces the 1 in the address C1, so you're left with the column letter.

Exhaustive

I got this when trying: screencast.com/t/Jmc8L9W5LB. I figured this out. I solved it by replacing all commas with semi-colons. This is probably a localization issue – Corpuscle

@David, It works for me with commas. Maybe it's because of locale settings. – Script

The question is asking for a function for use in scripts whereas this solution gives a formula for use in cells. – Roberts

I wonder what will happen at rows with more-than-one digits (i.e. row 10 and below). will the subtitute work well? – Cyclometer

Answer 5

This works on ranges A-Z

formula =char(64+column())

js String.fromCharCode(64+colno)

an google spreadsheet appscript code, based on @Gardener would be:

function columnName(index) {
    var cname = String.fromCharCode(65 + ((index - 1) % 26));
    if (index > 26)
        cname = String.fromCharCode(64 + (index - 1) / 26) + cname;
    return cname;
}

Watchcase

This is quite tricky for people who don't come from a programming background and won't work on columns beyond "Z" (i.e. "AA") but I like it anyway because it's the shortest and fastest calculation (e.g. you could have 1,000s of these going at once without your computer breaking a sweat). – Oreste

Unfortunately this answer is a sheet formula, not usable in GAS. – Fredericton

Answer 6

JavaScript version of [this Python code]:

function nToAZ(n) {
  return (a = Math.floor(n / 26)) >= 0
    ? nToAZ(a-1) + String.fromCharCode(65 + (n % 26))
    : '';
}

console.log(nToAZ(0) === 'A');
console.log(nToAZ(25) === 'Z');
console.log(nToAZ(26) === 'AA');
console.log(nToAZ(51) === 'AZ');
console.log(nToAZ(52) === 'BA');
console.log(nToAZ(18_277) === 'ZZZ');  // max # of columns in Google Sheets

Merl

From here #21229680 – Bradawl

Answer 7

Adding to @SauloAlessandre's answer, this will work for columns up from A-ZZ.

=if(column() >26,char(64+(column()-1)/26),) & char(65 + mod(column()-1,26))

I like the answers by @wronex and @Ondra Žižka. However, I really like the simplicity of @SauloAlessandre's answer.

So, I just added the obvious code to allow @SauloAlessandre's answer to work for wider spreadsheets.

As @Dave mentioned in his comment, it does help to have a programming background, particularly one in C where we added the hex value of 'A' to a number to get the nth letter of the alphabet as a standard pattern.

Answer updated to catch the error pointed out by @Sangbok Lee. Thank you!

Jaeger

It gives @ instead of Z when used in Z column. – Orgulous

@Sangbok is correct! This has been updated and tested for columns Z, AA, AZ, BB. I believe it will work at through ZZ. – Jaeger

Unfortunately this answer is a sheet formula, not usable in GAS. – Fredericton

Answer 8

I was looking for a solution in PHP. Maybe this will help someone.

<?php

$numberToLetter = function(int $number)
{
    if ($number <= 0) return null;

    $temp; $letter = '';
    while ($number > 0) {
        $temp = ($number - 1) % 26;
        $letter = chr($temp + 65) . $letter;
        $number = ($number - $temp - 1) / 26;
    }
    return $letter;
};

$letterToNumber = function(string $letters) {
    $letters = strtoupper($letters);
    $letters = preg_replace("/[^A-Z]/", '', $letters);

    $column = 0; 
    $length = strlen($letters);
    for ($i = 0; $i < $length; $i++) {
        $column += (ord($letters[$i]) - 64) * pow(26, $length - $i - 1);
    }
    return $column;
};

var_dump($numberToLetter(-1));
var_dump($numberToLetter(26));
var_dump($numberToLetter(27));
var_dump($numberToLetter(30));

var_dump($letterToNumber('-1A!'));
var_dump($letterToNumber('A'));
var_dump($letterToNumber('B'));
var_dump($letterToNumber('Y'));
var_dump($letterToNumber('Z'));
var_dump($letterToNumber('AA'));
var_dump($letterToNumber('AB'));

Output:

NULL
string(1) "Z"
string(2) "AA"
string(2) "AD"
int(1)
int(1)
int(2)
int(25)
int(26)
int(27)
int(28)

Critchfield

Answer 9

Simple way through Google Sheet functions, A to Z.

=column(B2) : value is 2
=address(1, column(B2)) : value is $B$1
=mid(address(1, column(B2)),2,1) : value is B

It's a complicated way through Google Sheet functions, but it's also more than AA.

=mid(address(1, column(AB3)),2,len(address(1, column(AB3)))-3) : value is AB

Bismuthic

Answer 10

Here's a two liner which works beyond ZZ using recursion:

Python

def col_to_letter(n):
    l = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
    return col_to_letter((n-1)//26) + col_to_letter(n%26) if n > 26 else l[n-1]

Javascript

function colToLetter(n) {
    l = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
    return n > 26 ? colToLetter(Math.floor((n-1)/26)) + colToLetter(n%26) : l[n-1]
}

Coly

Thanks for the decent solution. in Python version, l = string.ascii_uppercase makes sure you have all letters. – Anemone

Answer 11

I also was looking for a Python version here is mine which was tested on Python 3.6

def columnToLetter(column):
    character = chr(ord('A') + column % 26)
    remainder = column // 26
    if column >= 26:
        return columnToLetter(remainder-1) + character
    else:
        return character

Betatron

X=lambda n:~n and X(n/26-1)+chr(65+n%26)or'' – Script

Anyway, if I am not mistaken, this is base26. Tricky: next to Z should be AA. This gives BA. – Script

Your version is nearly right, I think it should be: X=lambda n:~int(n) and X(int(n/26)-1)+chr(65+n%26)or'' – Betatron

Answer 12

A comment on my answer says you wanted a script function for it. All right, here we go:

function excelize(colNum) {
    var order = 1, sub = 0, divTmp = colNum;
    do {
        divTmp -= order; sub += order; order *= 26;
        divTmp = (divTmp - (divTmp % 26)) / 26;
    } while(divTmp > 0);

    var symbols = "0123456789abcdefghijklmnopqrstuvwxyz";
    var tr = c => symbols[symbols.indexOf(c)+10];
    return Number(colNum-sub).toString(26).split('').map(c=>tr(c)).join('');
}

This can handle any number JS can handle, I think.

Explanation:

Since this is not base26, we need to substract the base times order for each additional symbol ("digit"). So first we count the order of the resulting number, and at the same time count the number to substract. And then we convert it to base 26 and substract that, and then shift the symbols to A-Z instead of 0-P.

Anyway, this question is turning into a code golf :)

Exhaustive

Good idea. But wrong ordering. 27 to A not AA. 703 to BAA not AAA. – Trainbearer

How about my answer? :) – Trainbearer

Answer 13

Java Apache POI

String columnLetter = CellReference.convertNumToColString(columnNumber);

Bogoch

Answer 14

This will cover you out as far as column AZ:

=iferror(if(match(A2,$A$1:$AZ$1,0)<27,char(64+(match(A2,$A$1:$AZ$1,0))),concatenate("A",char(38+(match(A2,$A$1:$AZ$1,0))))),"No match")

Quinquefid

Answer 15

A function to convert a column index to letter combinations, recursively:

function lettersFromIndex(index, curResult, i) {

  if (i == undefined) i = 11; //enough for Number.MAX_SAFE_INTEGER
  if (curResult == undefined) curResult = "";

  var factor = Math.floor(index / Math.pow(26, i)); //for the order of magnitude 26^i

  if (factor > 0 && i > 0) {
    curResult += String.fromCharCode(64 + factor);
    curResult = lettersFromIndex(index - Math.pow(26, i) * factor, curResult, i - 1);

  } else if (factor == 0 && i > 0) {
    curResult = lettersFromIndex(index, curResult, i - 1);

  } else {
    curResult += String.fromCharCode(64 + index % 26);

  }
  return curResult;
}

function lettersFromIndex(index, curResult, i) {

  if (i == undefined) i = 11; //enough for Number.MAX_SAFE_INTEGER
  if (curResult == undefined) curResult = "";

  var factor = Math.floor(index / Math.pow(26, i));

  if (factor > 0 && i > 0) {
    curResult += String.fromCharCode(64 + factor);
    curResult = lettersFromIndex(index - Math.pow(26, i) * factor, curResult, i - 1);

  } else if (factor == 0 && i > 0) {
    curResult = lettersFromIndex(index, curResult, i - 1);

  } else {
    curResult += String.fromCharCode(64 + index % 26);

  }
  return curResult;
}

document.getElementById("result1").innerHTML = lettersFromIndex(32);
document.getElementById("result2").innerHTML = lettersFromIndex(6800);
document.getElementById("result3").innerHTML = lettersFromIndex(9007199254740991);

32 --> <span id="result1"></span><br> 6800 --> <span id="result2"></span><br> 9007199254740991 --> <span id="result3"></span>

Girder

This returns A@ instead of Z for index 26 – Sudiesudnor

Answer 16

In python, there is the gspread library

import gspread
column_letter = gspread.utils.rowcol_to_a1(1, <put your col number here>)[:-1]

If you cannot use python, I suggest looking the source code of rowcol_to_a1() in https://github.com/burnash/gspread/blob/master/gspread/utils.py

Cotswold

Answer 17

Don't use 26 radix. Like below.

const n2c = n => {
  if (!n) return '';
  
  // Column number to 26 radix. From 0 to p.
  // Column number starts from 1. Subtract 1.
  return [...(n-1).toString(26)]
    // to ascii number
    .map(c=>c.charCodeAt())
    .map((c,i,arr)=> {
      // last digit
      if (i===arr.length-1) return c;
      // 10 -> p
      else if (arr.length - i > 2 && arr[i+1]===48) return c===49 ? null : c-2;
      // 0 -> p
      else if (c===48) return 112;
      // a-1 -> 9
      else if (c===97) return 57;
      // Subtract 1 except last digit.
      // Look at 10. This should be AA not BA.
      else return c-1;
    })
    .filter(c=>c!==null)
    // Convert with the ascii table. [0-9]->[A-J] and [a-p]->[K-Z]
    .map(a=>a>96?a-22:a+17)
    // to char
    .map(a=>String.fromCharCode(a))
    .join('');
};


const table = document.createElement('table');
table.border = 1;
table.cellPadding = 3;
for(let i=0, row; i<1380; i++) {
  if (i%5===0) row = table.insertRow();
  row.insertCell().textContent = i;
  row.insertCell().textContent = n2c(i);
}
document.body.append(table);

td:nth-child(odd) { background: gray; color: white; }
td:nth-child(even) { background: silver; }

Trainbearer

by far the best – Restate

It is nice and I hoped it did the trick but unfortunately it doesn't work past 'IZ'. Next col after 'IZ' should be 'JA' but it returns 'qA', 'qB', 'qC', etc... – Mossbunker

Thank you @inki. I fixed errors. 26 radix - toString(26) - was bad idea. AdamL's code looks good. – Trainbearer

Answer 18

If you need a version directly in the sheet, here a solution: For the colonne 4, we can use :

=Address(1,4)

I keep the row number to 1 for simplicty. The above formula returns $D$1 which is not what you want.

By modifying the formula a little bit we can remove the dollar signs in the cell reference.

=Address(1,4,4)

Adding four as the third argument tells the formula that we are not looking for absolute cell reference. Now the returns is : D1

So you only need to remove the 1 to get the colonne lettre if you need, for example with :

=Substitute(Address(1,4,4),"1","")

That returns D.

Phreno

Answer 19

This is a way to convert column letters to column numbers.

=mmult(ArrayFormula(ifna(vlookup(substitute(mid(rept(" ",3-len(filter(A:A,A:A<>"")))&filter(A:A,A:A<>""),sequence(1,3),1)," ",""),{char(64+sequence(26)),sequence(26)},2,0),0)*{676,26,1}),sequence(3,1,1,0))

Screenshot of the Google Sheet

Aynat

Answer 20

Simple typescript functional approach

const integerToColumn = (integer: number): string => {
  const base26 = (x: number): string =>
    x < 26
      ? String.fromCharCode(65 + x)
      : base26((x / 26) - 1) + String.fromCharCode(65 + x % 26)
  return base26(integer)
}

console.log(integerToColumn(0)) // "A"
console.log(integerToColumn(1)) // "B"
console.log(integerToColumn(2)) // "C"

Speechless

Answer 21

Here is a general version written in Scala. It's for a column index start at 0 (it's simple to modify for an index start at 1):

def indexToColumnBase(n: Int, base: Int): String = {
  require(n >= 0, s"Index is non-negative, n = $n")
  require(2 <= base && base <= 26, s"Base in range 2...26, base = $base")

  def digitFromZeroToLetter(n: BigInt): String =
    ('A' + n.toInt).toChar.toString

  def digitFromOneToLetter(n: BigInt): String =
    ('A' - 1 + n.toInt).toChar.toString

  def lhsConvert(n: Int): String = {
    val q0: Int = n / base
    val r0: Int = n % base

    val q1 = if (r0 == 0) (n - base) / base else q0
    val r1 = if (r0 == 0) base else r0

    if (q1 == 0)
      digitFromOneToLetter(r1)
    else
      lhsConvert(q1) + digitFromOneToLetter(r1)
  }

  val q: Int = n / base
  val r: Int = n % base

  if (q == 0)
    digitFromZeroToLetter(r)
  else
    lhsConvert(q) + digitFromZeroToLetter(r)
}

def indexToColumnAtoZ(n: Int): String = {
  val AtoZBase = 26
  indexToColumnBase(n, AtoZBase)
}

Kristie

Answer 22

In PowerShell:

function convert-IndexToColumn
{
    Param
    (
        [Parameter(Mandatory)]
        [int]$col
    )
    "$(if($col -gt 26){[char][int][math]::Floor(64+($col-1)/26)})$([char](65 + (($col-1) % 26)))"
}

Masque

Answer 23

Here is a 0-indexed JavaScript function without a maximum value, as it uses a while-loop:

function indexesToA1Notation(row, col) {
    const letterCount = 'Z'.charCodeAt() - 'A'.charCodeAt() + 1;
    row += 1
    let colName = ''
    while (col >= 0) {
        let rem = col % letterCount
        colName = String.fromCharCode('A'.charCodeAt() + rem)
        col -= rem
        col /= letterCount
    }
    return `${colName}${row}`
}

//Test runs:
console.log(indexesToA1Notation(0,0)) //A1
console.log(indexesToA1Notation(37,9)) //J38
console.log(indexesToA1Notation(5,747)) //ABT6

I wrote it for a web-app, so I'm not 100% sure it works in Google Apps Script, but it is normal JavaScript, so I assume it will.

For some reason I cant get the snippet to show its output, but you can copy the code to some online playground if you like

Suppletion

Answer 24

Here's a zero-indexed version (in Python):

letters = []
while column >= 0:
    letters.append(string.ascii_uppercase[column % 26])
    column = column // 26 - 1
return ''.join(reversed(letters))

Arching

This isn't it. The question is not base26. I also originally got lured into that :) – Script