S
S
Shamil Khairulaev2021-12-19 21:04:08
Google Sheets
Shamil Khairulaev, 2021-12-19 21:04:08

How to generate column coordinates for any serial number in Google Sheets?

I am working with google sheets api. And at some points it may be necessary to extract the literal coordinates by the passed column serial number (for example, if it is 26, then this is the letter Z, etc.). With numbers <= 26, everything is very simple, you can create an array with the alphabet and pull it out by index. And if the number is greater, then I have difficulties in compiling the correct coordinates (for 27 it is AA, etc.). Tell me some algorithm or provide a ready-made function that can handle any number. I write in Php if that matters. And so, I think I can disassemble the algorithm if it is written in another language (except for assembler).

Answer the question

In order to leave comments, you need to log in

2 answer(s)
G
Grigory Boev, 2021-12-20
@ProgrammerForever

Like this, for example

//Имя столбца по его номеру
function col2A1(col){
  let result = "";
  let base = 27;
  while(col>0){    
    let newLetter = String.fromCharCode("A".charCodeAt(0)+(col-1)%(base-1));  
    result=newLetter + result;
    col = (col - col%base) / base;
  };
  return result;
};
// или короткая версия
function col2A1_(r){let o="";for(;0<r;){var t=String.fromCharCode("A".charCodeAt(0)+(r-1)%26);o=t+o,r=(r-r%27)/27}return o}

A
Alexander, 2021-12-19
@ForestAndGarden

A = 1, B = 2 ... Z = 26.
The column number cannot be less than 1 and greater than 18278.

  1. 10000: 26 = 384 and 16 in the remainder, respectively. P
  2. 384 ≥ 26 - TRUE - continue calculations
  3. 384: 26 = 14 and 20 in the remainder, respectively. T
  4. 14 ≥ 26 - FALSE - 14 resp. N

Column address: NTP
Picture-confirmation.

61bf8f82ae6d0344144546.jpeg

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question