Convert Excel column coordinates to integer position in PHP

Question

I need to convert Excel coordinates (for example "AD45") into X=30 and Y=45 positions in integers.

I have this snippet of PHP code:

/**
 * @param  String $coordinates
 *
 * @return array
 */
public function getCoordinatesPositions($coordinates) {
    $letters = preg_replace('/[^a-zA-Z]/', '', $coordinates);
    $numbers = preg_replace('/[^0-9]/', '', $coordinates);

    $letters = strtoupper($letters);

    $columnCoordinate = 0;
    $alphabetIterate = 0;

    $alphabetRange = range('A', 'Z');
    $alphabetCount = count($alphabetRange);

    $splittedLetters = str_split($letters);
    $lettersCount = count($splittedLetters);

    $i = 1;

    if ($lettersCount === 1) {
        $columnCoordinate = array_search($splittedLetters[0], $alphabetRange) + 1;

    } else {
        foreach ($splittedLetters as $letter) {
            if ($i !== $lettersCount) {
                $position = (array_search($letter, $alphabetRange) + 1) * $alphabetCount;
            } else {
                $position = (array_search($letter, $alphabetRange) + 1);
            }

            $columnCoordinate += $position;

            $i++;
        }
    }

    return array('column' => $columnCoordinate, 'row' => $numbers);
}

My problem is, that this function is not returning correct column value if you pass coordinates with 3 or more letters ("ABC45"). And my colleague said, that this algorithm is also poor performance.

Do you have any ideas for simpler and better performance algorithm? Thank you.

score 2 · Answer 1 · edited May 23 '17 at 12:15

In principle the algorithm is fine. You can simplify it and make it more general this way:

function getCoordinatesPositions($coordinates) {

    $letters = preg_replace('/[^a-zA-Z]/', '', $coordinates);
    $numbers = preg_replace('/[^0-9]/', '', $coordinates);

    $letters = strtoupper($letters);

    $alphabetRange = range('A', 'Z');
    $alphabetCount = count($alphabetRange);

    $splittedLetters = str_split($letters);
    $lettersCount = count($splittedLetters);

    $columnCoordinate = 0;
    $i = 1;
    foreach ($splittedLetters as $letter) {
       $columnCoordinate += (array_search($letter, $alphabetRange) + 1) * pow($alphabetCount,  $lettersCount - $i);
       $i++;
    }

    return array('column' => $columnCoordinate, 'row' => intval($numbers));
}

var_dump(getCoordinatesPositions("ABC456"));

For PHPExcel see PHPExcel how to get column index from cell.

score 1 · Accepted Answer · answered May 28 '16 at 15:11

The @Axel Richter's answer is a good solution and works fine, but it may be improved to:

Secure against wrong coordinates.
Reduce code.
And probably increase performance.

Here is the proposed version:

function getCoordinatesPositions($coordinates) {
    if (preg_match('/^([a-z]+)(\d+)$/i', $coordinates, $matches)) {
        $level = strlen($matches[1]);
        $matches[1] = array_reduce(
            str_split(strtoupper($matches[1])),
            function($result, $letter) use (&$level) {
                return $result + (ord($letter) - 64) * pow(26, --$level);
            }
        );
        return array_splice($matches, 1);
    }
    // (returns NULL when wrong $coordinates)
}

Using the initial preg_match() ensures to avoid working with wrong coordinates, and directly extracts the column part into $matches['1'].

Now the main improvement is to use ord($letter) to compute the letter's individual value: it avoids creating a temporary array of range('A', 'Z'), and simplifies the evaluation.

Then array_reduce() allows more compact processing of the column part, which is modified in situ, so the final return is also simplified as a simple part of the intermediary $matches.

Simple and few lines of code. Works nicely. I will use this one. Thank you. :) — Michal Loksik, May 28 '16 at 18:37

Convert Excel column coordinates to integer position in PHP

2 Answers2