Converting Excel Column Names to Numbers in Rust

January 9, 2025

When working with Excel files programmatically, converting column names (like “A”, “B”, “C”, …, “Z”, “AA”, “AB”, etc.) to their corresponding numeric values (0, 1, 2, …, 25, 26, 27, etc.) is a common task. Rust, with its performance, safety, and expressiveness, is an excellent choice for implementing such functionality.

In this blog post, we’ll build a clean, efficient, and robust Rust program that:

Converts Excel column names to numbers.
Handles invalid inputs gracefully.
Supports both uppercase and lowercase inputs.
Includes comprehensive tests to ensure correctness.

By the end of this post, you’ll have a complete Rust program that you can use or extend for your own projects.

The Problem

Excel uses a base-26 numbering system for its columns, where:

“A” corresponds to 0
“B” corresponds to 1
“Z” corresponds to 25
“AA” corresponds to 26
“AB” corresponds to 27
…

Our task is to write a Rust function that takes a column name as input and returns its corresponding numeric value. Additionally, we’ll handle potential errors, such as invalid characters or empty inputs.

The Solution

We’ll break the solution into the following steps:

Define an Error type to handle invalid inputs and implement PartialEq for testing.
Write a function to convert a column name to a number.
Create a CLI program that accepts column names in a loop.
Write tests to ensure the function works correctly.

Step 1: Define the `Error` Type

To handle invalid inputs, we’ll define a custom Error enum. For testing, we’ll also implement the PartialEq trait to allow equality comparisons.

#[derive(Debug, PartialEq)]
enum Error {
    EmptyInput,
    InvalidCharacter(char),
    InputTooLong,
}

Explanation:

EmptyInput: The input string is empty.
InvalidCharacter(char): The input contains a non-alphabetic character.
InputTooLong: The input string is too long to be a valid Excel column name.
PartialEq: Allows comparing Error values in tests.

Step 2: Write the Conversion Function

Next, we’ll write the column_name_to_number function. This function will take a column name as input and return a Result<u32, Error>. If the input is valid, it will return the corresponding number; otherwise, it will return an appropriate error.

fn column_name_to_number(column_name: &str) -> Result<u32, Error> {
    if column_name.is_empty() {
        return Err(Error::EmptyInput);
    }

    if column_name.len() > 3 {
        return Err(Error::InputTooLong);
    }

    let mut result = 0;
    for ch in column_name.chars() {
        if !ch.is_ascii_alphabetic() {
            return Err(Error::InvalidCharacter(ch));
        }
        let value = ch.to_ascii_uppercase() as u32 - 'A' as u32 + 1; // Add 1 here
        result = result * 26 + value;
    }
    Ok(result - 1) // Subtract 1 at the end
}

Explanation:

We first check if the input is empty and return Error::EmptyInput if it is.
We then check if the input is too long (more than 3 characters) and return Error::InputTooLong if it is.
We initialize result to 0 and iterate over each character in the input string.
For each character, we check if it is alphabetic. If not, we return Error::InvalidCharacter.
If the character is valid, we calculate its value as ch.to_ascii_uppercase() as u32 - 'A' as u32 + 1 (since “A” corresponds to 1 in Excel’s system).
We update result by multiplying it by 26 and adding the current character’s value.
Finally, we subtract 1 from the result to make it zero-based and return Ok(result - 1).

Step 3: Create the CLI Program

Now, let’s create a CLI program that accepts column names in a loop and displays their corresponding numbers. The program will also handle errors gracefully.

use std::io::{self, Write};

fn main() {
    loop {
        print!("Enter column name (or 'exit' to quit): ");
        io::stdout().flush().unwrap();

        let mut input = String::new();
        io::stdin().read_line(&mut input).expect("Failed to read line");

        let input = input.trim();
        if input.eq_ignore_ascii_case("exit") {
            break;
        }

        match column_name_to_number(input) {
            Ok(number) => println!("The column number for '{}' is {}", input, number),
            Err(Error::EmptyInput) => println!("Error: Input is empty."),
            Err(Error::InvalidCharacter(ch)) => println!("Error: Invalid character '{}' in input.", ch),
            Err(Error::InputTooLong) => println!("Error: Input is too long."),
        }
    }
}

Explanation:

The program runs in a loop, prompting the user to enter a column name.
If the user types “exit” (case-insensitive), the program terminates.
The program trims the input and passes it to column_name_to_number.
It uses a match statement to handle the result and display either the column number or an appropriate error message.

Step 4: Write Tests

Finally, let’s write tests to ensure our function works correctly for both valid and invalid inputs.

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn test_column_name_to_number() {
        assert_eq!(column_name_to_number("A"), Ok(0));
        assert_eq!(column_name_to_number("B"), Ok(1));
        assert_eq!(column_name_to_number("Z"), Ok(25));
        assert_eq!(column_name_to_number("AA"), Ok(26));
        assert_eq!(column_name_to_number("AB"), Ok(27));
        assert_eq!(column_name_to_number("AZ"), Ok(51));
        assert_eq!(column_name_to_number("BA"), Ok(52));
        assert_eq!(column_name_to_number("ZZ"), Ok(701));
        assert_eq!(column_name_to_number("AAA"), Ok(702));
        assert_eq!(column_name_to_number("a"), Ok(0)); // lowercase input
        assert_eq!(column_name_to_number("z"), Ok(25)); // lowercase input
    }

    #[test]
    fn test_column_name_to_number_errors() {
        assert_eq!(column_name_to_number(""), Err(Error::EmptyInput));
        assert_eq!(column_name_to_number("A1"), Err(Error::InvalidCharacter('1')));
        assert_eq!(column_name_to_number("A!"), Err(Error::InvalidCharacter('!')));
        assert_eq!(column_name_to_number("AAAA"), Err(Error::InputTooLong));
    }
}

Explanation:

We test valid inputs to ensure they return the correct column number.
We test invalid inputs to ensure they return the appropriate error.

Full Code

Here’s the complete Rust program:

use std::io::{self, Write};

#[derive(Debug, PartialEq)]
enum Error {
    EmptyInput,
    InvalidCharacter(char),
    InputTooLong,
}

fn column_name_to_number(column_name: &str) -> Result<u32, Error> {
    if column_name.is_empty() {
        return Err(Error::EmptyInput);
    }

    if column_name.len() > 3 {
        return Err(Error::InputTooLong);
    }

    let mut result = 0;
    for ch in column_name.chars() {
        if !ch.is_ascii_alphabetic() {
            return Err(Error::InvalidCharacter(ch));
        }
        let value = ch.to_ascii_uppercase() as u32 - 'A' as u32 + 1; // Add 1 here
        result = result * 26 + value;
    }
    Ok(result - 1) // Subtract 1 at the end
}

fn main() {
    loop {
        print!("Enter column name (or 'exit' to quit): ");
        io::stdout().flush().unwrap();

        let mut input = String::new();
        io::stdin().read_line(&mut input).expect("Failed to read line");

        let input = input.trim();
        if input.eq_ignore_ascii_case("exit") {
            break;
        }

        match column_name_to_number(input) {
            Ok(number) => println!("The column number for '{}' is {}", input, number),
            Err(Error::EmptyInput) => println!("Error: Input is empty."),
            Err(Error::InvalidCharacter(ch)) => println!("Error: Invalid character '{}' in input.", ch),
            Err(Error::InputTooLong) => println!("Error: Input is too long."),
        }
    }
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn test_column_name_to_number() {
        assert_eq!(column_name_to_number("A"), Ok(0));
        assert_eq!(column_name_to_number("B"), Ok(1));
        assert_eq!(column_name_to_number("Z"), Ok(25));
        assert_eq!(column_name_to_number("AA"), Ok(26));
        assert_eq!(column_name_to_number("AB"), Ok(27));
        assert_eq!(column_name_to_number("AZ"), Ok(51));
        assert_eq!(column_name_to_number("BA"), Ok(52));
        assert_eq!(column_name_to_number("ZZ"), Ok(701));
        assert_eq!(column_name_to_number("AAA"), Ok(702));
        assert_eq!(column_name_to_number("a"), Ok(0)); // lowercase input
        assert_eq!(column_name_to_number("z"), Ok(25)); // lowercase input
    }

    #[test]
    fn test_column_name_to_number_errors() {
        assert_eq!(column_name_to_number(""), Err(Error::EmptyInput));
        assert_eq!(column_name_to_number("A1"), Err(Error::InvalidCharacter('1')));
        assert_eq!(column_name_to_number("A!"), Err(Error::InvalidCharacter('!')));
        assert_eq!(column_name_to_number("AAAA"), Err(Error::InputTooLong));
    }
}

Running the Program

First create rust project using cargo new excel_column_to_number and then replace the src/main.rs with the above code.

Then run the program using cargo run.

Conclusion

In this blog post, we’ve built a robust Rust program that converts Excel column names to numbers. The program handles invalid inputs gracefully, supports both uppercase and lowercase inputs, and includes comprehensive tests to ensure correctness. By implementing the PartialEq trait for the Error type, we’ve made it easy to write and run tests.

This program demonstrates Rust’s strengths in safety, performance, and expressiveness, making it an excellent choice for real-world tasks like this.

Converting Excel Column Names to Numbers in Rust

The Problem

The Solution

Step 1: Define the Error Type

Explanation:

Step 2: Write the Conversion Function

Explanation:

Step 3: Create the CLI Program

Explanation:

Step 4: Write Tests

Explanation:

Full Code

Running the Program

Conclusion

Step 1: Define the `Error` Type