Question

182

Convert column index into corresponding column letter

rated 0 times [ 185] [ 3] / answers: 1 / hits: 109795 / 11 Years ago, mon, january 20, 2014, 12:00:00

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

enter

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

Answers

Only authorized users can answer the question. Please sign in first, or register a free account.

kennedysaraiw

Add To Favorites

Follow

Total Points: 552

Total Questions: 99

Total Answers: 109

Location: South Sudan

Member since Sun, Jul 11, 2021

3 Years ago

answered 11 Years ago neildrews · Accepted Answer

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;

}