Cómo convertir letra de columna a número en Excel

El tutorial habla sobre cómo devolver un número de columna en Excel usando fórmulas y cómo numerar columnas automáticamente.

La semana pasada, discutimos las fórmulas más efectivas para cambiar el número de columna al alfabeto. Si tiene una tarea opuesta que realizar, a continuación se encuentran las mejores técnicas para convertir un nombre de columna en un número.

Cómo devolver el número de columna en Excel

Para convertir una letra de columna en un número de columna en Excel, puede usar esta fórmula genérica:

COLUMNA(INDIRECTA(letra&»1″))

Por ejemplo, para obtener el número de la columna F, la fórmula es:

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

Y así es como puede identificar los números de columna mediante la entrada de letras en celdas predefinidas (A2 a A7 en nuestro caso):

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

Ingrese la fórmula anterior en B2, arrástrela hacia las otras celdas de la columna y obtendrá este resultado:

Cómo funciona esta fórmula:

Primero, construye una cadena de texto que representa una referencia de celda. Para esto, concatena una letra y el número 1. Luego, entrega la cadena a la función INDIRECTA para convertirla en una referencia real de Excel. Finalmente, pasa la referencia a la función COLUMNA para obtener el número de columna.

Cómo convertir letra de columna a número (fórmula no volátil)

Al ser una función volátil, INDIRECTO puede ralentizar significativamente su Excel si se usa ampliamente en un libro de trabajo. Para evitar esto, puede identificar el número de columna usando una alternativa no volátil un poco más compleja:

COINCIDIR (letra y «1», DIRECCIÓN (1, COLUMNA ($ 1: $ 1), 4), 0)

Esto funciona perfectamente en matriz dinámica de Excel (365 y 2021). En la versión anterior, debe ingresarlo como una fórmula de matriz (Ctrl + Shift + Enter) para que funcione.

Por ejemplo:

=MATCH(A2&"1", ADDRESS(1, COLUMN($1:$1), 4), 0)

O puede usar esta fórmula no matricial en todas las versiones de Excel:

=MATCH(A2&"1", INDEX(ADDRESS(1, INDEX(COLUMN($1:$1), ), 4), ), 0)

Cómo funciona esta fórmula:

En primer lugar, concatena la letra en A2 y el número de fila «1» para construir una referencia de estilo estándar «A1». En este ejemplo, tenemos la letra «A» en A2, por lo que la cadena resultante es «A1».

A continuación, obtiene una matriz de cadenas que representan todas las direcciones de celda en la primera fila, desde «A1» hasta «XFD1». Para esto, usa la función COLUMNA ($ 1: $ 1), que genera una secuencia de números de columna, y pasa esa matriz al argumento column_num de la función DIRECCIÓN:

ADDRESS(1, {1,2,3,4,5,…, 16384), 4)

Dado que row_num (primer argumento) se establece en 1 y abs_num (tercer argumento) se establece en 4 (lo que significa que desea una referencia relativa), la función DIRECCIÓN entrega esta matriz:

{"A1","B1","C1","D1",…,"XFD1"}

Finalmente, crea una fórmula COINCIDIR que busca la cadena concatenada en la matriz anterior y devuelve la posición del valor encontrado, que corresponde al número de columna que está buscando:

MATCH("A1", {"A1","B1","C1","D1",…,"XFD1"}, 0)

Cambie la letra de la columna a un número usando una función personalizada

«La simplicidad es la máxima sofisticación», afirmó el gran artista y científico Leonardo da Vinci. Para obtener un número de columna de una letra de una manera fácil, puede crear su propia función personalizada.

Totalmente en línea con el principio anterior, el código de la función es tan simple como puede ser:

Public Function ColumnNumber(col_letter As String) As Long

  ColumnNumber = Columns(col_letter).Column

End Function

Inserte el código en su editor de VBA como se explica aquí, y su nueva función llamada ColumnNumber estará lista para usar.

La función requiere solo un argumento, col_letter, que es la letra de la columna que se convertirá en un número:

ColumnLetter(col_letter)

Su fórmula real puede ser la siguiente:

=ColumnNumber(A2)

Si compara los resultados devueltos por nuestra función personalizada y los nativos de Excel, se asegurará de que sean exactamente iguales:

Devuelve el número de columna de una celda específica

Para obtener un número de columna de una celda en particular, simplemente use la función COLUMNA:

COLUMNA(dirección_celular)

Por ejemplo, para identificar el número de columna de la celda B3, la fórmula es:

=COLUMN(B3)

Obviamente, el resultado es 2.

Obtener letra de columna de la celda actual

Para encontrar un número de columna de la celda actual, use la función COLUMNA() con un argumento vacío, por lo que se refiere a la celda donde está la fórmula:

=COLUMN()

Cómo mostrar números de columna en Excel

De forma predeterminada, Excel usa el estilo de referencia A1 y etiqueta los encabezados de las columnas con letras y las filas con números. Para obtener columnas etiquetadas con números, cambie el estilo de referencia predeterminado de A1 a R1C1. Así es cómo:

1. En su Excel, haga clic en Archivo > Opciones.
2. En el cuadro de diálogo Opciones de Excel, seleccione Fórmulas en el panel izquierdo.
3. En Trabajar con fórmulas, marque la Estilo de referencia R1C1 cuadro y haga clic en Aceptar.
   

Las etiquetas de las columnas cambiarán inmediatamente de letras a números:

Tenga en cuenta que seleccionar esta opción no solo cambiará las etiquetas de las columnas, sino que las direcciones de las celdas también cambiarán de referencias A1 a R1C1, donde R significa «fila» y C significa «columna». Por ejemplo, R1C1 hace referencia a la celda de la columna 1 de la fila 1, que corresponde a la referencia A1. R2C3 hace referencia a la celda de la fila 2 columna 3, que corresponde a la referencia C2.

En las fórmulas existentes, las referencias de celda se actualizarán automáticamente, mientras que en las fórmulas nuevas deberá usar el estilo de referencia F1C1.

Consejo. A volver al estilo A1desactive la casilla de verificación Estilo de referencia F1C1 en Opciones de Excel.

Cómo numerar columnas en Excel

Si no está acostumbrado al estilo de referencia F1C1 y desea mantener las referencias A1 en sus fórmulas, puede insertar números en la primera fila de nuestra hoja de trabajo, de modo que tenga tanto letras de columna como números. Esto se puede hacer fácilmente con la ayuda de la función Autocompletar.

Aquí están los pasos detallados:

1. En A1, escriba el número 1.
2. En B1, escriba el número 2.
3. Seleccione las celdas A1 y B1.
4. Pase el cursor sobre un pequeño cuadrado en la esquina inferior derecha de la celda B1, que se llama controlador de relleno. Al hacer esto, el cursor cambiará a una gruesa cruz negra.
5. Arrastre el controlador de relleno hacia la derecha hasta la columna que necesita.

Como resultado, conservará las etiquetas de las columnas como letras y, debajo de las letras, tendrá los números de las columnas.

Consejo. Para mantener los números de las columnas a la vista mientras se desplaza a las siguientes áreas de la hoja de trabajo, puede congelar la fila superior.

Así es como se devuelven los números de columna en Excel. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!

(archivo .xlsm)