viernes, 26 de septiembre de 2014

Separar en varias celdas un texto

Puede descargar el siguiente fichero de Excel con la macro y un ejemplo para practicar su uso.

En Excel se puede escribir un texto en una sola celda que ocupe varios párrafos. Cuando estamos escribiendo y queremos crear un nuevo párrafo dentro de la misma celda no tenemos que pulsar ENTER ya que en ese caso la celda se valida y no nos deja seguir escribiendo el segundo párrafo.

Lo que debemos hacer para pasar al siguiente párrafo es pulsar ALT+ENTER. Esto es, debemos pulsar la tecla ALT y simultáneamente la tecla ENTER, y así pasamos al segundo párrafo y podemos seguir escribiendo.

Supongamos que tenemos una única celda con dos o más párrafos y deseamos que cada párrafo ocupe una celda diferente. Esto se puede hacer de forma manual de la siguiente forma.

  1. Editamos la celda que contiene varios párrafos. Para editarla debemos estar situados en ella y pulsar la tecla de función F2. También se edita pulsando doble click sobre la propia celda.
  2. Seleccionamos todo el contenido de la celda
  3. Pulsamos Control+C para copiar al portapapeles
  4. Nos situamos en cualquier celda vacía y que debajo de ella también tenga algunas celdas vacías tantas. Vamos a necesitar tantas celdas vacías como párrafos contenga nuestro texto.
  5. Pulsamos Control+V que pega el contenido del portapapeles
Con este método hemos conseguido de forma manual separar los diferentes párrafos en y que cada uno de ellos ocupe una celda diferente.

Ahora queremos hacer esto de forma automática mediante una Macro de Excel, creando un código VBA (Visual Basic para Aplicaciones).

La macro es la siguiente.

Sub Separa()
Dim toma As String, linea As String
Dim largo As Integer, i As Integer, s As Byte
Dim caracter As String * 1
Dim fila As Long, columna As Integer
Dim A() As Integer
fila = ActiveCell.Row
columna = ActiveCell.Column
toma = ActiveCell.Value
largo = Len(toma)
ReDim A(largo)
For i = 1 To largo
    caracter = Mid(toma, i, 1)
    If caracter = Chr(10) Then
        s = s + 1
        A(s) = i
    End If
Next i
If s = 0 Then MsgBox ("En la celda activa no se detecta ningún ENTER"): End
A(s + 1) = largo + 1
For i = 0 To s
    linea = Mid(toma, A(i) + 1, A(i + 1) - A(i) - 1)
    Cells(fila + i + 1, columna) = linea
Next i
End Sub

Para que funcione correctamente debemos situarnos previamente en una celda que contenga dos o más párrafos de texto creados pulsando ALT+ENTER para separar cada uno de ellos.

La macro lo que hace es respetar la celda en la que estamos sin cambiarla y justo debajo de ella utiliza las celdas que existen para poner en cada una de ellas uno de los párrafos. Se necesitan tantas celdas vacías debajo de la celda que contiene el texto como párrafos existan.

Por ejemplo, si tenemos una única celda con el texto siguiente:

Linea1
Linea2
Linea3
Linea4

La macro detecta que existen 27 caracteres. Esto se puede medir con la función =LARGO
Los caracteres son:

  • 6 de la primera línea
  • 6 de la segunda líena
  • 6 de la tercera líena
  • 6 de la cuarta línea
  • 3 retornos de carro
Los retornos de carro son las veces que hemos pulsado ALT+ENTER y el código de carácter que llevan en CHR 10.





Al final la macro crea debajo de la celda que contiene ese texto (celda amarilla) cuatro celdas con el texto separado correspondiente a cada uno de los 4 párrafos.

Separar por un carácter


En la Hoja2 hemos creado otro ejemplo donde el usuario decide el carácter separador. Por ejemplo, podemos separar un texto por la coma.

En la celda D2, de color rosa, ponemos el carácter separador, por ejemplo una coma. En la celda D3 se calcula el código que corresponde a ese carácter. La coma tiene el código 44.

En la celda amarilla B5 escribimos el texto que contiene una o varias comas, o el carácter separador que nos interese. Con el cursor situado en B5 pulsamos el botón que lanza la macro y se producirá la separación del texto en varias celdas.


Observamos que Dato1 ha quedado bien, pero que Dato2 y los siguientes tienen un espacio en blanco al inicio. Si deseamos que este espacio inicial no aparezca lo que haremos es usar la otra macro que hemos habilitado que se lanza pulsando en el icono de la goma de borrar.


En esta ocasión no hemos lanzado las macros con los botones habituales, hemos optado por un control ActiveX para la primera y una imagen para la segunda macro.

La macro que se lanza con el botón rotulado como "Separa por carácter" es un botón de comando (control ActiveX). Para editarlo tenemos que ir a Programador y pulsar sobre el icono de escuadra para entrar en modo diseño y luego ir a las propiedades, donde podremos cambiar ligeramente el color de fondo 'BackColor'.

Estando en modo diseño pulsamos con el botón derecho del ratón sobre el botón y elegimos 'Ver código'. La macro es un evento de tipo click.


Private Sub CommandButton1_Click()
  Call SeparaPorCaracter
End Sub


No olvidar pulsar de nuevo sobre la escuadra para salir del modo diseño.

La segunda macro se lanza pulsando sobre el icono de la goma de borrar. Conseguir asociar una macro a una imagen es más sencillo ya que simplemente se ha de pulsar sobre el imagen con el botón derecho del ratón y elegir 'Asignar macro'.

14 comentarios:

  1. Agradecería mucho, por favor puedas mostrarnos el proceso inverso. Es decir, tener varias celdas con texto y unirlas en una sola de modo que podamos verla como en el ejemplo anterior (color amarillo)

    ResponderEliminar
  2. Existe una función muy útil para este caso es =concatenar(texto1, texto2 y así sucesivamente. Espero te sirva y evitas usar macros.

    ResponderEliminar
  3. Muchas gracias, pero como puedo hacer para que estas al lugar de que aparezcan abajo, aparezcan en una columna lateral

    ResponderEliminar
    Respuestas
    1. copias todo lo que genero en filas y haces pegado especial, opcion transportar

      Eliminar
  4. gracias por el aporte, el problema que encontré es que si el texto tiene algún tipo de formato (subrayado, negritas, color, otra letra...) no lo respeta

    ResponderEliminar
  5. Excelente. Muchiiiisimas gracias. Estaba buscando desde hace tiempo. Gracias

    ResponderEliminar
  6. Buenos dias, como podria hacer para que en vez que detecte el ENTER, detecte la coma para crear la separacion:
    ejem:
    dato1, dato2, dato3, dato4
    resultado:
    dato1
    dato2
    dato3
    dato4

    de antemano gracias por el apoyo

    saludos

    ResponderEliminar
    Respuestas
    1. Hola Elder.
      He creado la Hoja2 donde se intenta resolver el caso que planteas.
      Espero que esto te ayude.
      Un saludo.

      Eliminar
  7. Excelente macro, felicitaciones. Me salvo la vida. Well done!

    ResponderEliminar
  8. el ejemplo es excepcional pero sobrescribe las celdas siguientee, no detecta si en las celdas de abajo hay texto o no, a fin de colocarlo en la ultima celda no escrita.

    ResponderEliminar
  9. Quisiera saber como indicarle que no lo pegue justo en la siguiente celda si no en la primera celda vacia que encuentre de la columna A en la siguiente hoja

    ResponderEliminar
  10. Hola Podrias explicarlo en un video, sobre todo como la funcion mid en la macro, no entiendo como separa las palabras

    ResponderEliminar