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.

Answer 1

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.