Spreadsheets Made

Convert column letter to number with Apps Script

Written by

Spreadsheets Made

in

This is an Apps Script function that converts column letter to column number. It is to be used with Google Sheets and works up to column ZZZ. -SM, 2026-05-10

/**
 * Function converts column letter to column number
 * 
 * - @param columnLetter is required.
 * - @param startAt0or1 is optional.
 * 
 * - If @param startAt0or1 is 1,
 *   letter A is converted to number 1.
 * - If @param startAt0or1 is anything other than 1,
 *   letter A is converted to number 0.
 * 
 * - Google Sheets column limit is ZZZ
 *   (https://support.google.com/drive/answer/37603).
 */

function columnLetterToNumber(columnLetter, startAt0or1) {

  const alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
  const alphabetLength = alphabet.length; // 26
  const colLetLen = columnLetter.length;

  const rightLetter = columnLetter.slice(colLetLen - 1, colLetLen);

  // Convert right letter to number
  let columnNumber = alphabet.indexOf(rightLetter);

  // If column letter is AA or bigger
  if (colLetLen > 1) {
    const middleLetter = columnLetter.slice(colLetLen - 2, colLetLen - 1);

    // Convert middle letter to number, and add it to existing column number
    columnNumber = columnNumber
      + (alphabet.indexOf(middleLetter) + 1)
      * alphabetLength;


    // If column letter is AAA or bigger
    if (colLetLen > 2) {
      const leftLetter = columnLetter.slice(colLetLen - 3, colLetLen - 2);

      // Convert left letter to number, and add it to existing column number
      columnNumber = columnNumber
        + (alphabet.indexOf(leftLetter) + 1)
        * alphabetLength
        * alphabetLength;
    }
  }

  // Define if column numbers start at 0 or 1 
  if (startAt0or1 === 1) {
    columnNumber++;
  }

  // Return column number
  return columnNumber;
}

And here is a short version of this function, but it works only up to and including column Z.

function columnLetterToNumber(columnLetter) {
  return "ABCDEFGHIJKLMNOPQRSTUVWXYZ".indexOf(columnLetter);
}

Old version of this function. Am leaving it here just for reference. -SM, 2024-07-28

/**
 * Function converts column letter to column number
 * 
 * - @param columnLetter is required.
 * - @param startAt0or1 is optional.
 * 
 * - If @param startAt0or1 is 1,
 *   letter A is converted to number 1.
 * - If @param startAt0or1 is anything other than 1,
 *   letter A is converted to number 0.
 * 
 * - Google Sheets column limit is ZZZ
 *   (https://support.google.com/drive/answer/37603).
 */

function columnLetterToNumber(columnLetter, startAt0or1) {

  /**
   * Declare all variables
   */

  var alphabet;
  var alphabetLength;

  var columnNumber;

  var letterLength;
  var letterLeft;
  var letterMiddle;
  var letterRight;



  /**
   * Initialize main variables
   */

  alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

  alphabetLength = alphabet.length; /*26*/

  letterLength = columnLetter.length;



  /**
   * Calculate column number
   */

  /* Get rightmost letter */
  letterRight = columnLetter.slice(letterLength - 1, letterLength);

  /* Convert rightmost letter to number */
  columnNumber = alphabet.indexOf(letterRight);


  if (letterLength > 1) {

    /* Get middle letter */
    letterMiddle = columnLetter.slice(letterLength - 2, letterLength - 1);

    /* Convert middle letter to number, and add it to existing column number */
    columnNumber = columnNumber + (alphabet.indexOf(letterMiddle) + 1) * alphabetLength;


    if (letterLength > 2) {

      /* Get leftmost letter */
      letterLeft = columnLetter.slice(letterLength - 3, letterLength - 2);

      /* Convert leftmost letter to number, and add it to existing column number */
      columnNumber = columnNumber + (alphabet.indexOf(letterLeft) + 1) * alphabetLength * alphabetLength;
    }
  }



  /**
   * Define if column numbers start at 0 or 1 
   */

  if (startAt0or1 == 1) {

    columnNumber++;
  }



  /**
   * Return column number
   */

  return columnNumber;
}

More posts