miércoles, octubre 17, 2012

El extraño caso del espacio inamovible en Excel (o ASCII 160)

Cuando importamos datos a una hoja de Excel, en particular de una base de datos o de la Web, las celdas pueden contener, además del valor visible, un espacio o algún carácter no imprimible.

La presencia de estos caracteres crea varios problemas: los números son interpretados por Excel como texto o los resultados de filtrar u ordenar resultan imprevisibles.
Excel nos provee con dos funciones para enfrentarnos con estos problemas: ESPACIOS() y LIMPIAR().

ESPACIOS() remueve todos los espacios excepto los espacios individuales entre palabras. Pero no siempre es así. Veamos este ejemplo

La celda A2 contiene tres caracteres visibles (abc) pero la función LARGO da un valor de 4. Este se debe a que la celda contiene un espacio en blanco después de "c".

En la celda A3 usamos ESPACIOS() para remover el espacio y vemos que ahora el largo es de 3.





Ahora veamos este caso, aparentemente idéntico



A pesar de que ambos casos parecen idénticos, en este segundo caso LIMPIAR no logra remover el espacio en blanco. Esto se debe a que la función LIMPIAR fue diseñada para remover el carácter de espacio de 7-bit ASCII (valor 32). Pero ciertos programas, y en especial datos provenientes del Web usan el carácter ASCII 160. Este carácter se utiliza comúnmente en las páginas Web como la entidad HTML. La función LIMPIAR no quita este carácter de espacio.

Una solución es usar la función SUSTITUIR()

=SUSTITUIR(A2;CARACTER(160);"")



Si se trata de un número (que ha sido convertido en texto por la presencia del espacio), agregamos un doble signo menos (--) al principio de la fórmula para forzar la conversión a número



El uso de la función SUSTITUIR puede ser menos conveniente cuando tenemos que ocuparnos de un gran número de registros. En esos caso es mejor usar una macro como ésta

Sub limpiar_todo()

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


Esta macro quita todos los espacion generados por el carácter 160 en el rango seleccionado. Si queremos quitar todos los espacios en la hoja usamos

Sub limpiar_todo()

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

7 comentarios:

  1. Muy bueno el post, esto sirve bastante en el caso señalado,incluso cuando gente que no sabe mucho de Excel crea planillas y llegan a uno con este tipo de problemas.

    ResponderBorrar
  2. Jorge,

    Gracias por este aporte.

    Bienvenido de tus vacaciones

    ResponderBorrar
  3. Hace unos dias tube el mismo problema. No podía hacer nada con el excel. El principal problema es que no sabía que caracter era. Se comportaba con un salto de línea.
    Lo que hice fue copiar el cuadro a Word. Inmediatamente lo copie nuevamente a Excel. En ese proceso se eliminó ese carácter indeseado.

    ResponderBorrar
  4. Este carácter me ha traído problemas muchas veces, en mi caso lo soluciono así: Selecciono las celdas que posiblemente contienen dicho carácter, luego pulso Ctrl+L (reemplazar) y en la casilla buscar pulso Alt+255 (codigo ascii) y en reemplazar lo dejo vacio finalmente clic en "Reemplazar todos"

    ResponderBorrar
  5. Excelente aporte, el archivo lo baje de SAP y venia con el caracter 160; esta fue la opcion más conveniente para eliminar estos molestos caracteres.

    ResponderBorrar
  6. muchas graciass por fin una respuesta que me sirvioooo despues de tanto buscar una solucion este era el problemaaaa , ufff mil gracias por el post.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.