💾A1NotationToRowColumn()

GAS ⟩ Classes ⟩ Range ⟩ A1 notation ⟩ A1NotationToRowColumn()

💾 程式

• replit

equivalent to =ROW(), =COLUMN() in Google Sheets.

/**
 * A1 notation to [row, col]
 *
 * @param {string} a1notation - cell address in "A1 notation"
 * @returns {number[]} [row, col] (1-based)
 *
 * @example
 *
 *   A1NotationToRowColumn("A2")   // [ 2,  1 ]
 *   A1NotationToRowColumn("AA3")  // { 3, 27 ]
 */
function A1NotationToRowColumn(a1notation) {

    // character index (start from 1)
    // ------------------------------
    // A -> 1, ..., Z -> 26
    function CharIndex(char){
        return char.charCodeAt() - 'A'.charCodeAt() + 1
    }

    // capture groups                                            ╭──1───╮╭──2───╮
    const [_, columnName, row] = a1notation.toUpperCase().match(/([A-Z]+)([0-9]+)/);
    const numLetters = 26;    // A - Z
    
    // 'ABC' -> [A,B,C] -> ((0*26 + A)*26 + B)*26 + C = A*26^2 + B*26^1 + C
    let col = columnName
        .split('')
        .reduce((sum, char) => sum * numLetters + CharIndex(char), 0);

    return [+row, col];    // row: String -> Number
}

💈範例：

A1NotationToRowColumn("A2"),    // [ 2,  1 ]
A1NotationToRowColumn("AA3"),   // { 3, 27 ]

📗 參考

• How to Convert Column Number (e.g. 28) to Column Letter (e.g. AB) in Google Sheets ⭐️ - row/column number ⇄ A1 notation

👥 相關

• A1Notation() - (row, col) to A1 notation

• columnName()

• str.match()

• destructuring array