Is there an Excel function that associates letters with numbers (A = 1, B = 2, C = 3 etc.)?

Question

Is there any Excel function that associates letters to numbers according to alphabetical order (ie, f(A) = 1, f(B) = 2, f(C) = 3 and so on)?

Answer 1

You can use:

CÓDIGO("A") - 64

In the English version:

CODE("A") - 64

As the ASCII code of A is 65, it will return 1, B will return 2, so successively.

Just be careful with lower case letters, because the code is different. If you want a conversion case insensitive need convert to uppercase before:

CÓDIGO(MAIÚSCULA("a")) - 64

In the English version:

CODE(UPPER("a")) - 64

See applied in Libreoffice 5, which is compatible:

Answer 2

According to this article you can use the functions COLUMN and INDIRECT together to pick the numeric index of a string that represents a column.

The function INDIRECT can receive a string representing a cell and returns a reference to this cell.

The function COLUMN receives a reference from a cell and returns the column index.

Then you can concatenate any letter with a number, to be a valid cell reference (e.g..: A would become A1, which is a valid reference), pass this string as a parameter to INDIRECT, which will convert this string to a reference, and pass this reference to COLUMN that will return the desired number.

The advantage of this approach over that of Bacco is that it also works for columns with more than one digit as AA or AZ.

The formula would look like this^*:

=COLUMN(INDIRECT(letter & "1"))

If your Excel is in Portuguese, the functions used should be COL and INDIRECT. The formula would stay^*:

=COL(INDIRETO(letra & "1"))

^{* Substitute letter and letra by a string (eg.: "A" or "AA") or by a reference to a cell containing the letter in the column (ex.: A2).}

Print at Libreoffice:

Source: Convert column Letter to number (Exceljet)