How can I convert an integer to A1 notation?

Question

I am trying to create a method that will find the next column after a given column. For example:

input: A
output: B

It seems quite simple at first. I was just going to use the following method:

public static char nextLetter(char c) {
    c++;
    return c;
}

The problem arises when you get past column Z. In Google Sheets, after column Z, column names are two letters, then three, etc. So after column Z comes AA, after AZ comes BA, after ZZ comes AAA, etc. My next thought was to first figure out the column position in terms of index. So column AA would 27, BA 52, etc.

Finding the index of the column is not the problem I'm facing right now. I need to figure out how to convert that index to the corresponding column name. I was going to try the following method, but I realized that it is also limited to A-Z:

public static char getLetter(int index) {
    return (char) (index + 64);
}

At this point, I am thinking that a recursive method is needed. However, I cannot figure out to set it up. This is as far as I got:

private static void getNotation(int size) {
    int divided = size / 26;
    int remainder = size % 26;

    String notation = "";

    while (divided % 26 > 0) {
        // Here is where the need for a recursive method comes in
    }

}

Does anyone know a good way to convert an integer (index) to the corresponding column name?

EDIT

I just found a very helpful resource on Github which deals with hexavigesimals: https://gist.github.com/pinguet62/9817978

Homotaxis

Answer 1

You can do it as follows:

public class Main {
    public static void main(String[] args) {
        System.out.println(getNextColumn("A"));
        System.out.println(getNextColumn("Z"));
        System.out.println(getNextColumn("AA"));
        System.out.println(getNextColumn("AZ"));
        System.out.println(getNextColumn("ZA"));
        System.out.println(getNextColumn("ZZ"));
        System.out.println(getNextColumn("AAA"));
        System.out.println(getNextColumn("ABA"));
        System.out.println(getNextColumn("ABZ"));
        System.out.println(getNextColumn("ZZZ"));
    }

    static String getNextColumn(String column) {
        column = column.toUpperCase();
        StringBuilder sb = new StringBuilder();
        boolean allZ = true;
        for (int i = 0; i < column.length(); i++) {
            if (!(column.charAt(i) == 'Z')) {
                allZ = false;
                break;
            }
        }
        if (allZ) {
            for (int i = 0; i <= column.length(); i++) {
                sb.append('A');
            }
            return sb.toString();
        }
        boolean charAfterZ = false;
        int indexOfZ = 0;
        for (int i = 0; i < column.length() - 1; i++) {
            if (column.charAt(i) == 'Z') {
                charAfterZ = true;
                indexOfZ = i;
                break;
            }
        }
        if (charAfterZ) {
            sb.append(column.substring(0, indexOfZ + 1) + (char) (column.charAt(indexOfZ + 1) + 1));
            if (column.length() > indexOfZ + 2) {
                sb.append(column.substring(indexOfZ + 1));
            }
            return sb.toString();
        }

        char lastChar = column.charAt(column.length() - 1);

        if (lastChar == 'Z') {
            sb.append(column.substring(0, column.length() - 2) + (char) (column.charAt(column.length() - 2) + 1) + ""
                    + 'A');
        } else {
            if (column.length() > 1) {
                sb.append(column.substring(0, column.length() - 1) + ""
                        + (char) (column.charAt(column.length() - 1) + 1));
            } else {
                sb.append((char) (column.charAt(column.length() - 1) + 1));
            }
        }
        return sb.toString();
    }
}

Output:

B
AA
AB
BA
ZB
AAA
AAB
ABB
ACA
AAAA

Hwahwan

I like this answer because it saves me the trouble of getting the column index. +1 – Homotaxis

Answer 2

I created an example:

class Test {
    static char[] alphabet = { 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I',
                               'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R',
                               'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z' };

    private static String indexToColumnName(int i) {
        if (i >= alphabet.length) {
            return indexToColumnName((int)Math.floor(i / alphabet.length) - 1)
              + indexToColumnName(i % alphabet.length);
        }
        return Character.toString(alphabet[i]);
    }

    public static void main(String args[]) {
        for (int i = 0; i <= 800; ++i) {
            System.out.println(i + ": " + indexToColumnName(i));
        }
    }
}

The above will yield something like:

0: A
1: B
2: C
3: D
...
24: Y
25: Z
26: AA
27: AB
...
700: ZY
701: ZZ
702: AAA
703: AAB
...

In this case, it's zero-index, but that you could easily change yourself.

Interceptor

This works beautifully! If you have the time to explain what exactly you are doing in your answer, I think that would improve it's long-term value. Nevertheless, I like this answer. It's neat and clean and it works. +1 – Homotaxis

Answer 3

Try it

private static String getNotation(int size) {
        String result = "";
        int q = 0; // How many characters does the notation consist of?
        int f1 = 0;
        int f2 = 0;
        while (f2 < size) {
            q += 1;
            f1 = f2;
            f2 += Math.pow(26, q);
        }

        size -= f1;
        size--;
        for (int i = 0; i < q; i++) {
            result = (char) (size % 26 + 65) + result;
            size /= 26;
        }

        return result;
    }

Thebes

Thank you for your answer! It works works nicely. If you have the time to explain what exactly you are doing in your answer, I think that would improve it's long-term value. I am especially curious what you are doing with your q variable... I wish it was possible to accept two answers, but alas, it is not. +1 – Homotaxis

Answer 4

I suppose like hexa (base 16), you can work with a base 26. May be try to use a base 10 to base X converter. The main problem is that in this case after "number" "Z" you have number "AA" like if after number 9 you will have 00. Or in hexa if after F you have 00 instead of 10.

I tried a lot by myself but finally found this topic : How to convert a column number (eg. 127) into an excel column (eg. AA)

In Java :

private static String getNotation(int size) {
    str = "";
    int r;
    while(size >0) {
        r = (size-1) % 26;
        str = getLetter(r+1)+ str;
        size = (size-r) /26 ;
    }
    return str;
}

It's seem the solution is just the -1 near the modulo, if you remove it you will have A considered as 0 and so after Z there will be BA like a decimal to base 26 convertor.

Sadiesadira

That is really smart! You are right! This is a lot like a base 26 number system. You also provided a working example. +1 – Homotaxis

Answer 5

yesterday I needed to convert an integer to A1 notation in Go, so I made this recursive function (ported to java to match question tags):

  public static String indexToA1Notation(int column) {
    if (column <= 'Z' - 'A') {
      return Character.toString('A' + column);       
    }
    return String.format("%s%s", indexToA1Notation((column/26)-1), Character.toString('A'+column%26));
  }

Electra