domingo, 17 de mayo de 2015

Eliminar espacios en blanco

Archivo de Excel utilizado: apellidos.xlsm

Es frecuente que necesitemos eliminar espacios en blanco especialmente cuando hemos importado datos de algún sistema de reporting y hemos utilizado Datos > Texto en columnas.

Excel dispone de dos funciones que nos permiten eliminar espacios de una cadena alfanumérica. Son las siguientes.

=Espacios(texto)


Función de la categoría Texto. Quita todos los espacios del texto excepto los espacios individuales entre palabras. Esto es, elimina los espacios que existen antes y después de una frase y de los interiores deja uno entre palabras, pero si existe más de uno solo deja uno.

Ejemplo


Tenemos el siguiente texto:     Hola Madrid  y  Lisboa   

Contiene tres espacios al inicio de la frase, tres al final, uno antes de Madrid y una pareja de espacios antes de la 'y' otra pareja después.

Aplicando la función

=Espacios("   Hola Madrid  y  Lisboa   ")

obtenemos lo siguiente

Hola Madrid y Lisboa

Es una frase normal donde únicamente existen tres espacios situados entre palabras. Por tanto, se han eliminado 3 espacios al inicio, 3 al final, y 2 interiores.


=Sustituir(Texto;Texto_original;Texto_nuevo;[Núm_de_ocurrencia])


Reemplaza el texto existente con texto nuevo en una cadena.

Ejemplo


Disponemos de la siguiente frase en la celda A1

Balance de Enero y Cuenta de Resultados de Enero

Deseamos cambiar el mes de Enero por el de Febrero, para ello podemos usar la función Sustituir

=Sustitutir(A1;"Enero";"Febrero")

El cuarto argumento Núm_de_ocurrencia es optativo y si no se indica ningún valor se sustituyen todos los Eneros que se encuentren.

El resultado obtenido será el siguiente

Balance de Febrero y Cuenta de Resultados de Febrero


El carácter 160 en Excel


El carácter 160 en Excel se ve como un espacio en blanco pero no lo es, ya que el espacio en blanco se corresponde con el carácter 32. Cuando importamos texto o datos de Internet o de algún sistema de contabilidad, facturación, logística, etc, pudieran importarse caracteres que aparentemente son espacios en blanco pero en realidad no lo son ya que se trata del famoso carácter 160.

Reemplazar


Lo que procede en muchos casos es sustituir ese carácter por un verdadero espacio en blanco, o en otras ocasiones, lo que interesa es eliminarlo completamente.

Para sustituirle podemos usar Buscar y Reemplazar, por ejemplo pulsando Control+L, y así poder reemplazar el carácter 160 por un verdadero espacio en blanco.

El carácter 160 se puede generar en una celda escribiendo la siguiente fórmula.

=CARACTER(160)

También se puede obtener pulsando Alt+255. El número se debe marcar con el teclado numérico mientras se mantiene presionada la tecla Alt.

Macro 1


Otro método para reemplazar todo es usar la siguiente macro.

Como las macros no se puden deshacer es conveniente que grabe antes de lanzar la macro ya que el resultado de la macro sobre nuestra hoja aún no está probado.


Sub Reemplazalo()
Cells.Replace What:=Chr(160), Replacement:=Chr(32), LookAt:=xlPart, SearchOrder _
  :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub



Macro 2


Existe una segunda macro que es posible que le guste más ya que se trata de una función programada en VBA que lo que hace es ir recorriendo todos y cada uno de los caracteres de una celda y elimina todos los caracteres 160 y también todos los caracteres 32. Esto es, deja el contenido de la celda sin ningún espacio. La función es la siguiente.


Function EliminaEspacios(texto As String) As String
Dim i As Byte
Dim n As Byte 'es la longitud de la cadena
Dim Letra As String * 1
Dim NewTexto As String 'nueva cadena sin espacios
n = Len(texto)
For i = 1 To n
  Letra = Mid(texto, i, 1)
  'chr(32) es un espacio en blanco
  'chr(160) es similar a un espacio en blanco en Excel
  If Letra <> Chr(160) And Letra <> Chr(32) Then
    NewTexto = NewTexto & Letra
  End If
Next i
EliminaEspacios = NewTexto
End Function


Caso práctico


Paso 1

Vamos a tomar un listado de los apellidos más frecuentes en España de la siguiente página web. Los tomamos simplemente seleccionando y copiando y pegando en Excel.

El resultado lo encontrará en la Hoja1 del fichero de Excel que hemos dejado al inicio de este post.

Paso 2

En la Hoja2 separamos en tres columnas lo importado usando para ello Datos > Texto en columnas.


Si probamos con "Delimitados" y pedimos que use como delimitador el 'Espacio' podemos comprobar que no conseguimos nuestro objetivo ya que no se detecta ningún espacio. El motivo es que lo que parecen ser espacios en realidad se trata del carácter 160 que no es detectado como espacio.

Esto nos obliga usar la separación de columnas "De ancho fijo" obteniendo lo que se puede ver en la Hoja2.

Paso 3

Para eliminar las finas vacías y las filas donde únicamente aparece la letra del abecedario lo que hacemos es lo siguiente.

  • Seleccionamos todo el área de datos incluida cabecera
  • Pulsamos F5 que es Ir a... y pulsamos sobre el botón Especial
  • Elegimos la opción 'Celdas en blanco'
  • Eso lo que hace es seleccionar las filas que deseamos eliminar
  • Ahora vamos al menú Inicio > Eliminar > Eliminar filas de hoja
  • Con esto hemos eliminado las filas que no nos interesaban, las vacías y las del abecedario
  • El resultado lo puede ver en la Hoja3

Paso 4

En la Hoja4 vamos a utilizar la fórmula =EliminaEspacios(texto) para quitar tanto el carácter 160 como el carácter 32, que es el espacio en blanco, en caso de que lo hubiera.

Sitúese en la celda E2 y escriba la fórmula siguiente.

=EliminaEspacios(B2)

Copie esa fórmula a su derecha a las celdas F2 y G2.

Copie la celda E2:G2 y copie sus fórmulas hasta abajo, hasta la fila 1035.

Observe que los datos numéricos de la columna F no son verdaderamente numéricos. Son texto, se puede ver que están alineados al la izquierda. Para convertirlos en verdaderamente numéricos disponemos de dos métodos.

Método 1. Cambien la fórmula de la celda F3 por la siguiente y cópiela hacia abajo.
=VALOR(EliminaEspacios(C3))

Método 2. Cambien la fórmula de la celda F3 por la siguiente y cópiela hacia abajo.
=--EliminaEspacios(C3)

Si todo ha funcionado bien ya tiene la base de datos limpia de espacios y limpia del famoso carácter 160.

Paso 5

Ahora solo falta copiar la base de datos, que son las columnas E, F y G, y pegarlas con pegado especial valores en la Hoja5.


Otra posibilidad

Otra posibilidad para resolver este caso práctico consiste en importar de página web la tabla de apellidos y luego usar Datos > Texto en columnas. La ventaja de este método es que en este caso no necesitamos usar la macro que quita el carácter 160 ya que al vincular con página web este carácter no nos da problemas. Finalmente convendría romper el vínculo.



14 comentarios:

  1. eres un verdadero maestro amigo he tenido problemas con el caracter 160y hasta el dia do hey mi vida a cambiado. gracias y exelente aporte

    ResponderEliminar
  2. hola, estoy en búsqueda de una solución a mi gran problema. Importo una planilla con muchas columnas con espacios al inicio y al final de una celda, pero al intentar reemplazar espacios por nada o realizar la función ESPACIOS, solo se quita el espacio del lado derecho de la celda, no el inicial. Con texto en columnas lo puedo hacer, pero necesito algo más rápido que me quite todos los espacios de las celdas de toda la hoja en un solo paso... intenté con la macro que indicas en el nro 2 pero no queda grabada :(

    ResponderEliminar
  3. ahora me guardó la macro, pero no me quita el primer espacio en la hoja, se van todos los espacios de las celdas que están al final de los caracteres, pero el del inicio permanece... si pudieras ayudarme te lo agradecería montones :)

    ResponderEliminar
  4. Gracias amigo realmente me siento agradecido porque ya estaba por tirar la toalla con este libro de excel... Me fue muy util esta informacion!

    ResponderEliminar
  5. Buscando por la web me topé con esta macro. Hace exactamente lo mismo, eliminar espacios sobrantes al principio, en medio y al final, con menos líneas.

    Espero les sirva :)

    Sub EliminarTotal()
    Dim celda As Range
    For Each celda In Selection
    celda.Value = LTrim(celda.Value)
    celda.Value = RTrim(celda.Value)
    celda.Value = WorksheetFunction.Trim(celda.Value)
    Next
    End Sub

    ResponderEliminar
    Respuestas
    1. Gracias, me ha servido a la primera. Más de 800 líneas en medio segundo

      Eliminar
    2. esa funcion solo quita espacion a la derecha e izquierda de la celda no elimina espacios intermedios por eso no me sirve ya que la info que tengo es de base de datos por eso la funcion que detalla esta pagina
      es perfecta :
      Function EliminaEspacios(texto As String) As String
      Dim i As Byte
      Dim n As Byte 'es la longitud de la cadena
      Dim Letra As String * 1
      Dim NewTexto As String 'nueva cadena sin espacios
      n = Len(texto)
      For i = 1 To n
      Letra = Mid(texto, i, 1)
      'chr(32) es un espacio en blanco
      'chr(160) es similar a un espacio en blanco en Excel
      If Letra <> Chr(160) And Letra <> Chr(32) Then
      NewTexto = NewTexto & Letra
      End If
      Next i
      EliminaEspacios = NewTexto
      End Function

      Eliminar
  6. Gracias Rossana muy buena contribución

    ResponderEliminar
  7. Hola te cuento que ese código es muy bueno, ahora te pregunto si de casualidad conoces un código que permita borrar todos los espacios en blanco de una celda, ejemplo tengo 200 celdas con distintos números de identificación pero el inconveniente es este celda a1 1123 444 celda a2 123 44 55 deberían quedar todas las celdas así a1 1123444 celda a2 1234455 osea todo el texto o numero pegado sin espacios. no con espacios delanteros, traseros o intermedios.

    ResponderEliminar
    Respuestas
    1. Function EliminaEspacios(texto As String) As String
      Dim i As Byte
      Dim n As Byte 'es la longitud de la cadena
      Dim Letra As String * 1
      Dim NewTexto As String 'nueva cadena sin espacios
      n = Len(texto)
      For i = 1 To n
      Letra = Mid(texto, i, 1)
      'chr(32) es un espacio en blanco
      'chr(160) es similar a un espacio en blanco en Excel
      If Letra <> Chr(160) And Letra <> Chr(32) Then
      NewTexto = NewTexto & Letra
      End If
      Next i
      EliminaEspacios = NewTexto
      End Function

      Eliminar
  8. muchas gracias, me acaba de solucionar y acortar horas de trabajo

    ResponderEliminar