Convert Index to Column A1 Notation and vice-versa

Issue

How would you convert an index, i.e. 53, to a column reference, i.e. BA in GoLang? The table below shows a 2-way expected output for both column & index.

i.e. if you put in 703, you will get AAA. If you put in YOU, you will get 17311.

Example column to index table

Solution

This was an interesting problem to tackle. The solution involves 2 functions:

indexToColumn(int) (string, error) will convert an index to A1 Notation. e.g. 703 to AAA

columnToIndex(string) (int, error) will convert A1 Notation to an index. e.g. BA to 53

Here is the code:

// indexToColumn takes in an index value & converts it to A1 Notation
// Index 1 is Column A
// E.g. 3 == C, 29 == AC, 731 == ABC
func indexToColumn(index int) (string, error) {

    // Validate index size
    maxIndex := 18278
    if index > maxIndex {
        return "", web.Errorf("index cannot be greater than %v (column ZZZ)", maxIndex)
    }

    // Get column from index
    l := "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    if index > 26 {
        letterA, _ := indexToColumn(int(math.Floor(float64(index-1)/26)))
        letterB, _ := indexToColumn(index%26)
        return letterA + letterB, nil
    } else {
        if index == 0 {
            index = 26
        }
        return string(l[index-1]), nil
    }

}

// columnToIndex takes in A1 Notation & converts it to an index value
// Column A is index 1
// E.g. C == 3, AC == 29, ABC == 731
func columnToIndex(column string) (int, error) {

    // Calculate index from column string
    var index int
    var a uint8 = "A"[0]
    var z uint8 = "Z"[0]
    var alphabet = z - a + 1
    i := 1
    for n := len(column) - 1; n >= 0; n-- {
        r := column[n]
        if r < a || r > z {
            return 0, web.Errorf("invalid character in column, expected A-Z but got [%c]", r)
        }
        runePos := int(r-a) + 1
        index += runePos * int(math.Pow(float64(alphabet), float64(i-1)))
        i++
    }

    // Return column index & success
    return index, nil

}

Answered By – James

Answer Checked By – Robin (GoLangFix Admin)

Leave a Reply

Your email address will not be published.