Convert Column Numbers to Letters in Google Sheets and Excel

Introduction

When working with spreadsheets, you’ll often need to translate a column’s numeric index (such as 28) into its alphabetical label (AB) or perform the reverse operation. While the conversion seems trivial for a few columns, it quickly becomes cumbersome when dealing with large data sets, dynamic formulas, or automated scripts. Both Google Sheets and Microsoft Excel provide built‑in functions and custom formulas that can handle these transformations efficiently. This article explores the underlying logic of column‑letter conversion, demonstrates practical methods for Google Sheets and Excel, and offers tips for integrating the conversion into larger workflows, ensuring you can move seamlessly between numeric and alphanumeric column references.

Understanding the Alphabetic Base‑26 System

Spreadsheet columns are essentially a base‑26 numbering system, but with a twist: there is no zero digit. The sequence starts at A (1) and proceeds to Z (26), after which it rolls over to AA (27), AB (28), and so on. To convert a number to a letter:

• Subtract 1 from the column number to align it with zero‑based indexing.
• Repeatedly divide by 26, recording the remainder each time.
• Map each remainder (0‑25) to its corresponding letter (A‑Z) and reverse the order of the letters.

The reverse—turning AB into 28—requires treating each character as a positional value, multiplying the leftmost letter by 26 and adding the rightmost.

Google Sheets: Built‑In Functions and Custom Formulas

Google Sheets does not include a native COLUMN‑to‑letter function, but you can achieve the conversion with a combination of ADDRESS, SUBSTITUTE, and REGEXEXTRACT, or by writing a short Apps Script.

• Formula‑only method: =REGEXEXTRACT(ADDRESS(1, 28, 4), "[A-Z]+") returns AB. Change the second argument to any column number.
• Reverse conversion: =COLUMN(INDIRECT("AB1")) yields 28. Replace “AB” with any column letters.
• Apps Script function:

  function colToLetter(num) {
        var letter = '';
        while (num > 0) {
          var mod = (num - 1) % 26;
          letter = String.fromCharCode(65 + mod) + letter;
          num = Math.floor((num - mod) / 26);
        }
        return letter;
      }

  This script can be called from a cell as =colToLetter(28).

Microsoft Excel: Native Functions and VBA Solutions

Excel offers the ADDRESS function, which can be paired with SUBSTITUTE to strip the row number, delivering the column letters directly.

• Formula approach: =SUBSTITUTE(ADDRESS(1,28,4), "1", "") returns AB. Adjust the second argument for other columns.
• Reverse conversion: =COLUMN(INDIRECT("AB1")) provides the numeric index.
• VBA custom function:

  Function ColToLetter(colNum As Long) As String
          Dim result As String
          Do While colNum > 0
              colNum = colNum - 1
              result = Chr(65 + (colNum Mod 26)) & result
              colNum = colNum \ 26
          Loop
          ColToLetter = result
      End Function

  Use it in a worksheet as =ColToLetter(28).

Practical Applications and Automation Tips

Knowing how to toggle between numbers and letters unlocks several powerful use cases:

• Dynamic range creation: Build formulas that adapt to varying column widths, such as =SUM(INDIRECT(colToLetter(A1)&"1:"&colToLetter(A1)&"100")) in Google Sheets.
• Data migration scripts: When exporting data to databases that expect numeric column IDs, automate the conversion to keep mappings consistent.
• Conditional formatting: Apply rules based on column position without hard‑coding letters, making templates reusable across projects.
• Cross‑platform compatibility: Use the same logic in both Google Sheets and Excel to maintain a single source of truth for complex spreadsheets shared across teams.

Conclusion

Converting column numbers to letters—and back again—is a fundamental skill for anyone who builds advanced spreadsheets in Google Sheets or Microsoft Excel. By grasping the base‑26 logic, you can leverage built‑in functions like ADDRESS and INDIRECT, craft concise formulas, or extend functionality with simple Apps Script or VBA code. These techniques not only simplify manual look‑ups but also empower you to automate range definitions, streamline data migrations, and create adaptable templates that work across platforms. Armed with the methods outlined above, you can confidently handle any column‑reference challenge, turning what once seemed a tedious task into a seamless part of your spreadsheet workflow.