viernes, 4 de noviembre de 2011

Obtener la letra de una columna en Excel.

Me llegaba hace algunos días una petición curiosa, un usuario quería conocer, en base a unas coincidencias, cuál era la letra de una columna concreta. Como el ejercicio era algo complejo, me centraré sólo en la forma de conseguir para un caso general, la letra de una columna particular; fijémosnos que digo letra y no número, ya que este último es fácil de lograr con la función COLUMNA.
El asunto no es tan sencillo como pudiera parecer en un principio, de hecho vamos a necesitar anidar varias funciones una dentro de otra.
Las funciones a emplear ya son viejas conocidas:
  • SUSTITUIR: con esta reemplazaremos, realmente eliminaremos, caracteres que nos estorban para nuestro objetivo

  • DIRECCION: definiremos la celda a evaluar, para obtener de ella la letra de su columna.

  • FILA y COLUMNA: la base de toda la propuesta, con éstas obtendremos una numeración para definir una celda.

Combinando todas estas funciones obtendríamos la siguiente fórmula:
=SUSTITUIR(SUSTITUIR(DIRECCION(FILA();COLUMNA());"$";"");FILA();"")
que devolverá, se encuentre donde se encuentre, la letra de la columna, sin restricción.
La analizaremos de dentro hacia afuera:
con =DIRECCION(FILA();COLUMNA()) tendremos la referencia absoluta de la celda donde se encuentre la fórmula.
El siguiente paso será quitar de esa referencia lo que no nos hace falta, es decir, como sólo quiero la letra de la columna, tendremos que SUSTITUIR el símbolo del dolar $ y el número de la FILA,
lo que conseguimos con un doble anidamiento con la función SUSTITUIR, reemplazando $ y la FILA() por nada "".
Podemos comprobar como independientemente de donde copiemos dicha fórmula siempre obtendremos la letra de la columna buscada:

32 comentarios:

  1. hola.

    También puede usarse esta otra:

    =IZQUIERDA(SUSTITUIR(DIRECCION(FILA();COLUMNA());"$";"");1)

    ResponderEliminar
  2. Correcto...
    existen varias formas de llegar al mismo resultado... la mayoría usando la función SUSTITUIR.
    Gracias por el comentario.
    Un saludo

    ResponderEliminar
    Respuestas
    1. Sois geniales. Me acabáis de ahorrar un montón de tiempo de trabajo. ¡Gracias!!
      Carmen

      Eliminar
  3. También

    =izquierda(direccion(fila();columna();4);1)

    donde el 4 hace que sustituir dé la referencia relativa con lo que nos ahorramos unas cuantas anidaciones con Sustituir :-)

    Gracias por la idea original, me será muy útil!

    ResponderEliminar
  4. Corrijo el error:

    También

    =izquierda(direccion(fila();columna();4);1)

    donde el 4 hace que DIRECCION dé la referencia relativa con lo que nos ahorramos unas cuantas anidaciones con Sustituir :-)

    Gracias por la idea original, me será muy útil!

    ResponderEliminar
  5. Hola.

    Yo uso: =CARACTER(COLUMNA()+64)

    Saludos.

    ResponderEliminar
  6. Cesar..
    tu forma sólo llega hasta la columna Z
    :-(

    ResponderEliminar
  7. Hola...
    muy bueno el análisis de la función DIRECCION y el detalle del argumento [abs]...
    efectivamente, ahorras trabajo, dejando la referencia relativa en lugar de absoluta (es decir, sin símbolo $).
    Muchas gracias por el comentario... a veces olvidamos el uso de todos los argumentos de funciones sencillas.
    Un cordial saludo

    ResponderEliminar
  8. Hola...

    =EXTRAE(" ABCDEFGHI";1+(COLUMNA()-1)/26;1) & CARACTER(65+RESIDUO(COLUMNA()-1;26))

    =EXTRAE(" ABCDEFGHI";1+(COLUMNA()-1)/26;1) & EXTRAE("ABCDEFGHIJKLMNOPQRSTUVWXYZ";1+RESIDUO(COLUMNA()-1;26);1)

    Saludos.

    ResponderEliminar
  9. EN LA COLUMNA A1:A10 TENGO LOS NUMEROS DEL 1 AL 10 POR FAVOR DIGANME COMO EXTRAIGO POR EJ. EN LA CELDA A11 EL NUMERO 1

    ResponderEliminar
    Respuestas
    1. Hola, buenos días.
      en la celda A11 añade la función =MIN(A1:A10) o también la =K.ESIMO.MENOR(A1:A10;1)
      Un saludo
      P.D.: por favor, evita escribir en mayúsculas; es señal de falta de respeto ;-)

      Eliminar
  10. Hola!

    cuidado con las soluciones que usan IZQUIERDA. Sólo funcionan hasta la columna Z. A partir de ahí (AA,AB...) sólo devuelven la primera letra

    ResponderEliminar
    Respuestas
    1. Hola!
      supongo te refieres a la que comenta un lector a través del comentario...
      Sin duda, parece más completa la que comento en la entrada:
      =SUSTITUIR(SUSTITUIR(DIRECCION(FILA();COLUMNA());"$";"");FILA();"")

      Muchas gracias por el apunte!!
      Slds

      Eliminar
  11. Sres. les comparto la que utilizo y me es funcional para estos casos.

    =SUSTITUIR(DIRECCION(FILA(),COLUMNA();4),FILA();"")

    Saludos

    ResponderEliminar
    Respuestas
    1. PODRIAN PONER EJEMPLO COMO QUEDA EL TEXTO FINAL?

      Eliminar
    2. Hola Hector, qué tal estás?
      el texto de la fórmula anterior aparecerá como la letra de la columna en que esté, es decir, si la fórmula se encuentra en la celda XCD200 mostrará XCD.
      Sdls

      Eliminar
  12. perdon esta es la correcta

    =SUSTITUIR(DIRECCION(FILA();COLUMNA();4);FILA();"")

    ResponderEliminar
    Respuestas
    1. Muchas gracias,
      una variante de la propuesta más elegante.
      Slds

      Eliminar
    2. oye y ahorita asta ke letra llega exel tanto en columnas y en filas con numeros

      Eliminar
    3. Hola, que tal!
      Pues 16.384 columnas y 1.048.576 filas

      Slds

      Eliminar
  13. Hola
    Tengo una planilla con muchos datos, en donde ingreso un dato a buscar y me dice en que numero de fila esta el dato o valor buscado, pero ademas necesito que el cursor se vaya directo a la priemra celda o a la celda donde esta el valor.
    Gracias

    ResponderEliminar
    Respuestas
    1. Hola Wladimir,
      bueno, podrías emplear el método Find, en una macro para tal cosa.
      Slds

      Eliminar
  14. Saludos, tengo una pregunta: como extraigo el numero de fila y columna donde hay algun valor o texto, y los resultados, que los pueda usar en otra formula... que el input son esos valores para de esa casilla extraer los datos que haya y usarlos en los calculos que esta haciendo?? por ejemplo (b,4) tiene el valor 5 y en otro lado (45*"b4")= x pero el b4 puede cambiar a d345 o tty56.. se explica?

    ResponderEliminar
  15. Sigo, es decir identificar algun valor en una casilla, como con buscarv o buscarh y que devuelva la fila y columna donde esta ese valor y esos valores sean input en la formula de esa casilla, por ejemplo: buscarv(.....) obtiene un numero 4455 y ese numero esta en col B y fila 99.. quiero que el b99 aparezca en el calculo que se esta haciendo ya que asi podra variar dicha formula de acuerdo a lo que encuentra buscarv o h..

    ResponderEliminar
    Respuestas
    1. Hola Hector,
      No termino de ver muy claro el planteamiento, creo que serviría BUSCARV sin más , ya que esto te devolvería el valor buscado independientemente de donde esté cada vez situado, en función a diferentes búsquedas.

      Otra opción pasaría por emplear una de las funciones explicadas en este mismo post DIRECCION, y claro está tener muy claro donde voy a realizar esa búsqueda inicial (y aunque no lo has comentado, calramente no puede haber valores repetidos). Aunque sería más retorcido...

      Imaginemos para este segundo caso en el rango C9:C12 diferentes importes, en otra celda queremos localizar la dirección de u nimporte concreto situado en E11, asi que en la celda F11 escribimos:
      =DIRECCION(COINCIDIR(E11;C9:C12;0)+8;3)

      ojo al ajuste +8 de las filas.
      Si posteriormente queremos emplear el valor (Importe) de esa celda en otra fórmula simplemente anidaríamos dicha fórmula en un INDIRECTO(F11)

      Espero haber comprendido bien lo que tratabas de explicar.
      Slds

      Eliminar
    2. Gracias pero se me hace que no lo he explicado bien, lo trato de ilustrar con un ejemplo posteriormente... hector@delmargg.com

      Eliminar
    3. Hola..
      envíame un ejemplo a
      excelforo@gmail.com

      Slds

      Eliminar
  16. Saludos Ismael, te mande ejemplo.. 25-5-13

    ResponderEliminar
  17. para obtener de deforma relativa y al gusto cree una funcion y al llamarla
    simplemente =LetraColumna(B34) resultado sera B

    Function LetraColumna(rango As Range) As String
    LetraColumna = ""
    Letra = rango.AddressLocal(0, 0)
    For I = 1 To Len(Letra) - 1
    LetraColumna = Mid(Letra, 1, I)
    If IsNumeric(LetraColumna) Then Exit For
    Next
    End Function

    ResponderEliminar
    Respuestas
    1. Hola, que tal Luís Carlos?
      un palcer saludarte, y muchas gracias por el aporte.
      La idea es buena.. aunque falla, ya que devuelve columna y número de fila..quitándole el último caracter :-(

      Sería mucho más efectivo y válida para todos los casos:

      Function L_Columna(rng As Range)
      Dim MiColumna As String, dato As String
      dato = rng.Address
      L_Columna = Mid(dato, InStr(dato, "$") + 1, InStr(2, dato, "$") - 2)
      End Function

      Un saludo

      Eliminar
    2. Gracias por la correccion

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.