CODEWITHSUNDEEP
javascriptgoogle-apps-scriptindexinggoogle-sheets
BeNdErR
BeNdErR

Reputation: 17937

Convert column index into corresponding column letter

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

enter image description here

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

Upvotes: 140

Views: 125870

Answers (24)

Pascal DeMilly
Pascal DeMilly

Reputation: 731

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

Upvotes: 14

Emanuel
Emanuel

Reputation: 3277

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"

Upvotes: 0

Jehong Ahn
Jehong Ahn

Reputation: 2416

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; }

Upvotes: 1

dolfus61
dolfus61

Reputation: 1

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

Upvotes: 0

Fabien Letort
Fabien Letort

Reputation: 31

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.

Upvotes: 0

Frederik
Frederik

Reputation: 31

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

Upvotes: -1

SauloAlessandre
SauloAlessandre

Reputation: 835

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;
}

Upvotes: 30

Mordin Solus ME
Mordin Solus ME

Reputation: 59

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]
}

Upvotes: 2

George C
George C

Reputation: 1233

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

Upvotes: 0

Fred
Fred

Reputation: 949

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)

Upvotes: 3

jim_kastrin
jim_kastrin

Reputation: 5040

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>

Upvotes: 0

redlasha
redlasha

Reputation: 81

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

Upvotes: 2

Mike Shaw
Mike Shaw

Reputation: 1

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)))"
}

Upvotes: -1

Gardener
Gardener

Reputation: 2660

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!

Upvotes: 3

Ondra Žižka
Ondra Žižka

Reputation: 46914

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

Upvotes: 1

Ian Elliott
Ian Elliott

Reputation: 7

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

Upvotes: -1

Tomi Heiskanen
Tomi Heiskanen

Reputation: 611

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"

Upvotes: 51

hum3
hum3

Reputation: 1721

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

Upvotes: 1

Christian Swanson
Christian Swanson

Reputation: 9

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

Upvotes: 0

CelinHC
CelinHC

Reputation: 1984

Java Apache POI

String columnLetter = CellReference.convertNumToColString(columnNumber);

Upvotes: 0

blais
blais

Reputation: 737

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

Upvotes: -2

Ondra Žižka
Ondra Žižka

Reputation: 46914

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

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

enter image description here

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.

Upvotes: 42

wronex
wronex

Reputation: 1035

This works good

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

even for columns beyond Z.

Demo of function

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

Upvotes: 85

AdamL
AdamL

Reputation: 24659

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;
}

Upvotes: 236

Related Questions