[JAVA] A1 notation and 26-ary number

A1 notation

In spreadsheet software, the row number is represented by a normal decimal number, and the column number is represented by a character string that is a combination of uppercase letters starting with ʻA. This notation is called A1 notation because the top left cell is represented as ʻA1 in this method.

The column number uses 26 kinds of symbols, so isn't it a 26-ary number?

There are 26 types of uppercase alphabets, so isn't it actually a 26-ary number? However, it has different properties from general n-ary numbers.

If you touch it somehow, you may fall into an unexpected pitfall. It's easy, but I thought about the difference.

Comparison of column numbers in decimal, 26, and A1 notation

• Decimal number using the 0-9 symbol
• A 26-ary number using the A-Z symbol
• Column number in A1 notation

Let's compare these three.

In general n-ary numbers, there is a symbol corresponding to zero. It is "0" in decimal and "A" in 26.

On the other hand, there is no symbol corresponding to zero in the column number in A1 notation. The table above uses "-" instead to represent it. If you replace "-" with "A", it will be difficult to handle because the second digit starts with "A" when it is carried up to two digits.

In 26-ary numbers, the most significant digit is never "A" (unless it is zero), so that's not a problem.

Therefore, the column number in A1 notation implicitly has the digit number information, which probably has an essential meaning. And, by the amount of that information, the ability to express is higher even with the same number of digits than the 26 base number.

Conversion from column number in A1 notation to decimal number

\begin{equation}
f(d) = 
\begin{cases}
    0, & d = \mbox{'A'} \\
    1, & d = \mbox{'B'} \\
    2, & d = \mbox{'C'} \\
    ... \\
    25, & z = \mbox{'Z'} \\
\end{cases}
\end{equation}

If you define the function $ f $ in this way, the conversion of 26-ary numbers to decimal numbers will be

\Sigma_{i=0}^{D_{max}}{f(D[i])*26^i}

Can be expressed as. However, $ D $ is a 26-ary number, $ D [i] $ represents the i-th digit character from the right, and $ D {max} $ represents the maximum digit number of $ D $. .. Well, this is a general radix conversion calculation, isn't it?

To convert a column number in A1 notation to a decimal number, each digit starts with 1 instead of zero, so by adding +1 to the value of $ f $,

\Sigma_{i=0}^{D_{max}}{(f(D[i])+1)*26^i}

Can be expressed as.

• In the case of 26-ary numbers, the maximum digit number is not always necessary, and if you know in advance that there is no larger number (a value such as 31 in a 32-bit integer), that is fine.

It turns out that when represented by N characters, the number that can be represented differs by $ \ Sigma_ {i = 0} ^ {N-1} {26 ^ i} $.

Implementation example (Java)

Let's implement the conversion from 26-ary numbers and A1 format column numbers to decimal numbers in Java.

public class Main {
    public static int f(char d) {
        return d - 'A';
    }

    public static int fromBase26Num(String base26Num) {
        int base10Num = 0;
        for (int i = 0; i < base26Num.length(); i++) {
            base10Num += (f(base26Num.charAt(base26Num.length() - 1 - i))) * Math.pow(26, i);
        }
        return base10Num;
    }

    public static int fromA1ColNum(String a1ColNum) {
        int base10Num = 0;
        for (int i = 0; i < a1ColNum.length(); i++) {
            base10Num += (f(a1ColNum.charAt(a1ColNum.length() - 1 - i)) + 1) * Math.pow(26, i);
        }
        return base10Num;
    }

    public static void main(String[] args) {
        System.out.println("[0 is displayed]");
        System.out.println(fromBase26Num("AAAA"));

        System.out.println("[1 is displayed]");
        System.out.println(fromBase26Num("AAAB"));
        System.out.println(fromA1ColNum("A"));

        System.out.println("[27 is displayed]");
        System.out.println(fromBase26Num("AABB"));
        System.out.println(fromA1ColNum("AA"));
    }
}

Execution result

[0 is displayed]
0
[1 is displayed]
1
1
[27 is displayed]
27
27

OK

Summary

• A1 format column numbers are not common 26-ary numbers
• With N digits, there are more numbers that can be expressed by $ \ Sigma_ {i = 0} ^ {N-1} {26 ^ i} $ than 26-ary numbers.
• Be careful when implementing!

I didn't write it here, but converting a decimal number to an A1 format column number is more troublesome. https://github.com/ttk1/A1Util I don't know if it's in demand, but I've put the implementation here. (Please forgive that the variable name and function name are not cool!)

Postscript

Converting decimal numbers to A1 format column numbers is more cumbersome.

However, as commented by @ saka1029, it was less troublesome than I expected.