¿Te has encontrado alguna vez lidiando con datos desordenados en Excel? ¡No estás solo! Limpiar y organizar información puede ser una tarea tediosa, especialmente cuando se trata de eliminar esos molestos caracteres o fragmentos de texto que solo complican las cosas. Aquí es donde entra en juego la magia de las expresiones regulares, o regex, una herramienta poderosa que puede transformar tu forma de trabajar con Excel. En este artículo, te enseñaremos cómo utilizar regex para simplificar el proceso de limpieza de datos, permitiéndote concentrarte en lo que realmente importa: analizar y presentar tus información. ¡Prepárate para convertirte en un maestro de la limpieza de datos!
En Excel, a menudo nos enfrentamos a la tarea de limpiar datos antes de poder utilizarlos eficientemente. Una manera efectiva de hacer esto es utilizando expresiones regulares, también conocidas como regex. ¿Alguna vez te has preguntado cómo eliminar ciertos caracteres o texto no deseados en Excel? En este artículo, aprenderás cómo utilizar regex para simplificar y agilizar el proceso de limpieza de datos en Excel. Descubre cómo esta poderosa herramienta puede ayudarte a eliminar de manera rápida y precisa esos caracteres molestos que dificultan el manejo de tus hojas de cálculo. ¡No te lo pierdas!
¿Alguna vez has pensado en lo poderoso que sería Excel si alguien pudiera enriquecer su caja de herramientas con expresiones regulares? No sólo hemos pensado sino que hemos trabajado en ello 🙂 ¡Y ahora, puedes agregar esta maravillosa función RegEx a tus propios libros y eliminar subcadenas que coincidan con un patrón en poco tiempo!
La semana pasada vimos cómo usar expresiones regulares para reemplazar cadenas en Excel. Para esto, creamos una función personalizada de Reemplazo de expresiones regulares. Al final resultó que, la función va más allá de su uso principal y no sólo puede reemplazar cadenas sino también eliminarlas. ¿Cómo es posible? En términos de Excel, eliminar un valor no es más que reemplazarlo con una cadena vacía, ¡algo en lo que nuestra función Regex es muy buena!
Cómo eliminar cadenas usando expresiones regulares – ejemplos
Como se mencionó anteriormente, para eliminar partes de texto que coincidan con un patrón, debe reemplazarlas con una cadena vacía. Entonces, una fórmula genérica toma esta forma:
RegExpReplace(texto, patrón, «», [instance_num], [match_case])
Los siguientes ejemplos muestran varias implementaciones de este concepto básico.
Eliminar todas las coincidencias o una coincidencia específica
La función RegExpReplace está diseñada para encontrar todas las subcadenas que coincidan con una expresión regular determinada. Qué ocurrencias eliminar está controlada por los 4th argumento opcional, llamado núm_instancia.
El valor predeterminado es «todas las coincidencias» – cuando el núm_instancia Se omite el argumento, se eliminan todas las coincidencias encontradas. Para eliminar una coincidencia específica, defina el número de instancia.
En las cadenas siguientes, supongamos que desea eliminar el primer número de pedido. Todos estos números comienzan con el signo almohadilla (#) y contienen exactamente 5 dígitos. Entonces, podemos identificarlos usando esta expresión regular:
Patrón: #d{5}b
La palabra límite b especifica que una subcadena coincidente no puede ser parte de una cadena más grande como #10000001.
Para eliminar todas las coincidencias, el núm_instancia El argumento no está definido:
=RegExpReplace(A5, «#d{5}b», «»)
>Para erradicar sólo la primera aparición, establecemos el núm_instancia argumento a 1:
=RegExpReplace(A5, «#d{5}b», «», 1)
>Regex para eliminar ciertos caracteres
Para eliminar ciertos caracteres de una cadena, simplemente escriba todos los caracteres no deseados y sepárelos con una barra vertical | que actúa como operador OR en expresiones regulares.
Por ejemplo, para estandarizar los números de teléfono escritos en varios formatos, primero nos deshacemos de caracteres específicos como paréntesis, guiones, puntos y espacios en blanco.
Patrón: (|)|-|.|s
=RegExpReplace(A5, «(|)|-|.|s», «»)
El resultado de esta operación es un número de 10 dígitos como «1234567890».
Para mayor comodidad, puede ingresar que la expresión regular es una celda separada y hacer referencia a esa celda usando una referencia absoluta como $A$2:
=RegExpReplace(A5, $A$2, «»)
>Y luego, puede estandarizar el formato de la forma que desee utilizando el operador de concatenación (&) y funciones de texto como DERECHA, MEDIO e IZQUIERDA.
Por ejemplo, para escribir todos los números de teléfono en el formato (123) 456-7890, la fórmula es:
=»(«&LEFT(B5, 3)&») «&MID(B5, 4, 3)&»-«&RIGHT(B5, 4)
Donde B5 es la salida de la función RegExpReplace.
>Eliminar caracteres especiales usando expresiones regulares
En uno de nuestros tutoriales, vimos cómo eliminar caracteres no deseados en Excel utilizando funciones integradas y personalizadas. ¡Las expresiones regulares hacen las cosas mucho más fáciles! En lugar de enumerar todos los caracteres que desea eliminar, simplemente especifique los que desea conservar 🙂
El patrón se basa en clases de personajes negados – se coloca un símbolo de intercalación dentro de una clase de personaje [^ ] para que coincida con cualquier carácter que NO esté entre paréntesis. El cuantificador + lo obliga a considerar los caracteres consecutivos como una sola coincidencia, de modo que se realiza un reemplazo para una subcadena coincidente en lugar de para cada carácter individual.
Dependiendo de sus necesidades, elija una de las siguientes expresiones regulares.
Para eliminar no alfanumérico caracteres, es decir, todos los caracteres excepto letras y dígitos:
Patrón: [^0-9a-zA-Z]+
Para purgar todos los personajes excepto letras, dígitos y espacios:
Patrón: [^0-9a-zA-Z ]+
Para eliminar todos los caracteres excepto letras, dígitos y guion bajopuedes usar W que representa cualquier carácter que NO sea un carácter alfanumérico o un guión bajo:
Patrón: W+
Si quieres mantener algunos otros personajespor ejemplo, signos de puntuación, colóquelos dentro de los corchetes.
Por ejemplo, para eliminar cualquier carácter que no sea una letra, dígito, punto, coma o espacio, utilice la siguiente expresión regular:
Patrón: [^0-9a-zA-Z., ]+
Esto elimina con éxito todos los caracteres especiales, pero quedan espacios en blanco adicionales.
>Para solucionar este problema, puede anidar la función anterior en otra que reemplace varios espacios con un solo carácter de espacio.
=RegExpReplace(RegExpReplace(A5,$A$2,»»), » +», » «)
O simplemente use la función TRIM nativa con el mismo efecto:
=TRIM(RegExpReplace(A5, $A$2, «»))
>Regex para eliminar caracteres no numéricos
Para eliminar todos los caracteres no numéricos de una cadena, puede utilizar esta fórmula larga o una de las expresiones regulares muy simples que se enumeran a continuación.
Haga coincidir cualquier carácter que NO sea un dígito:
Patrón:D+
Elimina caracteres no numéricos usando clases negadas:
Patrón: [^0-9]+
Patrón: [^d]+
>
Consejo. Si su objetivo es eliminar texto y dividir los números restantes en celdas separadas o colocarlos todos en una celda separada con un delimitador específico, utilice la función RegExpExtract como se explica en Cómo extraer números de una cadena usando expresiones regulares.
Regex para eliminar todo después del espacio
Para borrar todo lo que hay después de un espacio, utilice el carácter de espacio ( ) o espacio en blanco (s) para encontrar el primer espacio y .* para hacer coincidir los caracteres que aparecen después de él.
Si tiene cadenas de una sola línea que solo contienen espacios normales (valor 32 en el sistema ASCII de 7 bits), realmente no importa cuál de las siguientes expresiones regulares utilice. En el caso de cadenas de varias líneas, sí hay una diferencia.
para quitar todo después de un carácter de espaciousa esta expresión regular:
Patrón: » .*»
=RegExpReplace(A5, » .*», «»)
Esta fórmula eliminará cualquier cosa después del primer espacio en cada línea. Para que los resultados se muestren correctamente, asegúrese de activar Ajustar texto.
Para quitarlo todo después de un espacio en blanco (incluido un espacio, tabulación, retorno de carro y nueva línea), la expresión regular es:
Patrón: s.*
=RegExpReplace(A5, «s.*», «»)
Porque s coincide con algunos tipos diferentes de espacios en blanco, incluido una nueva línea (n), esta fórmula elimina todo después del primer espacio en una celda, sin importar cuántas líneas tenga.
>Regex para eliminar texto después de un carácter específico
Utilizando los métodos del ejemplo anterior, puede erradicar el texto después de cualquier carácter que especifique.
Para manejar cada línea por separado:
patrón genérico: carácter.*
En cadenas de una sola línea, esto eliminará todo después carbonizarse. En cadenas de varias líneas, cada línea se procesará individualmente porque en el tipo VBA Regex, un punto (.) coincide con cualquier carácter excepto una nueva línea.
Para procesar todas las líneas como una sola cadena:
patrón genérico: carácter(.|n)*
Para eliminar cualquier cosa después de un carácter determinado, incluidas nuevas líneas, se agrega n al patrón.
Por ejemplo, para eliminar texto después de la primera coma de una cadena, pruebe estas expresiones regulares:
Patrón: ,.*
Patrón: ,(.|n)*
En la captura de pantalla siguiente, puede examinar en qué se diferencian los resultados.
>Regex para eliminar todo antes del espacio
Cuando trabaje con largas cadenas de texto, es posible que en ocasiones desee acortarlas eliminando la misma parte de información en todas las celdas. A continuación analizaremos dos de esos casos.
Elimina todo antes del último espacio.
Al igual que en el ejemplo anterior, una expresión regular depende de su comprensión de un «espacio».
Para igualar cualquier cosa hasta el último espacioesta expresión regular servirá (se agregan comillas para hacer visible el espacio después de un asterisco).
Patrón: «.* «
Para igualar cualquier cosa antes del último espacio en blanco (incluido un espacio, tabulación, retorno de carro y nueva línea), utilice esta expresión regular.
Patrón: .*s
La diferencia es especialmente notable en cadenas de varias líneas.
>Quita todo antes del primer espacio.
Para hacer coincidir cualquier cosa hasta el primer espacio de una cadena, puede utilizar esta expresión regular:
Patrón: ^[^ ]* +
Desde el comienzo de una cadena ^, hacemos coincidir cero o más caracteres que no sean espacios [^ ]* que van seguidos inmediatamente de uno o más espacios «+». La última parte se añade para evitar posibles espacios de ventaja en los resultados.
Para eliminar el texto antes del primer espacio en cada línea, la fórmula se escribe en el modo predeterminado «todas las coincidencias» (núm_instancia omitido):
=RegExpReplace(A5, «^[^ ]* +», «»)
Para eliminar texto antes del primer espacio en la primera línea y dejar todas las demás líneas intactas, el núm_instancia El argumento se establece en 1:
=RegExpReplace(A5, «^[^ ]* +», «», 1)
>Regex para quitar todo antes del personaje.
La forma más sencilla de eliminar todo el texto antes de un carácter específico es mediante una expresión regular como esta:
patrón genérico: ^[^char]*carbonizarse
Traducido a un lenguaje humano, dice: «desde el inicio de una cadena anclada por ^, coincida con 0 o más caracteres excepto carbonizarse [^char]* hasta la primera aparición de carbonizarse.
Por ejemplo, para eliminar todo el texto antes de los primeros dos puntos, utilice esta expresión regular:
Patrón: ^[^:]*:
Para evitar espacios al principio de los resultados, agregue un carácter de espacio en blanco s* al final. Esto eliminará todo lo que esté antes de los primeros dos puntos y recortará los espacios inmediatamente después:
Patrón: ^[^:]*:s*
=RegExpReplace(A5, «^[^:]*:s*», «»)
>
Consejo. Además de las expresiones regulares, Excel tiene sus propios medios para eliminar texto por posición o coincidencia. Para aprender cómo realizar la tarea con fórmulas nativas, consulte Cómo eliminar texto antes o después de un carácter en Excel.
>
