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

Búsquedas con COINCIDIR en varias columnas

A partir de mañana y por los próximos diez estaré ocupado en una investigación sobre el tema “Los mejores platos y vinos en restoranes no turísticos de Toscana” (es decir, me tomo vacaciones en esa bellísima zona de Italia).

Mientras tanto veamos un tema sobre el cual he recibido varias consultas últimamente: realizar búsqueda usando COINCIDIR a través de varias columnas.

Para calcular qué posición ocupa un elemento determinado en un rango, Excel nos provee con la función COINCIDIR. Pero si el rango de búsqueda comprende más de una columna (o fila), la función da un resultado de error.

Para el caso supongamos que tenemos una serie de valores (números o texto) en el rango D1:E10. Para saber que posición ocupa el valor “14” nos veremos tentados a usar la fórmula

=COINCIDIR(B2,D1:E10,0)

donde B2 contiene el valor de búsqueda. Si bien 14 ocupa el quinto lugar en la segunda columna, el resultado es #N/A

La función COINCIDIR funciona sólo con rangos de búsqueda (matrices) de una única columna o fila.

Para hacer la búsqueda a través de varias columnas podemos combinar COINCIDIR con las funciones SI y ESERROR o, si usamos Excel 2007 o 2010, la nueva función SI.ERROR de esta manera

=SI.ERROR(COINCIDIR(B2,D1:D10,0),SI.ERROR(COINCIDIR(B2,E1:E10,0),"inexistente"))

Si la búsqueda debe hacerse en tres columnas agregamos otra función SI.ERROR

=SI.ERROR(COINCIDIR(B2,D1:D10,0),SI.ERROR(COINCIDIR(B2,E1:E10,0),SI.ERROR(COINCIDIR(B2,F1:F10,0),"inexistente")))

En Excel Clásico (97-2003) puede hacerse combinando Si con ESERROR, pero la nueva función SI.ERROR nos permite crear una fórmula mucho más compacta.

Uno de los inconvenientes de esta solución es que no nos dice en qué columna se encuentra el valor. Además, nos da la ubicación relativa del elemento en la matriz de búsqueda, pero por lo general queremos saber en qué fila se encuentra el elemento.

Para calcular la dirección de la celda que contiene el elemento buscado podemos usar DIRECCION combinada con la solución anterior

=SI.ERROR(DIRECCION(COINCIDIR(B2,D1:D10,0),4),SI.ERROR(DIRECCION(COINCIDIR(B2,E1:E10,0),5),"inexistente"))

Otra alternativa es crear una UDF (función definida por el usuario) como ésta

Function direccion_celda(Valor_Buscado, Matriz_Busqueda As Range)
    Dim rngCell As Range
   
    For Each rngCell In Matriz_Busqueda
        If rngCell.Value = Valor_Buscado Then
            direccion_celda = rngCell.Address
            Exit Function
        Else
            direccion_celda = "inexistente"
        End If
    Next rngCell

End Function

Más sobre el extraño caso del signo más en Excel

En la nota anterior vimos el extraño caso del signo más en Excel, producto de los problemas de compatibilidad con Lotus 1-2-3

y mostramos la solución.

Pero sucede que esta solución tiene efectos secundarios, como me señala el amigo Sebastián, asiduo lector del blog.

Normalmente, si ingresamos el valor “28-8-2012” en una celda, Excel lo transforma automáticamente en la fecha 28/8/2012

Veamos que pasa después de marcar las opciones de compatibilidad con Lotus 1-2-3

Excel realiza la operación (28-8-2012 = -1992), en lugar de convertirlo en fecha.

A diferencia del caso anterior, esto no parece estar relacionado al formato de la celda. También si la celda tiene el formato General, Excel realiza la operación en lugar de poner la fecha.