[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

Let's compare these three.

Decimal number 26-ary A1 notation
0000 AAAA ----
0001 AAAB ---A
0002 AAAC ---B
... ... ...
0025 AAAZ ---Y
0026 AABA ---Z
0027 AABB --AA
0028 AABC --AB
... ... ...

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.

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

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.

Recommended Posts

A1 notation and 26-ary number
Make a given number of seconds into hours, minutes and seconds
Read and write a file
Write and read a file
Difference between ps a and ps -a
Random number seed fixed in TensorFlow and Numpy A little different
Python a + = b and a = a + b are different
[python] week1-3: Number type and operation
It's a prime number ... Counting prime numbers ...
Pseudo-random number generation and random sampling
a () and a.__ call__ () are not equivalent
Create a 1MByte random number file
[Python] return A [or / and] B
C> Enter a number> fgets () / sscanf ()
Create a correspondence table between EC number and Uniprot entry from enzyme.dat