Question 332133 in Javascript Question posted in
A very good W3school tutorial can be found here.

Parsing Excel-like Cell Range to Numeric Format in JavaScript

I need to convert Excel-like cell ranges into a numeric format in JavaScript. The format I am aiming for is [rowStart, columnStart, rowEnd, columnEnd].

For example, a cell range like "A1:B3" should be converted to "1:1:3:2", and a range like "AA1:AAV5" should become "1:27:5:724".

I am struggling to come up with a way to convert the column letters to numbers, especially when the column range exceeds ‘Z’ and starts from ‘AA’, ‘AB’, and so on.

Here is what I have tried so far:

function parseCellRange(cellRange) {
    // Split the range into start and end
    let [startCell, endCell] = cellRange.split(':');
    
    // Split startCell and endCell into row and column
    let startColum = startCell.match(/[a-zA-Z]+/g);
    let startRow = startCell.match(/(d+)/)[1];
    let endColumn = endCell.match(/[a-zA-Z]+/g);
    let endRow = endCell.match(/(d+)/)[1];

    // Convert column letters to numbers
    // This is where I am stuck    

    // Generate new cell range format
    let newCellRange = [rowStart, columnStart, rowEnd, columnEnd] 
    
    return(newCellRange)
}

// Tests
parseCellRange("A1:B3"); // Should return [1, 1, 3, 2]
parseCellRange("AA1:AAV5"); // Should return [1, 27, 5, 724]
Tags:

3

Answers


  1. 0

    You could a function to convert a series of letters to a number (see also here) and use one regex-replace operation to make the final conversion:

    function lettersToNumber(letters) {
    letters = letters.toLowerCase();
    const offset = "a".charCodeAt() - 1;
    let sum = 0;
    for (let i = 0; i < letters.length; i++) {
        sum = sum * 26 + letters.charCodeAt(i) - offset;
    }
    return sum;
}

function parseCellRange(range) {
    return range.replace(/^([a-z]+)(d+):([a-z]+)(d+)$/gi, (_, c1, r1, c2, r2) =>
        `${r1}:${lettersToNumber(c1)}:${r2}:${lettersToNumber(c2)}`
    );
}

// Tests
console.log(parseCellRange("A1:B3")); // 1:1:3:2
console.log(parseCellRange("AA1:AAV5")); // 1:27:5:724

    This returns the text version as you described at the top of your question. If you want arrays, like specified in your code comments, then use this version of parseCellRange:

    function lettersToNumber(letters) {
    letters = letters.toLowerCase();
    const offset = "a".charCodeAt() - 1;
    let sum = 0;
    for (let i = 0; i < letters.length; i++) {
        sum = sum * 26 + letters.charCodeAt(i) - offset;
    }
    return sum;
}

function parseCellRange(range) {
    const [[, c1, r1, c2, r2]] = range.matchAll(/^([a-z]+)(d+):([a-z]+)(d+)$/gi);
    return [+r1, lettersToNumber(c1), +r2, lettersToNumber(c2)];
}

// Tests
console.log(parseCellRange("A1:B3")); // [1, 1, 3, 2]
console.log(parseCellRange("AA1:AAV5")); // [1, 27, 5, 724]
    Login or Signup to reply.
  2. 0

    You can consider the column name as a 26-based number and then you want to convert it to a decimal number:

    For example AAV, the equation is:

    index(V) * 26^0 + index(A) * 26^1 + index(A) * 26^2
= (22 * 1) + (1 * 26) + (1 * 676) = 724

    Here is a working solution:

    const ASCII_A = 'A'.charCodeAt();

function getColumnNumber(column) {
  let res = 0, power = 1;
  for (const letter of column.split("").reverse()) {
    const letterIndex = letter.charCodeAt() - ASCII_A + 1;
    res += power * letterIndex;
    power *= 26;
  }
  
  return res;
}

function parseCellRange(range) {
    // Split the range into start and end
    let [startCell, endCell] = range.split(':');
    
    // Split startCell and endCell into row and column
    let startColumn = startCell.match(/[a-zA-Z]+/g)[0];
    let startRow = +startCell.match(/(d+)/)[1];
    let endColumn = endCell.match(/[a-zA-Z]+/g)[0];
    let endRow = +endCell.match(/(d+)/)[1];

    // Convert column letters to numbers
    const startColumnNumber = getColumnNumber(startColumn);
    const endColumnNumber = getColumnNumber(endColumn);

    // Generate new cell range format
    let newCellRange = [startRow, startColumnNumber, endRow, endColumnNumber];
    
    return(newCellRange)
}

// Tests
console.log(parseCellRange("A1:B3")); // Should return [1, 1, 3, 2]
console.log(parseCellRange("AA1:AAV5")); // Should return [1, 27, 5, 724]
    Login or Signup to reply.
  3. 0

    I build this class that works with any letters array.

    class LetterToNumber
{
  constructor(letters) {
    this._map = {};
    let j = 0;
    for (let l0 of letters) {
      this._map[l0] = ++j;
    }
    for (let l1 of letters) {
      for (let l2 of letters) {
        this._map[l1+l2] = ++j;
      }
    }
  }
  getNumber(key) {
    if (!this._map.hasOwnProperty(key))
      throw 'not found';
    return this._map[key];
  }
}

// usage
let letters = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
let ltn = new LetterToNumber(letters);
for (let key of ['A','AC','AA','ZS','FI']) {
  echo key +' = '+ ltn.getNumber(key);
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search