Cuando las funciones ESPACIOS( ) o TRIM( ) no son suficientes para eliminar los espacios en blanco en Excel

Hace poco me hicieron una consulta que inicialmente me pareció muy sencilla de resolver. 

Un amigo, había importado algunos datos en una hoja de cálculo y después de hacerlo, quiso realizar algunos cálculos con estos. 

Inicialmente lo único que quería hacer era sumar en la columna H, los valores observados en las columnas F y G. Al hacerlo, se encontró con un error como el que aparece a continuación:

Le dije que eso era fácil de resolver, que utilizara la función VALOR( ) o VALUE( ) para convertir el texto en número y que aplicara la suma y el resultado fue el mismo:

Entonces le dije que de seguro tenia problemas con algunos espacios en blanco al comienzo y/o al final de cada valor y le sugerí que removiera primero los espacios y luego convirtiera a valores usando la siguiente fórmula:

Como pueden ver, tampoco sirvió de mucho. Utilizar la funcionalidad de buscar y reemplazar el espacio en blanco por nada tampoco era muy práctica dado que revisando en detalle, los valores tenían distintas cantidades de espacios en blanco.

Investigando un poco encontré que cuando importamos texto desde una aplicación o desde una página web, excel no siempre esta en capacidad de eliminar los espacios en blanco que puedan venir adjuntos en el. Cual es la razón? Resulta que no existe una sola clase de espacios en blanco, a pesar que siempre lo veamos igual, para el computador se trata de caracteres diferentes. 

Un claro ejemplo es el espacio en blanco que se suele incluir en las páginas web ( ). Este caracter no se puede limpiar con la clásica función TRIM( ) o ESPACIOS( ).

Entonces como lo debemos hacer? Debemos indicar en nuestra fórmula, cual es el caracter que vamos a sustituir, al llamarlo directamente, excel sabrá exactamente que hacer y nos sacará del problema. La fórmula construida para solucionar este caso fue la siguiente:

Exactamente: =VALUE(SUBSTITUTE(TRIM(SUBSTITUTE(F4;CHAR(160);CHAR(32)));",";"")) + VALUE(SUBSTITUTE(TRIM(SUBSTITUTE(G4;CHAR(160);CHAR(32)));",";""))

Donde yendo en la fórmula de adentro hacia afuera tenemos:

• SUBSTITUE( ) : La usamos para reemplazar los espacios que no puede capturar el excel CHAR(160) por los espacios que sí puede capturar CHAR(32).
• TRIM( ): Nos va a ayudar a eliminar los espacios en blanco tipo CHAR(32).
• SUBSTITUE( ): Esta la usamos aquí porque la coma (,) que traen los datos de la columna F y G hacen que excel los interprete como un texto.
• VALUE( ): Esta última es para estar completamente seguros que todo el texto se ha convertido en números.

Veámoslo más claramente revisando el paso a paso de lo que hace excel cuando le pedimos evaluar la fórmula:

En esta podemos apreciar los espacios en blanco al comienzo y al final del valor que queremos extraer:

 En este paso ya han sido retirados los espacios:

 Aquí nos aseguramos de que el texto se convierta en valor:

Y finalmente llegamos al dato convertido en valor, listo para usarlo en cualquier operación matemática:

Lo mismo sucede al evaluar la fórmula que apunta a la columna G:

Y al final llegamos al resultado deseado: