Limpieza de datos en la hoja: lo que puedes arreglar y dónde se rompe

Te han pasado un CSV exportado de un CRM y lo abres en la hoja. A primera vista parece limpio: tiene columnas con nombres razonables, los números parecen números, las fechas parecen fechas. Haces una tabla dinámica para ver las ventas por ciudad y aparecen "Madrid", " Madrid", "madrid" y "MADRID" como cuatro ciudades distintas. El total de Madrid está repartido en cuatro filas y el informe que ibas a entregar en quince minutos se convierte en una sesión de limpieza de dos horas. Bienvenido a la realidad del dato sucio.

La buena noticia es que la hoja de cálculo trae herramientas para limpiar los problemas más comunes. La mala noticia es que esas herramientas tienen un techo, y reconocer ese techo es tan importante como saber usarlas. Esta lección cubre las dos cosas: primero las funciones y técnicas de limpieza que resuelven la mayor parte de los casos, y después los cinco escenarios donde la hoja ya no puede y necesitas Python o SQL.

No es una lección de "memoriza estas funciones". Es una lección de diagnóstico: aprender a mirar un dato y saber qué le pasa, qué herramienta lo arregla, y cuándo la herramienta no existe en la hoja. Porque el analista que limpia sin pensar acaba creando datos nuevos que parecen limpios pero no lo son, y eso es peor que el dato sucio original: al menos del sucio desconfías.

### Los espacios invisibles: el enemigo número uno

El problema de limpieza más común y más traicionero son los espacios que no se ven. Un " Madrid" con un espacio delante no es lo mismo que "Madrid" para la hoja: son cadenas de texto distintas, y un BUSCARV que busque "Madrid" no encontrará " Madrid". La tabla dinámica las mostrará como dos ciudades. Y lo peor: a simple vista no se distinguen. Tienes que seleccionar la celda y mirar la barra de fórmulas para ver el espacio fantasma.

1=ESPACIOS(A2)
2=TRIM(A2)

Pero ESPACIOS no arregla todo. Hay caracteres invisibles que no son espacios normales: saltos de línea (carácter 10), tabuladores (carácter 9) y espacios de no separación (carácter 160). Vienen de copiar texto de páginas web, de PDF o de sistemas que codifican distinto. ESPACIOS no los toca. Para los dos primeros necesitas LIMPIAR (CLEAN), que elimina los caracteres no imprimibles.

Y ahora el tercero, que merece párrafo propio porque es el más frecuente y el más traicionero. LIMPIAR elimina los caracteres de control, que van del 0 al 31. El espacio de no separación es el 160, así que LIMPIAR NO lo quita. Ninguna de las dos funciones lo toca: ni ESPACIOS, porque no es el carácter espacio, ni LIMPIAR, porque está fuera de su rango. Hace falta SUSTITUIR(A2; CARACTER(160); " ") a mano.

Fíjate en por qué es el peor de los tres. Un salto de línea se ve: la celda tiene dos alturas. Un tabulador se nota: el texto salta. El espacio de no separación se ve EXACTAMENTE igual que un espacio normal, así que "Madrid" y "Madrid" pueden ser dos valores distintos en tu tabla dinámica sin que haya en pantalla una sola diferencia. Y es el que más aparece, porque es el que mete el HTML al copiar de una web. Así que SUSTITUIR con CARACTER(160) no es "a veces": si tus datos vienen de páginas web, es obligatorio. Cuando dos valores que se ven idénticos no se agrupan, ése es el primer sospechoso.

1=MINUSC(ESPACIOS(LIMPIAR(SUSTITUIR(A2; CARACTER(160); " "))))

### Normalizar mayúsculas y minúsculas

El segundo problema más común: "Madrid", "madrid", "MADRID" y "mADRID" son la misma ciudad para un humano, pero cuatro valores distintos para la hoja. La solución es normalizar a un formato consistente antes de hacer cualquier análisis.

1=MAYUSC(A2)        →  todo en mayúsculas: "MADRID"
2=MINUSC(A2)        →  todo en minúsculas: "madrid"
3=NOMPROPIO(A2)     →  primera letra de cada palabra en mayúscula: "Madrid"

La estrategia habitual es crear una columna auxiliar con la versión normalizada (por ejemplo, columna B con =MINUSC(ESPACIOS(A2))), usar esa columna para el análisis, y conservar la original por si la necesitas para presentar. Nunca borres el dato original hasta que confirmes que la limpieza es correcta. Es el equivalente de trabajar con una copia en vez de con el original.

### Extraer partes de un texto: IZQUIERDA, DERECHA y EXTRAE

A veces el problema no es que el dato esté sucio, sino que está mezclado. Tienes una columna "dirección" que dice "Calle Mayor 14, 3B, 28001 Madrid" y necesitas el código postal por separado para agrupar por zona. O tienes un código de producto como "CAT-PRD-001234" y necesitas extraer la categoría (CAT) o el número (001234). Para eso están las funciones de extracción.

1=IZQUIERDA(A2; 3)              →  primeros 3 caracteres: "CAT"
2=DERECHA(A2; 6)                →  últimos 6 caracteres: "001234"
3=EXTRAE(A2; 5; 3)              →  desde la posición 5, 3 caracteres: "PRD"

Para textos con separador variable (como extraer lo que hay antes de la primera coma), necesitas combinar EXTRAE con ENCONTRAR (FIND) o HALLAR (SEARCH). ENCONTRAR te da la posición de un carácter dentro del texto, y con esa posición le dices a EXTRAE cuánto coger.

1=IZQUIERDA(A2; ENCONTRAR(","; A2) - 1)

### El cero que desaparece: el error que más datos españoles ha destruido

Este apartado no va de una función. Va de un error que le pasa a todo el mundo, que es completamente invisible, y que probablemente ya te ha pasado sin que lo sepas. Abres un export con códigos postales y ves que unos tienen cinco dígitos y otros cuatro. La conclusión natural es que los datos vienen sucios y que hay que normalizarlos. Y es una conclusión equivocada.

Un código postal español tiene SIEMPRE cinco dígitos. Si ves cuatro es porque ese código empieza por cero —los de las nueve provincias que van del 01 al 09, de Álava a Burgos —Álava, Albacete, Alicante, Almería, Ávila, Badajoz, Baleares, Barcelona y Burgos—— y en algún paso del camino la columna se guardó como NÚMERO. Y para un número, 08001 y 8001 son exactamente el mismo valor: el cero de la izquierda no aporta cantidad, así que se descarta. Nadie lo pidió y nadie avisó.

1"08001" como texto   →  5 caracteres  →  08001   [OK]
2 08001  como número  →  4 caracteres  →   8001   [ERROR]  el cero se ha ido

La causa de fondo es conceptual y conviene tenerla clara, porque explica muchos otros desastres: un código postal NO es un número. Con un número puedes hacer aritmética, y a nadie se le ocurre sumar dos códigos postales ni calcular su media. Es un identificador, o sea texto que resulta que usa dígitos. Lo mismo pasa con el NIF, con el IBAN, con el número de teléfono, con el código de producto y con el número de factura. La regla práctica: si no tiene sentido sumarlo, no es un número.

El arreglo tiene dos pasos y el orden importa. El bueno es AL IMPORTAR: en el asistente de importación de texto, o en Datos > Obtener datos, marca esa columna como Texto antes de que la hoja decida por ti. Así el cero nunca se pierde. El parche, cuando ya te lo han comido y no tienes el fichero original, es =TEXTO(A2; "00000"), que rellena con ceros por la izquierda hasta cinco dígitos. Funciona, pero es un parche: si además el dato ha pasado por otra transformación, puede que ya no puedas recuperar el original.

Y un aviso que ahorra un mal rato: el formato de celda NO arregla esto. Poner la columna con formato "Texto" DESPUÉS de que el valor ya sea 8001 no devuelve el cero, porque el cero ya no existe en el dato: solo cambia cómo se pinta un número que ya perdió información. Es la diferencia entre el valor de una celda y su formato, y es la misma distinción que verás en la lección de fechas. El formato es maquillaje; el tipo es el hueso.

### Buscar y Reemplazar: la limpieza más rápida

Antes de escribir una fórmula, prueba con Buscar y Reemplazar (Ctrl+H en Windows, Cmd+Shift+H en Mac con Google Sheets). Es la herramienta más rápida para limpiezas masivas simples: reemplazar "S.L." por "SL" en toda la columna de nombres de empresa, quitar todos los guiones de los teléfonos, cambiar "si" por "Si" en una columna de respuestas. No necesita columna auxiliar: modifica los datos en el sitio.

Tiene dos modos que la gente no conoce. "Buscar en toda la celda" frente a "buscar dentro del contenido": si buscas "e" y reemplazas por nada, en modo "toda la celda" solo borra las celdas que contienen SOLO "e"; en modo "contenido" quita todas las "e" de todas las celdas, que probablemente no es lo que querías.

Y hay una cosa sobre los comodines que se cuenta mal en todas partes, incluida mucha documentación de internet, así que conviene tenerla clara porque puede vaciarte una columna. En Excel los comodines NO se pueden activar ni desactivar: el asterisco (cualquier cosa) y la interrogación (un solo carácter) están SIEMPRE activos en Buscar y Reemplazar, y no hay casilla que los apague. La casilla "usar comodines" que muchos recuerdan es de Word, no de Excel.

Y eso es una noticia peor de lo que parece. Piensa en qué pasa si buscas un asterisco de verdad —porque una columna trae `Producto*` para marcar los descatalogados— y lo reemplazas por nada, sin marcar "toda la celda". El asterisco no significa asterisco: significa "cualquier cosa". Así que coincide con el contenido completo de cada celda y te VACÍA la columna entera. Sin aviso y sin preguntar. Para buscar un asterisco literal hay que escaparlo con una virgulilla delante: `~*` para el asterisco y ~? para la interrogación. Apúntalo, porque es de las cosas que solo se aprenden después de perder una columna.

### Texto en columnas: partir un campo en varios

A veces recibes datos donde la información está compactada en una sola celda: "Garcia Lopez, Ana" (apellidos y nombre juntos), "Madrid|28001|Centro" (ciudad, código postal y barrio separados por pipe), "2024-03-15 14:32:00" (fecha y hora juntas). Texto en columnas (Data > Text to Columns en Excel, o Datos > Dividir texto en columnas en Google Sheets) parte una columna en varias usando un separador que tú elijas.

El proceso es simple: seleccionas la columna, eliges el separador (coma, punto y coma, tabulador, espacio, otro carácter que definas), y la herramienta crea tantas columnas como partes resulten. Es el equivalente de un .split() en Python o un SPLIT en SQL. Funciona bien cuando el separador es consistente; se rompe cuando no lo es. Si algunos registros tienen dos comas y otros tienen tres, el resultado tendrá columnas desalineadas.

### Quitar duplicados: con cuidado

Tanto Excel como Google Sheets tienen una función de "Quitar duplicados" (en Excel: Datos > Quitar duplicados; en Sheets: Datos > Limpieza de datos > Quitar duplicados). Seleccionas las columnas que quieres comparar y la herramienta elimina las filas que son idénticas en esas columnas, dejando solo la primera ocurrencia.

Parece simple, pero hay tres trampas que el analista novato no ve. Primera: la herramienta no te dice CUÁLES eliminó. Solo te dice "se han eliminado 47 duplicados". Si alguno de esos 47 no era un duplicado real (por ejemplo, dos pedidos legítimos del mismo cliente en la misma fecha), ya has perdido datos. Segunda: "primera ocurrencia" depende del orden de la tabla. Si no has ordenado antes, qué fila se queda puede ser aleatoria. Tercera: si no normalizaste antes (espacios, mayúsculas), dos filas que son el mismo dato pero escritas distinto NO se detectan como duplicados.

### Los cinco límites de la limpieza en hoja

Hasta aquí, todo lo que hemos visto se puede hacer en la hoja con las funciones de siempre. Pero hay cinco situaciones donde la hoja dice basta y necesitas Python (o SQL, según el caso). Reconocerlas a tiempo te ahorra horas de frustración intentando hacer lo imposible con fórmulas cada vez más largas.

El primer límite es la limpieza condicional compleja. "Si el teléfono empieza por 34, quita los dos primeros dígitos; si empieza por +34, quita los tres primeros; si tiene guiones, quítalos; si tiene espacios, quítalos; si tiene paréntesis, quítalos; y al final comprueba que quedan exactamente 9 dígitos". Eso en la hoja es un SI anidado con SUSTITUIR dentro de SUSTITUIR dentro de SUSTITUIR, ilegible e imposible de depurar. En Python es una función de diez líneas con if/elif que cualquiera puede leer y modificar.

El segundo límite son los patrones irregulares. Las expresiones regulares (regex) son un lenguaje para describir patrones de texto: "una secuencia de 8 dígitos seguidos de una letra mayúscula" (un NIF), "algo@algo.algo" (un email), "un número de 4 dígitos, un guión, otro de 2, otro guión, otro de 2" (una fecha ISO). Google Sheets tiene REGEXEXTRACT y REGEXMATCH desde hace años, y Excel de Microsoft 365 ya tiene REGEXTEST, REGEXEXTRACT y REGEXREPLACE. O sea que la frontera ya no es "regex sí o no": las versiones de licencia perpetua siguen sin ellas, pero si tienes 365 las tienes.

Entonces, si las dos hojas ya tienen regex, ¿dónde está el límite? Se ha movido, y esto es lo que hay que entender: el límite ya no es una transformación, es una TUBERÍA de transformaciones. Una limpieza de verdad casi nunca es un solo paso. Es quitar los caracteres invisibles, luego normalizar los espacios, luego extraer el trozo que interesa, luego comprobar que cumple un formato, luego decidir qué haces con las filas que no lo cumplen, y todo eso encadenado y aplicado a cada fila. En la hoja eso se convierte en cinco columnas auxiliares, o en una fórmula anidada que nadie va a mantener. En Python son cinco líneas seguidas que se leen de arriba abajo, con un nombre para cada paso. La diferencia no está en si puedes hacer una cosa: está en si puedes encadenar diez y seguir entendiendo lo que hiciste seis meses después.

El tercer límite es el volumen. Con 10.000 filas, una columna auxiliar con ESPACIOS+MINUSC recalcula en un segundo. Con 500.000 filas, la hoja tarda un minuto cada vez que tocas algo, y el fichero pesa 80 MB y tarda en abrirse. Con un millón todavía cabe —el límite duro de Excel son 1.048.576 filas por hoja— pero limpiarlo ahí dentro es inviable: cada recálculo se va a minutos y el fichero se vuelve imposible de compartir. Python con pandas procesa un millón de filas en dos segundos sin pestañear.

El cuarto límite es la repetibilidad. Si cada lunes recibes el mismo CSV y tienes que aplicar la misma limpieza, hacerlo a mano en la hoja es arriesgarte a que un lunes te saltes un paso, o lo hagas distinto, o se lo dejes a un compañero que no sabe el orden. Un script de Python hace exactamente lo mismo cada vez que lo ejecutas, y queda documentado: el código ES la documentación de tu proceso.

El quinto límite es la validación cruzada. "Comprueba que todos los códigos postales de la columna F existen en la tabla oficial de correos" requiere un JOIN entre tus datos y una tabla externa. En la hoja puedes hacer un BUSCARV contra otra pestaña, pero si la tabla oficial tiene 52.000 códigos y tus datos tienen 100.000 filas, la hoja se arrastra. En SQL o Python esa comprobación es instantánea.

### Resumen

• ▹ESPACIOS (TRIM) + LIMPIAR (CLEAN): primera línea de defensa contra caracteres invisibles.
• ▹MAYUSC/MINUSC/NOMPROPIO: normalizar texto antes de agrupar o cruzar.
• ▹IZQUIERDA/DERECHA/EXTRAE + ENCONTRAR: extraer partes de texto con formato fijo.
• ▹Buscar y Reemplazar (Ctrl+H): limpieza masiva rápida, pero destructiva.
• ▹Texto en columnas: partir campos compuestos usando un separador.
• ▹Quitar duplicados: solo después de diagnosticar cuáles son reales.
• ▹Límites de la hoja: lógica condicional, regex, volumen, repetibilidad, validación cruzada. Cuando cruces dos, pasa a Python.