# Convert column index into corresponding column letter

I need to convert a Google Spreadsheet column index into its corresponding letter value, for example, given a spreadsheet:

I need to do this (this function obviously does not exist, it's an example):

``````getColumnLetterByIndex(4);  // this should return "D"
getColumnLetterByIndex(1);  // this should return "A"
getColumnLetterByIndex(6);  // this should return "F"
``````

Now, I don't recall exactly if the index starts from `0` or from `1`, anyway the concept should be clear.

Thank you

I wrote these a while back for various purposes (will return the double-letter column names for column numbers > 26):

``````function columnToLetter(column)
{
var temp, letter = '';
while (column > 0)
{
temp = (column - 1) % 26;
letter = String.fromCharCode(temp + 65) + letter;
column = (column - temp - 1) / 26;
}
return letter;
}

function letterToColumn(letter)
{
var column = 0, length = letter.length;
for (var i = 0; i < length; i++)
{
column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
}
return column;
}
``````

This works good

``````=REGEXEXTRACT(ADDRESS(ROW(); COLUMN()); "[A-Z]+")
``````

even for columns beyond Z.

Simply replace `COLUMN()` with your column number. The value of `ROW()` doesn't matter.

``````=SUBSTITUTE(ADDRESS(1,COLUMN(),4), "1", "")
``````

This takes your cell, gets it's address as e.g. C1, and removes the "1".

How it works

• `COLUMN()` gives the number of the column of the cell.
• `ADDRESS(1, ..., <format>)` gives an address of a cell, in format speficied by `<format>` parameter. `4` means the address you know - e.g. `C1`.
• Finally, `SUBSTITUTE(..., "1", "")` replaces the `1` in the address `C1`, so you're left with the column letter.

this work on interval A-Z

=char(64+column())

No need to reinvent the wheel here, use the GAS range instead:

`````` var column_index = 1; // your column to resolve

var sheet = ss.getSheets()[0];
var range = sheet.getRange(1, column_index, 1, 1);

Logger.log(range.getA1Notation().match(/([A-Z]+)/)[0]); // Logs "A"``````

In javascript:

``````X = (n) => (a=Math.floor(n/26)) >= 0 ? X(a-1) + String.fromCharCode(65+(n%26)) : '';
console.assert (X(0) == 'A')
console.assert (X(25) == 'Z')
console.assert (X(26) == 'AA')
console.assert (X(51) == 'AZ')
console.assert (X(52) == 'BA')
``````

Adding to @SauloAlessandre's answer, this will work for columns up from A-ZZ.

``````=if(column() >26,char(64+(column()-1)/26),) & char(65 + mod(column()-1,26))
``````

I like the answers by @wronex and @Ondra Žižka. However, I really like the simplicity of @SauloAlessandre's answer.

As @Dave mentioned in his comment, it does help to have a programming background, particularly one in C where we added the hex value of 'A' to a number to get the nth letter of the alphabet as a standard pattern.

Answer updated to catch the error pointed out by @Sangbok Lee. Thank you!

I also was looking for a Python version here is mine which was tested on Python 3.6

``````def columnToLetter(column):
character = chr(ord('A') + column % 26)
remainder = column // 26
if column >= 26:
return columnToLetter(remainder-1) + character
else:
return character
``````

Here is a general version written in Scala. It's for a column index start at 0 (it's simple to modify for an index start at 1):

``````def indexToColumnBase(n: Int, base: Int): String = {
require(n >= 0, s"Index is non-negative, n = \$n")
require(2 <= base && base <= 26, s"Base in range 2...26, base = \$base")

def digitFromZeroToLetter(n: BigInt): String =
('A' + n.toInt).toChar.toString

def digitFromOneToLetter(n: BigInt): String =
('A' - 1 + n.toInt).toChar.toString

def lhsConvert(n: Int): String = {
val q0: Int = n / base
val r0: Int = n % base

val q1 = if (r0 == 0) (n - base) / base else q0
val r1 = if (r0 == 0) base else r0

if (q1 == 0)
digitFromOneToLetter(r1)
else
lhsConvert(q1) + digitFromOneToLetter(r1)
}

val q: Int = n / base
val r: Int = n % base

if (q == 0)
digitFromZeroToLetter(r)
else
lhsConvert(q) + digitFromZeroToLetter(r)
}

def indexToColumnAtoZ(n: Int): String = {
val AtoZBase = 26
indexToColumnBase(n, AtoZBase)
}
``````

A comment on my answer says you wanted a script function for it. All right, here we go:

``````function excelize(colNum) {
var order = 1, sub = 0, divTmp = colNum;
do {
divTmp -= order; sub += order; order *= 26;
divTmp = (divTmp - (divTmp % 26)) / 26;
} while(divTmp > 0);

var symbols = "0123456789abcdefghijklmnopqrstuvwxyz";
var tr = c => symbols[symbols.indexOf(c)+10];
return Number(colNum-sub).toString(26).split('').map(c=>tr(c)).join('');
}
``````

This can handle any number JS can handle, I think.

Explanation:

Since this is not base26, we need to substract the base times order for each additional symbol ("digit"). So first we count the order of the resulting number, and at the same time count the number to substract. And then we convert it to base 26 and substract that, and then shift the symbols to `A-Z` instead of `0-P`.

Anyway, this question is turning into a code golf :)

Java Apache POI

``````String columnLetter = CellReference.convertNumToColString(columnNumber);
``````

This will cover you out as far as column AZ:

``````=iferror(if(match(A2,\$A\$1:\$AZ\$1,0)<27,char(64+(match(A2,\$A\$1:\$AZ\$1,0))),concatenate("A",char(38+(match(A2,\$A\$1:\$AZ\$1,0))))),"No match")
``````

Simple way through Google Sheet functions, A to Z.

``````=column(B2) : value is 2
=address(1, column(B2)) : value is \$B\$1
=mid(address(1, column(B2)),2,1) : value is B
``````

It's a complicated way through Google Sheet functions, but it's also more than AA.

``````=mid(address(1, column(AB3)),2,len(address(1, column(AB3)))-3) : value is AB
``````

I'm looking for a solution in PHP. Maybe it will help someone.

``````<?php

\$numberToLetter = function(int \$number)
{
if (\$number <= 0) return null;

\$temp; \$letter = '';
while (\$number > 0) {
\$temp = (\$number - 1) % 26;
\$letter = chr(\$temp + 65) . \$letter;
\$number = (\$number - \$temp - 1) / 26;
}
return \$letter;
};

\$letterToNumber = function(string \$letters) {
\$letters = strtoupper(\$letters);
\$letters = preg_replace("/[^A-Z]/", '', \$letters);

\$column = 0;
\$length = strlen(\$letters);
for (\$i = 0; \$i < \$length; \$i++) {
\$column += (ord(\$letters[\$i]) - 64) * pow(26, \$length - \$i - 1);
}
return \$column;
};

var_dump(\$numberToLetter(-1));
var_dump(\$numberToLetter(26));
var_dump(\$numberToLetter(27));
var_dump(\$numberToLetter(30));

var_dump(\$letterToNumber('-1A!'));
var_dump(\$letterToNumber('A'));
var_dump(\$letterToNumber('B'));
var_dump(\$letterToNumber('Y'));
var_dump(\$letterToNumber('Z'));
var_dump(\$letterToNumber('AA'));
var_dump(\$letterToNumber('AB'));
``````

Output:

``````NULL
string(1) "Z"
string(2) "AA"
int(1)
int(1)
int(2)
int(25)
int(26)
int(27)
int(28)
``````

A function to convert a column index to letter combinations, recursively:

``````function lettersFromIndex(index, curResult, i) {

if (i == undefined) i = 11; //enough for Number.MAX_SAFE_INTEGER
if (curResult == undefined) curResult = "";

var factor = Math.floor(index / Math.pow(26, i)); //for the order of magnitude 26^i

if (factor > 0 && i > 0) {
curResult += String.fromCharCode(64 + factor);
curResult = lettersFromIndex(index - Math.pow(26, i) * factor, curResult, i - 1);

} else if (factor == 0 && i > 0) {
curResult = lettersFromIndex(index, curResult, i - 1);

} else {
curResult += String.fromCharCode(64 + index % 26);

}
return curResult;
}
``````

``````function lettersFromIndex(index, curResult, i) {

if (i == undefined) i = 11; //enough for Number.MAX_SAFE_INTEGER
if (curResult == undefined) curResult = "";

var factor = Math.floor(index / Math.pow(26, i));

if (factor > 0 && i > 0) {
curResult += String.fromCharCode(64 + factor);
curResult = lettersFromIndex(index - Math.pow(26, i) * factor, curResult, i - 1);

} else if (factor == 0 && i > 0) {
curResult = lettersFromIndex(index, curResult, i - 1);

} else {
curResult += String.fromCharCode(64 + index % 26);

}
return curResult;
}

document.getElementById("result1").innerHTML = lettersFromIndex(32);
document.getElementById("result2").innerHTML = lettersFromIndex(6800);
document.getElementById("result3").innerHTML = lettersFromIndex(9007199254740991);``````
``32 --> <span id="result1"></span><br> 6800 --> <span id="result2"></span><br> 9007199254740991 --> <span id="result3"></span>``

In PowerShell:

``````function convert-IndexToColumn
{
Param
(
[Parameter(Mandatory)]
[int]\$col
)
"\$(if(\$col -gt 26){[char][int][math]::Floor(64+(\$col-1)/26)})\$([char](65 + ((\$col-1) % 26)))"
}

``````

Here's a zero-indexed version (in Python):

``````letters = []
while column >= 0:
letters.append(string.ascii_uppercase[column % 26])
column = column // 26 - 1
return ''.join(reversed(letters))
``````

In python, there is the gspread library

``````import gspread