Member Avatar for ivan3510

Hi!

I want to convert numbers to letters, in this way: A=0, B=1, C=2... Z=25, AA=26,AB=27 etc
(something like the names of columns in MS Excel). How to do that? Thanks a lot!

  • 4 Contributors
  • 7 Replies
  • 13K Views
  • 11 Years Discussion Span
  • Latest Post Latest Post by SAbadshah

Dani AI

Generated

What you want is Excel-style column labels, which are a bijective base-26 system (no zero digit). Excel itself is 1-based (A=1, Z=26, AA=27). If you prefer A=0, just subtract 1 on letters->number, or add 1 before number->letters. The snippets below handle both directions and validate input.

function letters_to_index(string $s, bool $zeroBased = true): int {
    $s = strtoupper($s);
    if (!preg_match('/^[A-Z]+$/', $s)) throw new InvalidArgumentException('A-Z only');
    $n = 0;
    for ($i = 0, $L = strlen($s); $i < $L; $i++) {
        $n = $n * 26 + (ord($s[$i]) - 64); // A->1 ... Z->26
    }
    return $zeroBased ? $n - 1 : $n;
}

function index_to_letters(int $n, bool $zeroBased = true): string {
    if ($zeroBased) $n++;
    if ($n < 1) throw new InvalidArgumentException('Index must be >= ' . ($zeroBased ? 0 : 1));
    $out = '';
    while ($n > 0) {
        $n--; // shift to 0..25
        $out = chr(($n % 26) + 65) . $out; // 65 = 'A'
        $n = function_exists('intdiv') ? intdiv($n, 26) : (int) floor($n / 26);
    }
    return $out;
}

Quick checks: letters_to_index('BE') returns 56 for A=0; index_to_letters(26) returns AA. If you need strict Excel behavior, use zeroBased=false. Note that Excel currently stops at column XFD (16384) as per Microsoft specs (Excel specifications and limits). For the math behind this mapping, see bijective numeration (Wikipedia). PHP details: ord, chr, and intdiv.

Recommended Answers

All 7 Replies

Member Avatar for hawx

nice and easy :-)

$alpha = 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');

$i = 5;
echo $alpha[$i]; //will output letter E

Member Avatar for ivan3510

And what if I have letters "BE"? Or three letters?

Member Avatar for Member #120589

This is base26!

Member Avatar for Member #120589 
function getColNo($colLetters){
  $limit = 5; //apply max no. of characters
  $colLetters = strtoupper($colLetters); //change to uppercase for easy char to integer conversion
  $strlen = strlen($colLetters); //get length of col string
  if($strlen > $limit)return "Column too long!"; //may catch out multibyte chars in first pass
  preg_match("/^[A-Z]+$/",$colLetters,$matches); //check valid chars
  if(!$matches)return "Invalid characters!"; //should catch any remaining multibyte chars or empty string, numbers, symbols
  $it = 0; $vals = 0; //just start off the vars
  for($i=$strlen-1;$i>-1;$i--){ //countdown - add values from righthand side
	$vals += (ord($colLetters[$i]) - 64 ) * pow(26,$it); //cumulate letter value
	$it++; //simple counter
  }
  return $vals - 1; //this is the answer
}
//sample usage:
echo getColNo("BX");

The ord and -64 get the value of the letter (ASCII goes from A (65) to Z (90)). SO -64 will give A=1, B=2 etc. You need to multiply this value by the number base (26) to the exponent (0 = rightmost letter, 1= next letter to left... etc), so no change for rightmost letter. next letter to left will be multiplied by 26. Take off 1 at the end to start counting at A = 0.

Perhaps easier method out there?

Edited by Member #120589 because: n/a
Member Avatar for ivan3510

I mean, to do this with words (something like this site:click)
For both: from letters to numbers and from numbers to letters.

Member Avatar for Member #120589

I answered the question you posed I think. Now you want something else? Try the code and you'll that it works to at least 10 characters. I didn't cheat like the site and have Z=0 either!
If you want numbers to letters, I reckon you can backwards engineer my solution and do it yourself.
I shouldn't have posted the whole solution anyway, bit previous of me. Sorry folks.

Member Avatar for SAbadshah

If you want to convert numbers to letters, I believe you can reverse-engineer my solution and carry it out independently.
I was a little premature in posting the complete solution. Sorry, everyone.

Edited by SAbadshah
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.