lunes, 22 de octubre de 2012

Extraer una parte del texto de una celda, con VBA


Hace unos días recibí un e-mail de una lectora del Blog, preguntando cómo podría conseguir mediante código VBA (una macro) extraer una parte del texto de un rango de celdas en una columna. En su mensaje, me proponía un código que ya estaba utilizando, pero quería perfeccionarlo para tener una mayor utilidad.


El código aportado por mi lectora era:


Sub Extraer()
Final = Range("A65536").End(xlUp).Row
For x = 2 To Final
Cells(x, 2) = Mid(Cells(x, 1), 5, 4)
Next


Lo que hacía cada una de las líneas era:
  1. Determinar a cuantas celdas se le debe aplicar la "Extracción"  (2ª línea del código)
  2. Iniciar un bucle con un número de repeticiones igual al número de celdas hallado en el punto anterior, asignando en la primera vuelta del bucle, el valor x = 2
  3. Insertar en la celda B2 un texto que se extraerá del texto de la celda A2 (Cells (x,2), empezando por el 5º caracter (contando desde la izquierda)  y tomando para la extracción los siguientes 4 caracteres.
  4. Comienza otra vuelta de bucle en el que inserta en la celda B3 un texto que se extraerá del texto de la celda A3, empezando por el 5º caracter (contando desde la izquierda)  y tomando para la extracción los siguientes 4 caracteres.
  5. ...y así sucesivamente...
En definitiva, que del texto  FRANCISCO-01   extraería la cadena de texto   CISC

Pero mi lectora quería algo diferente. Lo que pretendía era tener la posibilidad de "extraer" un cierto número de caracteres contando desde la derecha. Ese "cierto número de caracteres" debería ser elegido previamente por el usuario, mediante un cuadro de diálogo que se obtendría en pantalla al presionar un botón (por ejemplo). 

Pues bien, le ofrecí la siguiente solución:

Sub RecortarDerecha()
NCaracteres = InputBox(Chr(13) & Chr(13) & Chr(13) & Chr(13) & "Introduce el número de caracteres a recortar", "Nº caracteres a recortar", "Nº caracteres a recortar", 1000, 1000)
Final = Range("A65536").End(xlUp).Row
For x = 2 To Final
Cells(x, 2) = Right(Cells(x, 1), NCaracteres)
Next
End Sub


Respetando parte del código que teníamos en la propuesta inicial, una de las soluciones posibles se trata de:
  1. Introducir la variable NCaracteres, que representaría a lo introducido por el usuario en un "Inputbox" (2ª línea del código). Podemos recordar cómo se introduce un cuadro de introducción de datos o "Inputbox" en el siguiente artículo: "Introducción de datos en Excel con un cuadro de diálogo"
  2. Cambiar la función MID por RIGHT  (recordemos que ahora se pretende extraer desde el primer caracter de la derecha siempre).
  3. Cambiar los dos últimos argumentos que tenía la función MID, por un sólo argumento: el número de caracteres que queremos recortar desde el primer caracter de la derecha (NCaracteres).
Podemos comprobar el funcionamiento de todo ello, descargando el siguiente archivo Excel:


Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:






29 comentarios:

  1. Es posible hacer que en vez de usar la derecha como referencia, pueda usar un caracter (como la @)?

    ResponderEliminar
    Respuestas
    1. Es fácil, solo se debe de saber que lugar ocupa este caracter (@), para ello se debe usar la función: InStr y después usaremos la función MID para extraer hacia la izquierda o hacia la derecha. Ejm:
      Texto="CONCRETO@C34"
      i = InStr(Texto, "@") 'el valor de i será 9 De aquí se puede extraer a la izquierda o hacia la derecha.
      TextoIzq = Mid(Texto, 1, i - 1)' TextoIzq será: CONCRETO
      TextoDer = Mid(Texto, i + 1, 10000)' TextoDer será: C34






      Eliminar
  2. como podria hacer que aparezca automaticamente el siguiente codigo (+1 al anterior) cada vez que habra el formulario.

    AYUDA POR FAVOR

    ResponderEliminar
    Respuestas
    1. Insertando el siguiente código dentro del módulo privado de la hoja (no en un módulo estándar):

      Private Sub Worksheet_Activate()
      Range("A1") = Range("A1") + 1
      End Sub


      Para insertar código en un módulo privado de hoja, debes hacer clic con el botón derecho en la pestaña de la hoja y elegir "Ver código".

      Saludos.

      Eliminar
  3. queria aprevchar este foro para saber si alguien me puede ayudar

    necesito crear un boton en excel de manera que cuando lo pulse me muestre en determinada celda el valor"1" tantas veces sea pulsado pero de la siguiente manera
    si se pulsa una vez que aparezca "1" si se pulsa dos veces "11" y son 3 "111" y asi de esta manera las veas que sean

    alguien me podri ayudar

    Un saludo

    les agradezco

    ResponderEliminar
  4. Hola me gusta el post, es muy bueno. muchas gracias! :D
    Pero tengo una duda lo podria hacer no por columna sino por fila?
    como quedaria?

    ResponderEliminar
    Respuestas
    1. Este comentario ha sido eliminado por el autor.

      Eliminar
    2. Podría ser así:

      Sub RecortarDerecha()
      NCaracteres = InputBox(Chr(13) & Chr(13) & Chr(13) & Chr(13) & "Introduce el número de caracteres a recortar", "Nº caracteres a recortar", "Nº caracteres a recortar", 1000, 1000)
      Final = Range("XFD1").End(xlToLeft).Column
      For x = 2 To Final
      Cells(2, x) = Right(Cells(1, x), NCaracteres)
      Next
      End Sub

      Eliminar
  5. Como podría extraer de una fila que contiene varios datos de interés en una misma celda por ejemplo:

    L1 Producto: Silla Marca: Ikea Pro
    L2 ducto: Mesa Marca: Tugo Producto:
    L3 Armario Marca: Ikea Producto:Sil
    L4 la Marca: Ikea Producto: Mesa Mar
    L5 ca: Tugo Producto: Silla Marca: I
    L6 kea


    En diferentes columnas sea una para Producto y otra para Marca y teniendo en cuenta que no existe una única posición de referencia, ni se pueden usar las palabras producto y marca como referencia porque no siempre están completas.

    ResponderEliminar
  6. Como podría extraer de una fila que contiene varios datos de interés en una misma celda por ejemplo:

    L1 Producto: Silla Marca: Ikea Pro
    L2 ducto: Mesa Marca: Tugo Producto:
    L3 Armario Marca: Ikea Producto:Sil
    L4 la Marca: Ikea Producto: Mesa Mar
    L5 ca: Tugo Producto: Silla Marca: I
    L6 kea


    En diferentes columnas sea una para Producto y otra para Marca y teniendo en cuenta que no existe una única posición de referencia, ni se pueden usar las palabras producto y marca como referencia porque no siempre están completas.

    ResponderEliminar
    Respuestas
    1. Como veo en el ejemplo, no hay ninguna pauta para poder automatizar esta tarea, ya sea mediante funcione o mediante VBA.
      Creo que la clave sería evitar tener los datos de eta forma tan poco óptima. Habría que revisar cómo llegan a estar asi los datos al traerlos desde su origen y buscar otro método que los deje en mejor disposición
      Saludos

      Eliminar
  7. Hola, dispongo de la siguiente información en una celda,

    "R23-24 LE 29/130, R22-23 TE 7/300, R23-24 TE 9/170" , la cual es un comentario para cada pieza en una manera standard. Como podría crear una macro para que me separe cada una de estas partes, es decir R23-24, LE, 29/130, R22-23, .... de manera separada. Dejar dicho que que cada comentario es diferente pero sigue un patron standar de reporte, por ejemplo se podria encontrar un comentario R9-10 TE 3/600.

    Las plantillas donde se generan esta información no se puede modificar, solo me queda crear una macro y adaptarme a esta manera de reporte.

    ResponderEliminar
    Respuestas
    1. No esposible contestar a esta pregunta con esos datos y explicación. La respuesta requiere conocer más la problemática (por ejemplo, el patrón de comportamiento de los datos, entre otras cosas). Además, requiere de un tiempo relativamente importante para diseñar la macro en funcion de los datos.
      Saludos

      Eliminar
  8. ME GUSTARIA SABER COMO EXTRAER DE UN CODIGO EJEMPLO(ABB UK50 ABBBL524DE PUERTA C/MARCO ANTRACITA PARA UK520 UN 55,8000)
    PODER SEPARARLO ASI
    A1(ABB) A2(UK50) A3(ABBBL524DE) A4(PUERTA C/MARCO ANTRACITA PARA UK520)A5(UN)A6(55,8000)
    GRACIAS DE ANTEMANO

    ResponderEliminar
  9. LOS DATOS ESTAN SEPARADOS POR ESPACIOS

    ResponderEliminar
    Respuestas
    1. Buenos ´días.
      Puesto que hay cadenas de varios textos separados por espacios que deseas que estén en una sola celda, no se puede realizar con el comando "Texto en columnas".
      Puesto que el texto a separar tampoco sigue un patrón para poder realizar una macro, no se posible hacerlo.
      Saludos

      Eliminar
  10. Buenos días,

    Me podrían ayudar con lo siguiente. Tengo una celda de textos separados por tabuladores(,) y necesito extraer solamente un tipo de texto que puede estar en cualquier orden en esa tabulación (primero, segundo, tercero etc.) Tengo una palabra clave con la que se identificaría el texto que necesito de todos los que tiene la celda. Lo que quuiere es una función que busque una palabra dentro de una celda y una vez haga esto busca la coma a la derecha y la coma a la izquiera para que me traiga un nombre. Ejemplo: texto de la celda (School of Business, University of Western Sydney, Parramatta Campus, Australia), necesito extaer University of Western Sydney, en este caso esta de segundo pero la posición no siempre es la misma. Mi palabra de busqueda sería University, quiero que una vez identifique esta palabra en la celda se traiga todo el nombre University of Western Sydney. Les agradezco mucho su ayuda.

    ResponderEliminar
  11. Esto se puede hacer de varias formas y ninguna es inmediata. Por lo tanto no esposible explicar aquí cómo hacerlo.
    Pero para dar una pequeña guía en una de las posibilidades, sería comenzar extrayendo todos los textos en varias columnas. Es decir, aprovechar el comando "Texto en columnas" de la ficha DATOS para que todo se coloque en varias columnas que coincidan con las separaciones de las comas.
    Después, depende del método de búsqueda que se exija, se podría hacer con la función BUSCARH o quizá habría que realizar una macro para resolver finalmente la búsqueda.
    Saludos.

    ResponderEliminar
  12. Buen dia

    requiero una macro que me cuente la cantidad de caracteres de una celda y pueda escoger ya sea de la derecha o de la izquierda cierta cantidad y asignarlos en otra celda

    Agradezco su atencion

    ResponderEliminar
    Respuestas
    1. Esta sería una posibilidad que colocaría el resultado en la celda A1, considerando que la celda en la que tiene que contar los caracteres es la celda seleccionada en ese momento:

      Sub ElegirCaracteres()

      MsgBox "En la celda " & ActiveCell.Address & " hay un texto con " & Len(ActiveCell) & " caracteres"

      Lado = InputBox("Escribe el número 1 si quieres elegir caracteres comenzando desde la izqiuerda o escribe el número 2 si quieres elegir caracteres comenzando desde la derecha.")
      NCaracteres = InputBox("Escribe el número de caracteres que quieres extraer.")
      ResultadoIzq = (Left(ActiveCell, NCaracteres))
      ResultadoDer = (Right(ActiveCell, NCaracteres))

      If Lado = 1 Then
      Range("A1") = ResultadoIzq
      Else
      Range("A1") = ResultadoDer
      End If

      End Sub

      Eliminar
    2. Buen dia...Muchas gracias por la repuesta me fue muy util.
      No se si me pueda colaborar con lo siguiente

      ADSL: Se realiza verificación del equipo BOTIERURBA14 y a la hora se observa operativo. Se confirma incidente.
      19/01/2017 16:07:59 (fabicrut):

      El texto anterior lo tengo dentro de una celda lo que requiero es:

      19/01/2017 16:07:59 (fabicrut): (Esto dentro de una celda)

      ADSL: Se realiza verificación del equipo BOTIERURBA14 y a la hora se observa operativo. Se confirma incidente. (Esto dentro de una celda frente a lo anterior hecho)

      la verdad soy nuevo en el tema de programacion no se si me pueda colaborar explicando que realiza cada funcion

      nuevamente le agradezco por su colaboracion y el compartir ese conocimiento


      Eliminar
    3. buen dia de lo anterior si se puede en bucle ya que son varias celdas donde tengo informacion parecida

      Agradezco su atencion

      Eliminar
    4. Buen dia..

      Agradezco su ayuda.

      tengo otra consulta no se si me puede colaborar
      requiero una macro que me cuente los caracteres de una celda y me reste los ultimos 33, y ubique el resultado en la celda del frente. requiero que esta operacion sea en bucle. ya que tengo varias celdas.

      soy nuevo en el tema de programacion , algun libro o pagina que me recomiende.

      Nuevamente agradezco su ayuda

      Eliminar
    5. Para saber el nº de caracteres que contiene una celda, se debe utilizar la función de VBA: Len(expresión). Donde hay que sustituir "expresión" por la referencia a la celda que contiene el valor (por ejemplo, Range("A1")).
      Una vez hecho esto, si lo que quieres es restar 33, solo debes escribir: Len(expresión)-33.
      Pero si lo que quieres es que "corte" los últimos 33 caracteres, debes poner:
      Left(expresión,Len(expresion)-33)

      Eliminar
  13. Buen dia, tengo el siguiente texto en una celda "63183056 - 63183070 / UNIDROGAS NOTA CREDITO POR AJUSTE EN PRECIO TOTAL: 11.708.396", requiero extraer los digitos antes del simbolo / y el valor del final del texto. Como puedo generar una macro para esta operacion, gracias.

    ResponderEliminar
    Respuestas
    1. Se puede hacer con funciones:

      =IZQUIERDA(D3;ENCONTRAR("/";D3)-2)
      =DERECHA(D3;LARGO(D3)-ENCONTRAR(":";D3)-1)

      siendo D3 la celda del texto largo

      Saludos

      Eliminar
  14. Buenos días.

    Tengo una hoja de Excel en el que las celdas contienen texto y números de factura que empiezan por 6 y tienen 10 digitos. Necesitaría extraer estos números, sin embargo no logro la forma, pues la posición dentro de la celda no siempre es la misma. Un ejemplo de los datos que tengo en una celda son:REF-6508163H0023404
    /ENTRY-12 JUN POSTED=07:10
    TRSF BOOK TRANSFER CREDIT
    SND=/SA-GPBAFRPPXXX
    ORG=xxxx BANK ES 1)
    OGB=/SA-GPBAFRPPXXX
    6000000000 RPPXXX. ¿Alguien sabe como podría hacer?

    ResponderEliminar
  15. Si te refieres al número 6000000000 que hay en el texto que has puesto, sería necesario detectar un patrón de conducta constante en esas celdas, por ejemplo, que después o antes del 6000000000 siempre esté la cadena de caracteres RPPXXX.
    Si no se puede obtener ningún patrón, esto se debería hacer con VBA generando un programa adhoc que detecte cuán hay una cadena contínua de 10 caracteres y, en caso de ser así... si esa cadema comienza por 6.
    Saludos

    ResponderEliminar
  16. hola buendia, ejemplo, deseo extraer una palabra dentro de una cadena de texto, pero para el caso, deseo conocer la posición de los espacios, para elegir extraer la palabra que requiero, he intentado con instr pero me da valor 0, observo que el caracter "" no lo reconoce, es decir lo escribo asi: " "" " encerrado entre comillas para diferenciarlo, y el resultado es 0. gracias de antemano por su respuesta saludos

    ResponderEliminar