usar expresiones regulares en fórmulas

¿Nunca puedo entender por qué las expresiones regulares no son compatibles con las fórmulas de Excel? Ahora lo son 🙂 Con nuestras funciones personalizadas, puede buscar, reemplazar, extraer y eliminar fácilmente cadenas que coincidan con un patrón específico.

A primera vista, Excel tiene todo lo que pueda necesitar para manipular cadenas de texto. Hmm… ¿qué pasa con las expresiones regulares? Vaya, no hay funciones Regex integradas en Excel. Pero nadie dice que no podamos crear los nuestros 🙂

¿Qué es la expresión regular?

A expresión regular (también conocido como expresión regular o expresión regular) es una secuencia de caracteres especialmente codificada que define un patrón de búsqueda. Usando ese patrón, puede encontrar una combinación de caracteres coincidente en una cadena o validar la entrada de datos. Si está familiarizado con la notación de comodines, puede considerar las expresiones regulares como una versión avanzada de los comodines.

Las expresiones regulares tienen su propia sintaxis que consta de caracteres especiales, operadores y construcciones. Por ejemplo, [0-5] coincide con cualquier dígito del 0 al 5.

Las expresiones regulares se utilizan en muchos lenguajes de programación, incluidos JavaScript y VBA. Este último tiene un objeto RegExp especial, que utilizaremos para crear nuestras funciones personalizadas.

¿Excel admite expresiones regulares?

Lamentablemente, no hay funciones Regex incorporadas en Excel. Para poder utilizar expresiones regulares en sus fórmulas, deberá crear su propia función definida por el usuario (basada en VBA o .NET) o instalar herramientas de terceros que admitan expresiones regulares.

Hoja de trucos de Excel Regex

Ya sea que un patrón de expresiones regulares sea muy simple o extremadamente sofisticado, se construye utilizando la sintaxis común. Este tutorial no tiene como objetivo enseñarle expresiones regulares. Para ello, existen muchos recursos en línea, desde tutoriales gratuitos para principiantes hasta cursos premium para usuarios avanzados.

A continuación proporcionamos una referencia rápida a los principales patrones RegEx que le ayudarán a comprender los conceptos básicos. También puede funcionar como hoja de referencia cuando estudies más ejemplos.

Si se siente cómodo con las expresiones regulares, puede saltar directamente a la Funciones de expresión regular.

Caracteres

Estos son los patrones más utilizados para unir ciertos personajes.

PatrónDescripciónEjemploPartidos.Carácter comodín: coincide con cualquier carácter excepto un salto de línea.Antiguo Testamentopunto, caliente, maceta, @Antiguo TestamentodCarácter de dígito: cualquier dígito del 0 al 9dEn a1bpartidos 1DCualquier carácter que NO sea un dígitoDEn a1bpartidos a y bsCarácter de espacio en blanco: espacio, tabulación, nueva línea y retorno de carro..s.En 3 centavospartidos 3 tazasSCualquier carácter que no sea un espacio en blancoS+En 30 centavospartidos 30 y centavoswCarácter de palabra: cualquier letra, dígito o guión bajo ASCIIw+En 5_gatos***partidos 5_gatosWCualquier carácter que NO sea alfanumérico ni guión bajoW+En 5_gatos***partidos *** tPestañanorteNueva líneand+En la cadena de dos líneas a continuación, coincide con 10

5 gatos
10 perros

Escapa del significado especial de un carácter, para que puedas buscarlo.

w+.

Escapa de un punto para que puedas encontrar el «.» literal carácter en una cadena

Señor., Señora., Profe.

Clases de personajes

Con estos patrones, puede unir elementos de diferentes conjuntos de caracteres.

PatrónDescripciónEjemploPartidos[characters]Coincide con cualquier carácter entre corchetesd[oi]gramoperro y excavar[^characters]Coincide con cualquier carácter que NO esté entre corchetesd[^oi]gramoPartidos dag, cavado, d1g
No coincide perro y excavar[from–to]Coincide con cualquier carácter en el rango entre corchetes[0-9]
[a-z]
[A-Z]Cualquier dígito del 0 al 9
Cualquier letra minúscula
Cualquier letra mayúscula

Cuantificadores

Los cuantificadores son expresiones especiales que especifican la cantidad de caracteres que deben coincidir. Un cuantificador siempre se aplica al carácter anterior.

PatrónDescripciónEjemploPartidos*Cero o más ocurrencias1a*1, 1a, 1aa, 1aaaetc.+Una o más ocurrenciaspo+En macetapartidos correos
En pobrepartidos caca?Cero o una ocurrenciacaminocamino, varilla*?Cero o más ocurrencias, pero la menor cantidad posible1a*?En 1a, 1aa y 1aaapartidos 1a+?Una o más ocurrencias, pero la menor cantidad posiblepo+?En maceta y pobrepartidos correos??Cero o una ocurrencia, pero la menor cantidad posibleroa??En camino y varapartidos ro{norte}Coincide con el patrón anterior n vecesd{3}exactamente 3 dígitos{norte,}Coincide con el patrón anterior no o más vecesd{3,}3 o más dígitos{Nuevo Méjico}Coincide con el patrón anterior entre n y m vecesd{3,5}De 3 a 5 dígitos

Agrupamiento

Las construcciones de agrupación se utilizan para capturar una subcadena de la cadena fuente, de modo que pueda realizar alguna operación con ella.

SintaxisDescripciónEjemploPartidos(patrón)Grupo de captura: captura una subcadena coincidente y le asigna un número ordinal(d+)En 5 gatos y 10 perroscaptura 5 (grupo 1) y 10 (Grupo 2)(?:patrón)Grupo sin captura: coincide con un grupo pero no lo captura(d+)(?: perros)En 5 gatos y 10 perroscaptura 101Contenidos del grupo 1(d+)+(d+)=2+1Coincidencias 5+10=10+5 y captura 5 y 10que están en grupos de captura2Contenido del grupo 2

Anclas

Los anclajes especifican una posición en la cadena de entrada donde buscar una coincidencia.

AnclaDescripciónEjemploPartidos^Inicio de cadena

Nota: [^inside brackets] significa «no»

^d+Cualquier número de dígitos al inicio de la cadena.

En 5 gatos y 10 perrospartidos 5

psFin de la cuerdad+$Cualquier número de dígitos al final de la cadena.

En 10 más 5 da 15coincide con 15

bLímite de palabrabalegríabPartidos alegría como palabra separada, pero no en agradable.BNO es un límite de palabraBjoyBPartidos alegría en agradablepero no como una palabra separada.

Construcción de alternancia (O)

El operando de alternancia habilita la lógica OR, por lo que puede hacer coincidir este o aquel elemento.

ConstruirDescripciónEjemploPartidos|Coincide con cualquier elemento individual separado por la barra vertical(s|sh)ellsEn ella vende conchas marinas, partidos vende y conchas

miradas a su alrededor

Las construcciones de búsqueda son útiles cuando desea hacer coincidir algo que está o no seguido o precedido por otra cosa. Estas expresiones a veces se denominan «aserciones de ancho cero» o «coincidencia de ancho cero» porque coinciden con una posición en lugar de caracteres reales.

PatrónDescripciónEjemploPartidos(?=)Previsión positivaX(?=Y)Coincide con la expresión X cuando va seguida de Y (es decir, si hay Y delante de X)(?!)Previsión negativaX(?!Y)Coincide con la expresión X si NO va seguida de Y(?<=)Mirada atrás positiva(?<=Y)XCoincide con la expresión X cuando está precedida por Y (es decir, si hay Y detrás de X)(?Mirada atrás negativa(?Coincide con la expresión X cuando NO está precedida por Y

Ahora que conoce lo esencial, pasemos a la parte más interesante: usar expresiones regulares en datos reales para analizar cadenas y encontrar la información requerida. Si necesita más detalles sobre la sintaxis, la guía de Microsoft en Lenguaje de expresión regular puede resultar útil.

Funciones RegEx personalizadas para Excel

Como ya se mencionó, Microsoft Excel no tiene funciones RegEx integradas. Para habilitar expresiones regulares, hemos creado tres funciones VBA personalizadas (también conocidas como funciones definidas por el usuario). Puede copiar los códigos de las páginas vinculadas a continuación o de nuestro libro de trabajo de muestray luego pegue sus propios archivos de Excel.

Cómo funcionan las funciones VBA RegExp

Esta sección explica la mecánica interna y puede resultar interesante para aquellos que quieran saber exactamente qué sucede en el backend.

Para comenzar a usar expresiones regulares en VBA, debe activar la biblioteca de referencia de objetos RegEx o usar la función CreateObject. Para ahorrarle la molestia de configurar la referencia en el editor VBA, elegimos este último enfoque.

El objeto RegExp tiene 4 propiedades:

• Patrón – es el patrón para que coincida en la cadena de entrada.
• Global – controla si se buscan todas las coincidencias en la cadena de entrada o solo la primera. En nuestras funciones, se establece en Verdadero para obtener todos los partidos.
• Multilínea – determina si el patrón debe coincidir con los saltos de línea en cadenas de varias líneas o solo en la primera línea. En nuestros códigos, está configurado en Verdadero para buscar en cada línea.
• Ignorar caso – define si una expresión regular distingue entre mayúsculas y minúsculas (predeterminado) o no distingue entre mayúsculas y minúsculas (establecida en Verdadero). En nuestro caso, eso depende de cómo configures el opcional. caso_partido parámetro. Por defecto, todas las funciones están distingue mayúsculas y minúsculas.

Limitaciones de VBA RegExp

Excel VBA implementa los patrones de expresiones regulares esenciales, pero carece de muchas funciones avanzadas disponibles en .NET, Perl, Java y otros motores de expresiones regulares. Por ejemplo, VBA RegExp no admite modificadores en línea como (?i) para coincidencias que no distinguen entre mayúsculas y minúsculas o (?m) para modo multilínea, búsqueda hacia atrás, clases POSIX, por nombrar algunos.

Función de coincidencia de expresiones regulares de Excel

El Coincidencia de expresiones regulares La función busca en una cadena de entrada texto que coincida con una expresión regular y devuelve VERDADERO si se encuentra una coincidencia, FALSO en caso contrario.

RegExpMatch(texto, patrón, [match_case])

Dónde:

• Texto (obligatorio): una o más cadenas para buscar.
• Patrón (obligatorio): la expresión regular que debe coincidir.
• caso_partido (opcional) – tipo de coincidencia. VERDADERO u omitido: distingue entre mayúsculas y minúsculas; FALSO: no distingue entre mayúsculas y minúsculas

El código de la función está aquí.

Ejemplo: cómo usar expresiones regulares para hacer coincidir cadenas

En el siguiente conjunto de datos, supongamos que desea identificar las entradas que contienen códigos SKU.

Dado que cada SKU comienza con 2 letras mayúsculas, seguidas de un guión, seguido de 4 dígitos, puedes relacionarlos usando la siguiente expresión.

Patrón: b[A-Z]{2}-d{4}b

Dónde [A-Z]{2} significa 2 letras mayúsculas cualesquiera de la A a la Z y d{4} significa 4 dígitos cualesquiera del 0 al 9. Un límite de palabra b indica que un SKU es una palabra separada y no parte de una cadena más grande.

Con el patrón establecido, comience a escribir una fórmula como lo hace normalmente y el nombre de la función aparecerá en la lista sugerida por Autocompletar de Excel:

>Suponiendo que la cadena original está en A5, la fórmula es la siguiente:

=RegExpMatch(A5, «b[A-Z]{2}-d{3}b»)

Para mayor comodidad, puede ingresar la expresión regular en una celda separada y usar una referencia absoluta ($A$2) para la patrón argumento. Esto garantiza que la dirección de la celda permanecerá sin cambios cuando copie la fórmula a otras celdas:

=RegExpMatch(A5, $A$2)

Para mostrar sus propias etiquetas de texto en lugar de VERDADERO y FALSO, anide RegExpMatch en la función SI y especifique los textos deseados en el valor_si_verdadero y valor_si_falso argumentos:

=IF(RegExpMatch(A5, $A$2), «Yes», «No»)

>Para obtener más ejemplos de fórmulas, consulte:

Función de extracción de expresiones regulares de Excel

El Extracción RegExp La función busca subcadenas que coincidan con una expresión regular y extrae todas las coincidencias o coincidencias específicas.

RegExpExtract(texto, patrón, [instance_num], [match_case])

Dónde:

• Texto (obligatorio): la cadena de texto para buscar.
• Patrón (obligatorio): la expresión regular que debe coincidir.
• núm_instancia (opcional): un número de serie que indica qué instancia extraer. Si se omite, devuelve todas las coincidencias encontradas (predeterminado).
• caso_partido (opcional): define si se debe hacer coincidir (VERDADERO u omitido) o ignorar (FALSO) el texto entre mayúsculas y minúsculas.

Puede obtener el código de la función aquí.

Ejemplo: cómo extraer cadenas usando expresiones regulares

Llevando nuestro ejemplo un poco más allá, extraigamos los números de factura. Para esto, usaremos una expresión regular muy simple que coincida con cualquier número de 7 dígitos:

Patrón: bd{7}b

Coloca el patrón en A2 y lograrás hacer el trabajo con esta fórmula compacta y elegante:

=RegExpExtract(A5, $A$2)

Si un patrón coincide, la fórmula extrae un número de factura; si no se encuentra ninguna coincidencia, no se devuelve nada.

>Para obtener más ejemplos, consulte: Cómo extraer cadenas en Excel usando expresiones regulares.

Función de reemplazo de expresiones regulares de Excel

El RegExpReemplazar La función reemplaza los valores que coinciden con una expresión regular con el texto que especifique.

RegExpReplace(texto, patrón, reemplazo, [instance_num], [match_case])

Dónde:

• Texto (obligatorio): la cadena de texto para buscar.
• Patrón (obligatorio): la expresión regular que debe coincidir.
• Reemplazo (obligatorio): el texto con el que se reemplazarán las subcadenas coincidentes.
• núm_instancia (opcional): la instancia a reemplazar. El valor predeterminado es «todas las coincidencias».
• caso_partido (opcional): controla si se debe hacer coincidir (VERDADERO u omitido) o ignorar (FALSO) el texto entre mayúsculas y minúsculas.

El código de la función está disponible aquí.

Ejemplo: cómo reemplazar o eliminar cadenas usando expresiones regulares

Algunos de nuestros registros contienen números de tarjetas de crédito. Esta información es confidencial y es posible que desee reemplazarla con algo o eliminarla por completo. Ambas tareas se pueden realizar con la ayuda del RegExpReemplazar función. ¿Cómo? En un segundo escenario, lo reemplazaremos con una cadena vacía.

En nuestra tabla de muestra, todos los números de tarjetas tienen 16 dígitos, que están escritos en 4 grupos separados por espacios. Para encontrarlos, replicamos el patrón usando esta expresión regular:

Patrón: bd{4} d{4} d{4} d{4}b

Para el reemplazo, se utiliza la siguiente cadena:

Reemplazo: XXXX XXXX XXXX XXXX

Y aquí tienes una fórmula completa para reemplazar números de tarjetas de crédito con información no confidencial:

=RegExpReplace(A5, «bd{4} d{4} d{4} d{4}b», «XXXX XXXX XXXX XXXX»)

Con la expresión regular y el texto de reemplazo en celdas separadas (A2 y B2), la fórmula funciona igualmente bien:

>En Excel, «eliminar» es un caso particular de «reemplazar». A eliminar números de tarjetas de crédito, simplemente use una cadena vacía («») para el reemplazo argumento:

=RegExpReplace(A5, «bd{4} d{4} d{4} d{4}b», «»)

>

Consejo. Para eliminar líneas vacías en los resultados, puede usar otra función RegExpReplace como se muestra en este ejemplo: Cómo eliminar líneas en blanco usando expresiones regulares.