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.

• http://heraldica.es/apellidos/frecuentes.htm

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.