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

4

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)?

    
asked by anonymous 01.11.2018 / 13:52

2 answers

8

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:

    
01.11.2018 / 14:04
2

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)

    
01.11.2018 / 14:43