Translate integer to letters and vice versa (e.g. 0 = "A", 26 = "AA", 27 = "AB")

Question

So I have this function:

function toAlpha($data){
    $alphabet =   array('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');
    $alpha_flip = array_flip($alphabet);
    if($data <= 25){
      return $alphabet[$data];
    }
    elseif($data > 25){
      $dividend = ($data + 1);
      $alpha = '';
      $modulo;
      while ($dividend > 0){
        $modulo = ($dividend - 1) % 26;
        $alpha = $alphabet[$modulo] . $alpha;
        $dividend = floor((($dividend - $modulo) / 26));
      } 
      return $alpha;
    }
}

which given a number converts it into character and it works fine

but then I also want a reverse function of this that given any output of this function, return the exact input that was put in to produce that output and I tried this:

function toNum($data){
$alphabet =   array('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');
    $alpha_flip = array_flip($alphabet);
  if(strlen($data) == 1){
          return (isset($alpha_flip[$data]) ? $alpha_flip[$data] : FALSE);
        }
        else if(strlen($data) > 1){
          $num = 1;
          for($i = 0; $i < strlen($data); $i++){
            if(($i + 1) < strlen($data)){
              $num *= (26 * ($alpha_flip[$data[$i]] + 1));
            }
            else{
              $num += ($alpha_flip[$data[$i]] + 1);
            }
          }
          return ($num + 25);
        }
}

but it's not working properly...toAlpha(728) is producing 'aba' but toNum('aba') is producing 1378 rather than 728...

What did I do wrong? How can I fix the reverse function so that it works properly?

Outlier

So you're trying to convert a number base 10 to base 26, is that it? – Eckhardt

What is the original toAlpha function supposed to do? What is the desired behavior if the number is over 25? – Squad

it becomes double letters, triple letters, etc eg: 26 = aa, 27 = ab, etc – Outlier

If 'a' is your 0 then shouldn't aa also be 0? At least, if you're just trying to implement straight-up base-26 using letters rather than numbers. – Bortz

The desired result from both functions is really unclear. Can you explain what the functions should output exactly and by what rules? – Armindaarming

IMO, the best solution in the general case is to use chr() & ord(), e.g. $c = chr(ord('a') - 1 + $n); - you can even factor out ord('a') - 1 (or ord('A') etc.) as a constant here. – Automatism

Brilliant just what I was looking for (with the answer below). This was doing my head in but I now see why. This is NOT as simple base 26 question. z is 25 = highest number in base 26 but zz is NOT 675 but 701. This is because a does not operate as just a zero but it operates as a 1 after z or 25. Unlike 0 a has a positional value as a leading "zero" and effectively turns into base 27. So while 01 means 1 the equivalent aa does not represent 00 but 26 0. So the value formula is 26*27*27... if you see what I mean. Very odd BUT it all works fine. Thanks – Straightlaced

Answer 1

I don't understand at all the logic you're trying to use in that function. What you're trying to do seems very strange (why does 'a' map to zero and yet 'aa' maps to 26?), but this appears to work. (You will want to use some more test cases, I only checked that it gives the correct output for the case 'aba'.)

function toNum($data) {
    $alphabet = array( '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'
                       );
    $alpha_flip = array_flip($alphabet);
    $return_value = -1;
    $length = strlen($data);
    for ($i = 0; $i < $length; $i++) {
        $return_value +=
            ($alpha_flip[$data[$i]] + 1) * pow(26, ($length - $i - 1));
    }
    return $return_value;
}

Bortz

In base 10, the successor of 9 is not 00. So why is aa the successor of z? – Bortz

Excel does this. Maybe @Outlier was trying to figure out something with spreadsheets. – Excursionist

$alphabet = range('a', 'z'); – Spoilsman

@Bortz had me confused but after the single character the second a in aa moves this from base 26 to base 27 as it has a positional value (so a-z PLUS the absence of an a = 27 characters). It is as if 001 101! – Straightlaced

Dumb question time. 1) Why do you need ` $alpha_flip = array_flip($alphabet);` and in the original there is a line $modulo;. Both seem redundant. 2) I asked above but will repeat here: What does the % do? (I have tried Goog, PHP.net, SO, etc but searching with a % is rather entertaining.) – Straightlaced

Answer 2

Shortest way, in PHP >= 4.1.0

$alphabet = range('A', 'Z');

echo $alphabet[3]; // returns D

echo array_search('D', $alphabet); // returns 3

Bags

This will not work with any integer exceeding the length of the alphabet array; e.g. "42". – Sessoms

@Sessoms I posted an answer based on this case that works with integers exceeding the length of the alphabet array. – Brycebryn

For A letter it will print "0" number, it is wrong, so the new code should be : echo array_search($letter, $alphabet)+1; – Faliscan

This has been the correct answer to the wrong question since the question was posted. The answer scores on this page are conveying an inappropriate message to researchers. This answer should not have a positive score because it does not satisfy the brief. – Jetblack

Answer 3

There is a very clever solution by Theriault in the comments of PHPs base_convert function

/**
* Converts an integer into the alphabet base (A-Z).
*
* @param int $n This is the number to convert.
* @return string The converted number.
* @author Theriault
* 
*/
function num2alpha($n) {
    $r = '';
    for ($i = 1; $n >= 0 && $i < 10; $i++) {
    $r = chr(0x41 + ($n % pow(26, $i) / pow(26, $i - 1))) . $r;
    $n -= pow(26, $i);
    }
    return $r;
}
/**
* Converts an alphabetic string into an integer.
*
* @param int $n This is the number to convert.
* @return string The converted number.
* @author Theriault
* 
*/
function alpha2num($a) {
    $r = 0;
    $l = strlen($a);
    for ($i = 0; $i < $l; $i++) {
    $r += pow(26, $i) * (ord($a[$l - $i - 1]) - 0x40);
    }
    return $r - 1;
}

Coil

Works great with any number: num2alpha(25) → "Z", num2alpha(26) → "AA", ... – Peerage

Tip: Use 97 instead of 0x41 and 96 instead of 0x40 to get lowercase letters. Or use strtolower() to convert the result to lowercase – Peerage

Answer 4

From number to alphabet (with A=0, B=1, etc...):

function toAlpha($num){
    return chr(substr("000".($num+65),-3));
}

You can do the same from alphabet to number with the function ord().

Changing 65 with 97, you can obtain the lowercase values.

Idyll

Could use chr(str_pad((65 + $num), 3, '0', STR_PAD_LEFT)) instead of the substr call – Spotty

One note about this: It will only work with numbers 0 - 25 (a-z). toAlpha(26) returns { (when using base 97 for lowercase) - use this if you always expect a 1-character letter – Peerage

Answer 5

Your problems comes from your map. Look at this:

$alpha[0] = 'Alphabet';
for ($i = 'a'; $i<'z'; $i++) {
    $alpha[] = $i;
}
$alpha[26] = 'z';

You can run this as high as you want and your server memory will allow. The PHP is buggy, and (at least on my server) if you use the <= operator:

$alpha[0] = 'Alphabet';
for ($i = 'a'; $i<='z'; $i++) {
    $alpha[] = $i;
}

then it will map all the way to [676]=> string(2) "yz"! You just have to play with it.

I didn't want to map a letter to [0] so I just put a title in there. Obviously you can leave it out if you want 0=>a, 1=>b, etc.

Once the array is correct, the function is trivial.

Guido

one line : range('a', 'z') – Evansville

@Evansville That range will not satisfy anything from aa and beyond. – Jetblack

Answer 6

Using Cyril's answer, I elaborated a bit for a case with more than one letter.

function lettersToNumber($letters){
    $alphabet = range('A', 'Z');
    $number = 0;

    foreach(str_split(strrev($letters)) as $key=>$char){
        $number = $number + (array_search($char,$alphabet)+1)*pow(count($alphabet),$key);
    }
    return $number;
}

A few results for the function are displayed bellow:

lettersToNumber("A"); //returns 1
lettersToNumber("E"); //returns 5
lettersToNumber("Z"); //returns 26
lettersToNumber("AB"); //returns 28
lettersToNumber("AP"); //returns 42
lettersToNumber("CE"); //returns 83

Brycebryn

Answer 7

to convert number to alphacode

for example: 1402 to bax

function number_to_alpha($num, $code)
{   
    $alphabets = array('', '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');

    $division = floor($num / 26);
    $remainder = $num % 26; 

    if($remainder == 0)
    {
        $division = $division - 1;
        $code .= 'z';
    }
    else
        $code .= $alphabets[$remainder];

    if($division > 26)
        return number_to_alpha($division, $code);   
    else
        $code .= $alphabets[$division];     

    return strrev($code);
}

to convert alphacode to number

for example: bax to 1402

function alpha_to_number($code)
{
    $alphabets = array('', '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');

    $sumval = 0;

    $code = strtolower(trim($code));

    $arr = str_split($code);
    $arr_length = count($arr);

    for($i = 0, $j = $arr_length-1; $i < $arr_length; $i++, $j--)
    {
        $arr_value = array_search($arr[$i], $alphabets);
        $sumval = $sumval + ($arr_value * pow(26, $j));
    }

    return $sumval;
}

Macmacabre

Answer 8

I took the 'corrected' original, removed the debug code, and other unnecessary code, modified it so it will work with any number of characters. For example, Greek only has 24 characters.

function toAlpha($number, $alphabet)
    {

        $count = count($alphabet);
        if ($number <= $count) {
            return $alphabet[$number - 1];
        }
        $alpha = '';
        while ($number > 0) {
            $modulo = ($number - 1) % $count;
            $alpha  = $alphabet[$modulo] . $alpha;
            $number = floor((($number - $modulo) / $count));
        }
        return $alpha;
    }

    toAlpha(45,range('a','z'));

And here are some examples of ranges:

// lower greek
$range = ['α', 'β', 'γ', 'δ', 'ε', 'ζ', 'η', 'θ', 'ι', 'κ', 'λ', 'μ', 'ν', 'ξ', 'ο', 'π', 'ρ', 'σ', 'τ', 'υ', 'φ', 'χ', 'ψ', 'ω'];
// upper greek 
$range = ['Α', 'Β', 'Γ', 'Δ', 'Ε', 'Ζ', 'Η', 'Θ', 'Ι', 'Κ', 'Λ', 'Μ', 'Ν', 'Ξ', 'Ο', 'Π', 'Ρ', 'Σ', 'Τ', 'Υ', 'Φ', 'Χ', 'Ψ', 'Ω'];
// georgian 
$range = ['ჵ' => 10000, 'ჰ' => 9000, 'ჯ' => 8000, 'ჴ' => 7000, 'ხ' => 6000, 'ჭ' => 5000, 'წ' => 4000, 'ძ' => 3000, 'ც' => 2000, 'ჩ' => 1000, 'შ' => 900, 'ყ' => 800, 'ღ' => 700, 'ქ' => 600, 'ფ' => 500, 'ჳ' => 400, 'ტ' => 300, 'ს' => 200, 'რ' => 100, 'ჟ' => 90, 'პ' => 80, 'ო' => 70, 'ჲ' => 60, 'ნ' => 50, 'მ' => 40, 'ლ' => 30, 'კ' => 20, 'ი' => 10, 'თ' => 9, 'ჱ' => 8, 'ზ' => 7, 'ვ' => 6, 'ე' => 5, 'დ' => 4, 'გ' => 3, 'ბ' => 2, 'ა' => 1];

Hullda

Stupid question why $number--; not $number;. If this works it looks to be just what I needed. Also what is % LATER sadly this does not run. ` $alpha = $alphabet[$modulo].$alpha;` has an undefined $alpha. The original had $alpha = " " but even with this inserted it still does not work. I did not realise the OP had provided a working script for this. I wasted 20 mins trying to get this working so, sadly, you are getting my first down vote. If you fix it I will remove the down vote. – Straightlaced

@Straightlaced I just ran the code. The example works fine, but your right in that there is issues with the starting point of the number. There is also a PHP notice that is thrown, but this does not affect the running of it. – Hullda

@Straightlaced I have updated the example to what I ultimately ended up using. – Hullda

Thanks I tweaked another version and have, as per usual, hacked a solution together (it works I know not why, but it works!) Could u possibly explain the -- and the % pls. – Straightlaced

@Straightlaced php.net/manual/en/language.operators.increment.php php.net/manual/en/language.operators.arithmetic.php – Hullda

thanks for that. It wd be nice if PHP.net could intercept a query like "What is a %". Not rocket science but without knowing how to look ... I could not find. Much appreciated. Oh blimey -- - I have used ++ a hundred times. Age is slowly turning my brain to mush. As to % being modulo well for us part timers I like clarity over br3evity but, as I said, I am a part timer. – Straightlaced

Answer 9

Here is an implementation that can use very high column values (more than two letters). Spreadsheets will use either 0 or 1 as the first value, so be sure to specify the second parameter $indexStart as 1 if you are using Excel, or 0 (the default) if using Google Sheets.

letterToColumnNumber()

/**
 * Converts a spreadsheet column letter (e.g., "A") to a column number (e.g., 1).
 * Can handle multi-letter columns, including Google's max column ZZZ (18277) 
 * and Excel's XFD (16384).
 * 
 * @param string $letters The column letters to convert.
 * @param int $indexStart Specifies if offsets begin with zero (Google Sheets) or one (Excel).
 * @return int The column number.
 */
function letterToColumnNumber(string $letters, int $indexStart = 0): int {
    $length = strlen($letters);
    $number = 0;
    $pow = 1;

    for ($i = $length - 1; $i >= 0; $i--) {
        // calculate the value of each letter and add to the total
        $number += (ord($letters[$i]) - ord('A') + 1) * $pow;
        $pow *= 26;
    }

    return $number - (1 - $indexStart);
}

echo letterToColumnNumber("ABC"); // returns 730
echo letterToColumnNumber("ABC", 1); // returns 731

Additionally, if you wish to convert a coordinate reference (such as A1:B2) into a coordinate array (like [[0,0],[1,1]]), we can use this letterToColumnNumber function with the following function to achieve that:

rangeStringToRangeArray()

/**
 * Converts a spreadsheet range string like "A1:B2" into a coordinate array like [[0,0],[1,1]]
 * 
 * @param string $range The range string to convert.
 * @param int $indexStart Specifies if the index begins with zero (Google Sheets) or one (Excel).
 * @return array The coordinate array.
 */
function rangeStringToRangeArray(string $range, int $indexStart = 0): array {
    // Split the range into start and end parts
    [$startCell, $endCell] = explode(':', $range);

    // Separate letters and numbers for each part
    preg_match('/([A-Z]+)([0-9]+)/', $startCell, $startMatches);
    preg_match('/([A-Z]+)([0-9]+)/', $endCell, $endMatches);

    // Convert column letters to numbers
    $startColumn = letterToColumnNumber($startMatches[1], $indexStart);
    $endColumn = letterToColumnNumber($endMatches[1], $indexStart);

    // Convert row numbers
    $startRow = intval($startMatches[2]) - (1 - $indexStart); // Adjust for zero-based indexing
    $endRow = intval($endMatches[2]) - (1 - $indexStart);

    return [[$startColumn, $startRow], [$endColumn, $endRow]];
}

echo json_encode(rangeStringToRangeArray('A1:B2')); // returns [[0,0],[1,1]]
echo json_encode(rangeStringToRangeArray('A1:B2', 1)); // returns [[1,1],[2,2]]

If you wish to reverse either process, the following two functions together will achieve that result:

columnNumberToLetter()

/**
 * Converts a spreadsheet column number like 1 into a column letter reference like "A"
 * Can handle multi-letter columns including Google's max column 18277 (ZZZ) and 
 * Excel's 16384 (XFD).
 * 
 * @param int $columnNumber The column number to convert.
 * @param int $indexStart Specifies if the index begins with zero (Google Sheets) or one (Excel).
 * @return string The column letter.
 */
function columnNumberToLetter(int $columnNumber, int $indexStart = 0): string {
    $columnLetter = '';
    $columnNumber -= $indexStart;
    while ($columnNumber >= 0) {
        $modulus = $columnNumber % 26;
        $columnLetter = chr(65 + $modulus) . $columnLetter;
        $columnNumber = intdiv($columnNumber, 26) - 1;
    }
    return $columnLetter;
}

echo columnNumberToLetter(730); // returns "ABC"
echo columnNumberToLetter(731, 1); // returns "ABC"

rangeArrayToRangeString()

/**
 * Converts a spreadsheet coordinate array like [[0,0],[1,1]] into a range string like "A1:B2"
 * 
 * @param array $range The coordinate array to convert.
 * @param int $indexStart Specifies if the index begins with zero (Google Sheets) or one (Excel).
 * @return string The range string.
 */
function rangeArrayToRangeString(array $range, int $indexStart = 0): string {
    // Convert column numbers to letters
    $startColumn = columnNumberToLetter($range[0][0], $indexStart);
    $endColumn = columnNumberToLetter($range[1][0], $indexStart);

    // Convert row indices to spreadsheet row numbers
    $startRow = $range[0][1] + (1 - $indexStart);
    $endRow = $range[1][1] + (1 - $indexStart);

    // Combine to form range string
    return $startColumn . $startRow . ':' . $endColumn . $endRow;
}

echo rangeArrayToRangeString([[0,0],[1,1]]); // returns "A1:B2"
echo rangeArrayToRangeString([[1,1],[2,2]], 1); // returns "A1:B2"

This kind of code can be useful when you're integrating with Google Sheets API. E.g., if you want to get a reference for all the frozen rows and columns, which are provided as integers instead of a cell reference.

Please note that I have included exactly ZERO error checking in these functions, so you'll want to do that.

Icebreaker

fyi: intval($columnNumber / 26) can be simplified to intdiv($columnNumber, 26) ...this way there is only one operation instead of two. This is a good, readable, explained answer posted on a very suitable (old) page. Good. Job. Do more of this please ... and tell others that this is how you contribute on Stack Overflow. – Jetblack

This is the difference between dumping snippets and actually trying to educate/empower people. – Jetblack

To be fair, the asker is actually having trouble with reversing the functionality. Please include a letters-to-numbers function in your answer so that it satiiies the asked question. – Jetblack

@Jetblack Thank you for the great feedback! I've updated my answer accordingly. Very good catch with intdiv. Those are the kinds of optimizations I too often miss. I appreciate the encouragement... I didn't think I'd see that kind of thing on SO anymore. – Icebreaker

For your consideration 3v4l.org/679Dp – Jetblack

@Jetblack wow, that's pretty impressive, especially rangeStringToRangeArray. To be honest, I haven't used sscanf in a long time, but it's kind of perfect for this solution. And it's much more readable. – Icebreaker

Answer 10

Here is a fix to original function toAlpha. It is not working for toAlpha(27)

function toAlpha($n,$case = 'upper'){
    $alphabet   = array('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');
    $n = $n-1;
    Util::error_log('N'.$n);
    if($n <= 26){
        $alpha =  $alphabet[$n-1];
    } elseif($n > 26) {
        $dividend   = ($n);
        $alpha      = '';
        $modulo;
        while($dividend > 0){
            $modulo     = ($dividend - 1) % 26;
            $alpha      = $alphabet[$modulo].$alpha;
            $dividend   = floor((($dividend - $modulo) / 26));
        }
    }

    if($case=='lower'){
        $alpha = strtolower($alpha);
    }
    Util::error_log("**************".$alpha);
    return $alpha;

}

Ascot

Answer 11

i do this stuff to use fix char lenght before int parameter A = 0 ,Z = 25

function returnUIDfixChar(int $inc,int $charlength=3,int $min_input_length=3,string $prefix=''):string {
$alpha = range('A','Z');
$max_alpa_int = count($alpha)-1;
$over = 0;
$output_prefix = '';
$first_split = 0;
if(strlen($inc)>$min_input_length)
{
    $first_split = substr((string) $inc,0,strlen($inc)-$min_input_length);
}
$second_split = substr((string) $inc,0,$min_input_length);
for($i=0;$i<$charlength;$i++)
{
    $temp_over = (float) $first_split - $max_alpa_int;
    if($temp_over>0)
    {
        $output_prefix = $alpha[$max_alpa_int].$output_prefix;
        $first_split -= $max_alpa_int;
    }
    elseif($first_split<$max_alpa_int && $first_split>0)
    {
        $output_prefix = $alpha[$first_split].$output_prefix;
        $first_split -= $first_split;
    }
    else
    {
        $output_prefix = $alpha[0].$output_prefix;
    }
    $over = $first_split;
}
if(strlen($second_split)<$min_input_length)
{
    for($i=0;$i<$min_input_length-strlen($second_split);$i++)
    {
        $second_split = '0'.$second_split;
    }
}
return $output_prefix.($first_split>0?$first_split:'').$second_split;

}

Merwin

This wall of code is missing its educational explanation. – Jetblack

Answer 12

If the numbers start at 1:

function num2alpha($n) {
    $r = '';
    while( $n > 0 ) {
        $r = chr( ($n-1) % 26 + 0x41 ) . $r;
        $n = floor( ($n-1) / 26 );
    }
    return $r;
}

Conveyancing