Is there an Excel function that associates letters with numbers in alphabetical order (that is, f(A) = 1, f(B) = 2, f(C) = 3
and so on)?
Is there an Excel function that associates letters with numbers in alphabetical order (that is, f(A) = 1, f(B) = 2, f(C) = 3
and so on)?
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 on.
Just be careful with lowercase letters, because the code is different. If you want a case insensitive conversion you need to convert to capital before:
CÓDIGO(MAIÚSCULA("a")) - 64
In the English version:
CODE(UPPER("a")) - 64
See applied in LibreOffice 5, which is compatible:
According to this article you can use the COLUMN and INDIRECT together to get the numeric index of a string that represents a column.
The INDIRECT function can receive a string representing a cell and returns a reference to this cell.
The COLUMN function receives a a cell and returns the column index.
Then you can concatenate any letter with a number, to be a valid cell reference (eg, 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 Bacco is that it also works for columns with more than one digit as AA
or AZ
.
The formula would look like * :
=COLUMN(INDIRECT(letter & "1"))
If your Excel is in Portuguese, the functions used should be COL and INDIRECT . The formula would be * :
=COL(INDIRETO(letra & "1"))
* Replace letter
and letra
with a string (eg "A"
or "AA"
) or a reference to a cell containing the column letter (eg A2
) .
Print in LibreOffice:
Source: Convert column letter to number (ExcelJet)