Cómo Usar Expresiones Regulares En Excel – Guía Completa

¿Te gustaría aprender a usar las expresiones regulares en Excel? Puedes utilizar funciones de texto para manipular cadenas de texto en Excel. Sin embargo, no puedes usarlos con expresiones regulares. VBA es tu única opción. En esta guía, te mostraremos cómo usar expresiones regulares en Excel. Te invitamos a quedarte con nosotros.

¿Qué es una expresión regular en Excel?

Una expresión regular (también conocida como regex o regexp en breve) es una cadena de texto especial para especificar un patrón de búsqueda. Son como comodines. En lugar de especificar la cantidad de caracteres, puede crear patrones para encontrar un grupo específico de caracteres, como buscar entre «b» y «o«, usar la lógica OR, excluir algunos caracteres o repetir valores.

También te puede interesar leer sobre: Cómo Ocultar Filas En Excel De Forma Sencilla. 3 Métodos Fáciles

 Las expresiones regulares se usan comúnmente para analizar y reemplazar operaciones de texto para todos los lenguajes de programación. Para usar expresiones regulares en Excel, usaremos VBA.

Patrón	Descripción	Muestras
^ jack	comienza con «jack»	Jack-of-all-trades, casa de Jack
jack $	termina con «jack»	secuestrar
^ jack $	es exactamente «jack»	Jack
colo [u] {0,} r	puede incluir «u» al menos 0 veces	color, color (no color)
col [o | u] r	incluye «o» o «u»	color, colur (no color)
col [^ u] r	acepta cualquier carácter excepto «u»	color (ni color ni color)

Cómo usar expresiones regulares

Comencemos a usar expresiones regulares en Excel abriendo VBA.

1. Paso 1: Presiona las teclas Alt + F11 para abrir la ventana VBA (Visual Basic para Aplicaciones). Agrega un módulo para ingresar tu código.

2. Paso 2: El siguiente paso es agregar una referencia de expresión regular a VBA.
3. Paso 3: Haz clic en Herramientas> Referencias en la barra de herramientas de VBA.
4. Paso 4: Busca y verifica el elemento Microsoft VBScript Regular Expressions 5.5 en la ventana Referencias.
5. Paso 5: Haz clic en Aceptar para agregar la referencia.

Usando la referencia de VBScript, podemos crear un objeto de expresión regular, que se define como RegExp en VBA. Un objeto RegExp tiene 4 propiedades y 3 métodos:

Mira También Impresión De Una Imagen En Varias Hojas Para Ampliarla (Creación De Un Póster)

Propiedades

Nombre	Escribe	Descripción
Global	Booleano	Establezca True para buscar todos los casos que coincidan con el patrón. Establezca Falso para encontrar la primera coincidencia.
Ignorar caso	Booleano	Establezca True para no realizar búsquedas que distingan entre mayúsculas y minúsculas. Establezca Falso para realizar búsquedas que distingan entre mayúsculas y minúsculas.
Multilínea	Booleano	Establezca True si su cadena tiene varias líneas y desea realizar la búsqueda en todas las líneas.
Patrón	Cuerda	El patrón de expresión regular que desea buscar.

 

Métodos

Nombre	Argumentos	Descripción
Ejecutar	sourceString como cadena	Devuelve una matriz que contiene todas las apariciones del patrón coincidente en la cadena.
Reemplazar	sourceString como cadena replaceVar como variante	Devuelve una cadena en la que todas las apariciones del patrón en la cadena se reemplazan con la cadena replaceVar .
Prueba	sourceString como cadena	Devuelve True si hay una coincidencia. De lo contrario, falso .

Muestras de código

Ahora, veamos las muestras de código RegExFind:

Una función que devuelve VERDADERO / FALSO si el patrón se encuentra en una cadena

Función pública RegExFind (str como cadena, pat como cadena) como booleano

‘Definir el objeto de expresión regular

Mira También Cómo Insertar Emojis En Word. 6 Métodos Sencillos

Dim RegEx como nueva expresión regular

‘Configurar propiedades de expresión regular

Con RegEx.

Global = False ‘No todas las ocurrencias son necesarias ya que una sola ocurrencia es suficiente

.IgnoreCase = True ‘Sin distinción entre mayúsculas y minúsculas

.MultiLine = True ‘Marque todas las líneas

Mira También Cómo Insertar Notas Musicales En Word. 5 Pasos

.Patrón = patrón de palmaditas

Terminar con

RegExFind = RegEx.Test (str)

Función final

Después de escribir el código, puedes utilizar esta función como una función normal de Excel.

Mira También Cómo Usar El Puntero Láser En Powerpoint (Verde, Rojo Y Azul)

Una función que reemplaza el patrón con una cadena dada

Función pública RegExReplace (str como cadena, pat como cadena, replaceStr como cadena) como cadena:

‘Definir el objeto de expresión regular

Dim RegEx como nueva expresión regular

‘Configurar propiedades de expresión regular

Con RegEx

.Global = False ‘No todas las ocurrencias son necesarias ya que una sola ocurrencia es suficiente

.IgnoreCase = True ‘Sin distinción entre mayúsculas y minúsculas

.MultiLine = True ‘Marque todas las líneas

.Patrón = patrón de palmaditas

Terminar con

RegExReplace = RegEx.Replace (str, replaceStr) ‘Devuelve la cadena modificada con el valor de reemplazo

Función final

El siguiente ejemplo muestra cómo reemplazar cadenas que comienzan con «col», continúan con 0 o 1 apariciones de «o» y «u» simple, y finalmente terminan con un carácter «r» con una cadena «Color».

Regex Match en Excel: expresión regular que coincide con ejemplos

En esta parte del tutorial, veremos en profundidad cómo usar expresiones regulares para hacer coincidir cadenas en Excel.

Cuando necesites encontrar un cierto valor en un rango de celdas, usaría la función COINCIDIR. Al buscar una cadena específica en una celda, las funciones BUSCAR y BUSCAR son útiles. ¿Y cómo saber si una celda contiene información que coincide con un patrón determinado? Obviamente, usando expresiones regulares. ¡Pero Excel no admite expresiones regulares! No te preocupes, aquí te lo explicamos

Función Excel VBA Regex para hacer coincidir cadenas

Como queda bastante claro en el encabezado, para usar expresiones regulares en Excel, debes crear tu propia función. Afortunadamente, el VBA de Excel tiene un objeto RegExp incorporado, que puedes usar en tu código como se muestra a continuación:

Public Function RegExpMatch(input_range As Range, pattern As String, Optional match_case As Boolean = True) As Variant

    Dim arRes() As Variant ‘array to store the results

    Dim iInputCurRow, iInputCurCol, cntInputRows, cntInputCols As Long ‘index of the current row in the source range, index of the current column in the source range, count of rows, count of columns

    On Error GoTo ErrHandl

    RegExpMatch = arRes

    Set regex = CreateObject(«VBScript.RegExp»)

    regex.pattern = pattern

    regex.Global = True

    regex.MultiLine = True

    If True = match_case Then

        regex.ignorecase = False

    Else

        regex.ignorecase = True

    End If

    cntInputRows = input_range.Rows.Count

    cntInputCols = input_range.Columns.Count

    ReDim arRes(1 To cntInputRows, 1 To cntInputCols)

    For iInputCurRow = 1 To cntInputRows

        For iInputCurCol = 1 To cntInputCols

            arRes(iInputCurRow, iInputCurCol) = regex.Test(input_range.Cells(iInputCurRow, iInputCurCol).Value)

        Next

    Next

    RegExpMatch = arRes

    Exit Function

    ErrHandl:

        RegExpMatch = CVErr(xlErrValue)

End Function

Pega el código en el editor de VBA y tu nueva función RegExpMatch estará lista para usar.

NOTA: Después de insertar el código, recuerda guardar tu archivo como un libro de trabajo habilitado para macros (.xlsm).

Sintaxis de RegExpMatch

La función RegExpMatch comprueba si alguna parte de la cadena de origen coincide con una expresión regular. El resultado es un valor booleano: VERDADERO si se encuentra al menos una coincidencia, FALSO en caso contrario.

Nuestra función personalizada tiene 3 argumentos: los dos primeros son obligatorios y el último es opcional:

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

Dónde:

1. Texto(obligatorio): una o más cadenas para buscar. Se puede proporcionar como referencia de celda o rango.
2. Patrón(obligatorio): la expresión regular que debe coincidir. Cuando se coloca directamente en una fórmula, un patrón debe ir entre comillas dobles.
3. Match_case(opcional): define el tipo de coincidencia. Si es TRUE o se omite (predeterminado), se realiza una coincidencia sensible a mayúsculas y minúsculas; si es FALSO, no distingue entre mayúsculas y minúsculas.

La función funciona en todas las versiones de Excel 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013 y Excel 2010.

3 cosas que debes saber sobre RegExpMatch

Antes de comenzar con los cálculos prácticos, debes tener en cuenta los siguientes puntos que aclaran algunos aspectos técnicos:

1. La función puede procesar una sola celda o un rango de celdas. En el último caso, los resultados se devuelven en las celdas vecinas en forma de una matriz dinámica o rango de derrame, como se muestra en este ejemplo.
2. De forma predeterminada, la función distingue entre mayúsculas y minúsculas. Para ignorar las mayúsculas y minúsculas, establezca el argumento match_case en FALSE. Debido a las limitaciones de VBA Regexp, no se admite el patrón que no distingue entre mayúsculas y minúsculas.
3. Si no se encuentra un patrón válido, la función devuelve FALSO; si el patrón no es válido, un # ¡VALOR! se produce un error.

A continuación, encontrarás algunos ejemplos de coincidencias de expresiones regulares que se crearon con fines de demostración. No podemos garantizar que nuestros patrones funcionen a la perfección con una gama más amplia de datos de entrada en tus hojas de trabajo reales. Antes de poner en producción, asegúrate de probar y ajustar nuestros patrones de muestra de acuerdo con tus necesidades.

Cómo usar expresiones regulares para hacer coincidir cadenas en Excel

Cuando todas las cadenas que quieres hacer coincidir tienen el mismo patrón, las expresiones regulares son una solución ideal.

Supongamos que tienes un rango de celdas (A5: A9) que contienen varios detalles sobre algunos elementos. Quieres saber qué celdas tienen SKU. Suponiendo que cada SKU consta de 2 letras mayúsculas, un guión y 3 dígitos, puede unirlos con la siguiente expresión.

• Patrón: \ b [AZ] {2} – \ d {3} \ b
• Donde [AZ] {2} significa 2 letras mayúsculas cualesquiera de la A a la Z y \ d {3} significa 3 dígitos del 0 al 9.
• El carácter \ b denota un límite de palabra, lo que significa que un SKU es una palabra separada, y no parte de una cuerda más grande, como 23-MAR-2022.

Con el patrón establecido, podemos pasar a escribir una fórmula. Esencialmente, usar una función personalizada no es diferente de una nativa. Tan pronto como comience a escribir una fórmula, el nombre de la función aparecerá en la lista sugerida por Autocompletar de Excel.

Sin embargo, hay un par de matices en Dynamic Array Excel (Microsoft 365 y Excel 2021) y Excel tradicional (2019 y versiones anteriores).

Coincidir cadena en una celda

Para hacer coincidir una cadena en una sola celda, consulte esa celda en el primer argumento. Se supone que el segundo argumento contiene una expresión regular.

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

El patrón también se puede mantener en una celda predefinida, que está bloqueada con una referencia absoluta ($ A $ 2):

• =RegExpMatch(A5, $A$2)

Después de ingresar la fórmula en la primera celda, puede arrastrarla a todas las demás filas. Este método funciona a la perfección en todas las versiones de Excel.

Coincidir cadenas en varias celdas a la vez

Para hacer coincidir varias cadenas con una sola fórmula, incluya una referencia de rango en el primer argumento:

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

En Excel 365 y Excel 2021 que admiten matrices dinámicas, funciona de esta manera: escribe la fórmula en la primera celda, presiona Ingresar y la fórmula se derrama automáticamente en las celdas siguientes.

En Excel 2019 y versiones anteriores, solo funciona como una fórmula de matriz CSE tradicional, que se ingresa en un rango de celdas y se completa presionando la tecla Ctrl + Mayús + Entrar llaves juntas.

Regex para que coincida con el número

Para hacer coincidir cualquier dígito del 0 al 9, usa el carácter \ d en la expresión regular. Dependiendo de tu tarea particular, agrega un cuantificador adecuado o creas un patrón más complejo.

Regex para que coincida con cualquier número

Para hacer coincidir cualquier número de cualquier longitud, coloca el cuantificador + justo después del carácter / d, que dice buscar números que contengan 1 o más dígitos.

• Patrón: \ d +
• =RegExpMatch(A5:A9, «\d+»)

Regex para que coincida con el número de longitud específica

Si tu objetivo es hacer coincidir valores numéricos que contengan un cierto número de dígitos, utilice \ d junto con un cuantificador apropiado.

Por ejemplo, para hacer coincidir los números de factura que constan de exactamente 7 dígitos, usaría \ d {7}. Sin embargo, debes tener en cuenta que coincidirá con 7 dígitos en cualquier lugar de la cadena, incluido un número de 10 o 100 dígitos. Si esto no es lo que estás buscando, coloca la palabra límite \ b en ambos lados.

• Patrón: \ b \ d {7} \ b
• =RegExpMatch(A5:A9, «\b\d{7}\b»)

Regex para que coincida con los números de teléfono

Dado que los números de teléfono se pueden escribir en varios formatos, hacer coincidirlos requiere una expresión regular más sofisticada.

En el siguiente conjunto de datos, buscaremos números de 10 dígitos que tengan 3 dígitos en los primeros 2 grupos y 4 dígitos en el último grupo. Los grupos se pueden separar con un punto, un guión o un espacio. El primer grupo puede estar entre paréntesis o no.

• Patrón: (\ (\ d {3} \) | \ d {3}) [- \. \ S]? \ D {3} [- \. \ S]? \ D {4} \ b

Desglosando esta expresión regular, esto es lo que obtenemos:

1. La primera parte (\ (\ d {3} \) | \ d {3}) coincide con un número de 3 dígitos entre paréntesis o sin paréntesis.
2. ¿El [- \. \ S]? parte significa 0 o 1 aparición de cualquier carácter entre corchetes: guión, punto o espacio en blanco.
3. A continuación, hay un grupo más de 3 dígitos d {3} seguido de un guión, punto o espacio en blanco [\ – \. \ S]? apareciendo 0 o 1 vez.
4. El último grupo de 4 dígitos \ d {4} va seguido de un límite de palabra \ b para dejar en claro que un número de teléfono no puede formar parte de un número mayor.

Con la cadena original en A5 y la expresión regular en A2, la fórmula toma esta forma:

=RegExpMatch (A5, $A$2)

… y funciona exactamente como se esperaba:

NOTAS:

1. Los códigos internacionales no se comprueban, por lo que pueden estar presentes o no.
2. En las expresiones regulares, \ s representa cualquier carácter de espacio en blanco, como un espacio, tabulación, retorno de carro o nueva línea. Para permitir solo espacios, usa [- \. ] en lugar de [- \. \ s].

Regex para NO coincidir con el personaje

Para buscar cadenas que NO contengan un determinado carácter, puedes usar clases de caracteres negadas [^] que coincidan con cualquier cosa que NO esté entre corchetes. Por ejemplo:

1. [^ 13] coincidirá con cualquier carácter que no sea 1 o 3.
2. [^ 1-3] coincidirá con cualquier carácter que no sea 1, 2 o 3 (es decir, cualquier dígito del 1 al 3).

En una lista de números de teléfono, suponga que desea buscar aquellos que no tienen un código de país. Teniendo en cuenta que cualquier código internacional incluye el signo +, puede usar la clase de caracteres [^ \ +] para buscar cadenas que no contengan un signo más.

Es importante darse cuenta de que la expresión anterior coincide con cualquier carácter que no sea +. Debido a que un número de teléfono puede estar en cualquier lugar de una cadena, no necesariamente al principio, se agrega el cuantificador * para verificar cada carácter subsiguiente.

Los anclajes start ^ y end $ garantizan que se procese toda la cadena. Como resultado, obtenemos la siguiente expresión regular que dice «no coincide con el carácter + en ninguna posición de la cadena».

• Patrón: ^ [^ \ +] * $
• =RegExpMatch(A5, «^[^\+]*$»)

Regex para NO coincidir con la cadena

Aunque no existe una sintaxis de expresión regular especial para no coincidir con una cadena específica, puede emular este comportamiento mediante una búsqueda anticipada negativa .

Supongamos que desea encontrar cadenas que no contengan la palabra «limones». Esta expresión regular funcionará de maravilla:

• Patrón : ^ ((?! limones).) * $

Obviamente, aquí se necesita alguna explicación. La mirada hacia delante negativa (?! Limones) mira hacia la derecha para ver si no hay ninguna palabra «limones» delante. Si «limones» no está allí, entonces el punto coincide con cualquier carácter excepto con un salto de línea.

La expresión anterior realiza solo una verificación, y el cuantificador * la repite cero o más veces, desde el inicio de la cadena anclada por ^ hasta el final de la cadena anclada por $.

Para ignorar el caso del texto, establecemos el tercer argumento en FALSO para que nuestra función no distinga entre mayúsculas y minúsculas:

• =RegExpMatch (A5, $A$2, FALSE)

Consejos y notas:

1. La expresión regular anterior solo funciona para cadenas de una sola línea. En el caso de cadenas de milti-line, los caracteres ^ y $ coinciden con el principio y el final de cada línea en lugar del principio y el final de la cadena de entrada, por lo tanto, la expresión regular solo busca en la primera línea.
2. Para hacer coincidir cadenas que no comienzan con cierto texto, usa una expresión regular como ^ (?! limones). * $
3. Para hacer coincidir cadenas que no terminan con cierto texto, incluya el ancla de cadena final en el patrón de búsqueda: ^ ((?! limones $).) * $

Coincidencia que no distingue entre mayúsculas y minúsculas

En las expresiones regulares clásicas, hay un patrón especial para la coincidencia que no distingue entre mayúsculas y minúsculas (? I), que no se admite en VBA RegExp. Para superar esta limitación, nuestra función personalizada acepta la 3 rd argumento opcional llamado match_case.

Para hacer una coincidencia que no distinga entre mayúsculas y minúsculas, simplemente configúrelo en FALSE.

Supongamos que quieres identificar fechas como 1-Mar-22 o 01-MAR-2022. Para hacer coincidir los formatos dd-mmm-aaaa y d-mmm-aaa, usamos la siguiente expresión regular.

• Patrón: \ b \ d {1,2} – (Ene | Feb | Mar | Abr | May | Jun | Jul | Ago | Sep | Oct | Nov | Dic) – (\ d {4} | \ d {2} )\B

Nuestra expresión busca un grupo de 1 o 2 dígitos, seguido de un guión, seguido de cualquiera de las abreviaturas del mes separadas por | que habilita la lógica OR, seguida de un grupo de 4 o 2 dígitos.

¿Por qué no usar un patrón más simple como \ d {1,2} – [A-Za-z] {3} – \ d {2,4} \ b? Para evitar coincidencias de falsos positivos como 01-ABC-2020.

Ingresa el patrón en A2 y obtendrás la siguiente fórmula:

• =RegExpMatch (A5, $A$2, FALSE)

Regex para que coincida con direcciones de correo electrónico válidas

Como se conoce generalmente, una dirección de correo electrónico consta de 4 partes: nombre de usuario, símbolo @, nombre de dominio (servidor de correo) y dominio de nivel superior (como .com, .edu, .org, etc.). Para verificar la validez de la dirección de correo electrónico, necesitaremos replicar la estructura anterior usando expresiones regulares.

Patrón : \ b [\ w \. \ -] + @ [A-Za-z0-9] + [A-Za-z0-9 \. \ -] * [A-Za-z0-9] + \. [A-Za-z] {2.24} \ b

Para comprender mejor lo que está sucediendo aquí, echemos un vistazo más de cerca a cada parte:

• Nombre de usuario puede incluir letras, números, guiones bajos, puntos y guiones. Teniendo en cuenta que \ w coincide con cualquier letra, dígito o guión bajo, obtenemos la siguiente expresión regular: [\ w \. \ -] +
• Nombre de dominio puede incluir letras mayúsculas y minúsculas, dígitos, guiones (pero no en la primera o última posición) y puntos (en el caso de subdominios). Como no se permiten guiones bajos, en lugar de \ w estamos usando 3 conjuntos de caracteres diferentes: [A-Za-z0-9] + [A-Za-z0-9 \. \ -] * [A-Za-z0-9 ] +
• Dominio de nivel superior consta de un punto seguido de letras mayúsculas y minúsculas. Puede contener de 2 a 24 letras (el TLD más largo que existe actualmente): \. [A-Za-z] {2,24}

Nota. El patrón asume que el nombre de dominio contiene 2 o más caracteres alfanuméricos.

Con el texto original en A5 y el patrón en A5, la fórmula toma esta forma:

=RegExpMatch (A5, $A$2)

O puede usar una expresión regular más simple para la validación de correo electrónico con un juego de caracteres en minúsculas o mayúsculas:

Patrón : \ b [\ w \. \ -] + @ [a-z0-9] + [a-z0-9 \. \ -] * [a-z0-9] + \. [Az] {2, 24} \ b

Pero haz que su fórmula no distinga entre mayúsculas y minúsculas:

=RegExpMatch (A5, $A$2, FALSE)

Fórmula IF de Excel con expresión regular de coincidencia

Debido al hecho de que las funciones integradas y personalizadas van bien, no hay nada que le impida usarlas juntas en una sola fórmula. Para devolver o calcular algo si una expresión regular coincide y algo más si no coincide, incruste la función personalizada RegExpMatch en el texto lógico de IF:

• SI (Coincidencia_regExp (…), [valor_si_verdadero], [valor_si_falso])

Por ejemplo, si una cadena en A5 contiene una dirección de correo electrónico válida, puede devolver «Sí»; De otra manera no».

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

Cuenta si la expresión regular coincide

Debido a que las funciones nativas de Excel no admiten expresiones regulares, no es posible poner una expresión regular directamente en la función COUNTIS o COUNTIFS. Afortunadamente, puede emular esta funcionalidad utilizando nuestra función personalizada.

Supongamos que ha usado una expresión regular para hacer coincidir números de teléfono y generar los resultados en la columna B. Para averiguar cuántas celdas contienen números de teléfono, solo necesita contar los valores VERDADEROS en B5: B9. Y eso se puede hacer fácilmente usando la fórmula estándar CONTAR.SI :

=COUNTIF(B5:B9, TRUE)

¿No desea columnas adicionales en su hoja de trabajo? No hay problema. Teniendo en cuenta que nuestra función personalizada puede procesar varias celdas a la vez y que SUM de Excel puede sumar valores en una matriz, esto es lo que debe hacer:

1. Proporciona una referencia de rango a RegExpMatch, por lo que devuelve una matriz de valores VERDADERO y FALSO.
2. Utiliza una doble negación (-) para convertir los valores lógicos en unos y ceros.
3. Obtén la función SUMA para sumar unos y ceros en la matriz resultante.

=SUM(–RegExpMatch(A5:A9, $A$2))

Cómo utilizar la función personalizada RegexMatch

Suponiendo que tiene instalada la última versión de Ultimate Suite (2021.4 o posterior), puedes crear una fórmula Regex Match en dos simples pasos:

1. Paso 1: En la pestaña Datos de Ablebits, en el grupo Texto, haz clic en Herramientas Regex.

2. Paso 2: En el panel Herramientas de expresiones regulares, haz lo siguiente:
   1. Selecciona las cadenas de origen.
   2. Ingresa tu patrón.
   3. Elige la opción Coincidir.
   4. Para tener los resultados como fórmulas, no como valores, selecciona la casilla de verificación Insertar como fórmula.
   5. Haz clic en el botón Coincidir

Un momento después, la función AblebitsRegexMatch se inserta en una nueva columna a la derecha de tus datos.

En la captura de pantalla siguiente, la función verifica si las cadenas de la columna A contienen números de 7 dígitos o no.

Consejos:

1. La función se puede insertar directamente en una celda a través del cuadro de diálogo Insertar función estándar, donde se clasifica en AblebitsUDFs.
2. De forma predeterminada, se agrega una expresión regular a la fórmula, pero también puede mantenerla en una celda separada. Para ello, sólo tienes que utilizar una referencia de celda para el 2 dn argumento.
3. De forma predeterminada, la función distingue entre mayúsculas y minúsculas. Para la coincidencia que no distingue entre mayúsculas y minúsculas, utilice el patrón.

Así es como se hacen coincidencias de expresiones regulares en Excel.

RegEx (expresiones regulares) en Excel

RegEx significa expresión regular y es una secuencia de caracteres que define un patrón que se puede usar para hacer coincidir cadenas. O, en un lenguaje más simple, puedo crear un patrón de expresiones regulares y usarlo para buscar una cadena para ese patrón. También Puedes usar el patrón de expresiones regulares para validar la entrada.

Puedes usar funciones de cadena de VBA y el operador LIKE, y puedes usar funciones como MID, LEFT, RIGHT, en la hoja de trabajo. Pero ninguno de estos coincide con RegEx por la complejidad de la coincidencia de patrones y la validación de datos que proporciona.

Una vez que haya aprendido RegEx, puedes usarlo con una amplia gama de lenguajes de programación, no solo en Excel.

Patrones de expresiones regulares

Los patrones de expresiones regulares pueden ser simples o muy complicados, pero todos se crean con la siguiente sintaxis.

Personajes a juego

Patrón	Descripción	Ejemplo	Partidos
.	Cualquier carácter	Connecticut	gato, cuna, corte, c9t, c @ t
[ personajes ]	Coincide con cualquier carácter individual entre corchetes []	[en]	Coincide solo con a o t
[^ caracteres ]	Coincide con cualquier carácter individual que no esté entre corchetes []	[^ en]	Coincide con cualquier carácter, excepto a y t
[ inicio – fin ]	Coincide con cualquier carácter en el rango especificado entre corchetes []	[0-9]	Cualquier dígito entre 0 y 9
		[Arizona]	Cualquier letra minúscula
		[ARIZONA]	Cualquier letra mayúscula
\En	Caracteres alfanuméricos y el subrayado. El carácter de espacio no está incluido en esto.	s0 _d, fk	s 0 _ dfk
\EN	Cualquier carácter no alfanumérico o guión bajo	s0 _d, fk	«»,.
\s	Cualquier carácter de espacio en blanco, espacios, tabulaciones, etc.
\S	Cualquier carácter que no sea un espacio en blanco
\D	Cualquier solo dígito decimal	D	0 y 1 en a0b1
\D	Cualquier dígito no decimal	D	ayb en a0b1
\	Esto escapa a los caracteres especiales para que pueda buscarlos.	\.	. en s0 _d, fk
\ t	Tabulador (vbTab)
\ r	Retorno de carro (vbCr)
\norte	Nueva línea (vbNewLine)

Cuantificadores

Los cuantificadores le permiten modificar el patrón básico para especificar el número de veces que quieres que ese patrón coincida.

Cuantificar	Ejemplo	Descripción	Partidos
*	a B C	Coincide con cero o más ocurrencias del patrón anterior	y
			abc
			abbbbbc
+	ab + c	Coincide con una o más ocurrencias del patrón anterior	abc
			abbbbbc
?	a B C	Coincide con cero o una apariciones del patrón anterior	y
			abc
{norte}	a \ d {3}	Coincide con el patrón anterior n veces	Letra a seguida de 3 dígitos numéricos, por ejemplo, a000, a123, etc.
{norte,}	a \ d {2,}	Coincide con el patrón anterior al menos n veces	Letra a seguida de al menos 2 dígitos numéricos, p. Ej., A00 a123 a00b a1234z
{Nuevo Méjico}	a \ d {1,3}	Coincide con el patrón anterior entre n y m veces	Letra a seguida de entre 1 y 3 dígitos numéricos, p. Ej., A0 a123 a1234z

Capturando

Podemos especificar que queremos capturar un patrón, lo que significa que no solo estamos haciendo coincidir un patrón con una cadena, estamos diciendo que guarde esa parte de la cadena que coincide con nuestro patrón para que podamos hacer algo con ella.

Hasta ahora solo hemos descrito la comparación de un patrón con una cadena, la captura nos permite extraer partes de la cadena. Llegaré a ejemplos de eso más tarde, primero veamos la sintaxis.

Sintaxis	Ejemplo	Descripción	Partidos
(patrón)	[az] (\ d +)	Coincide con una sola letra seguida de uno o más dígitos numéricos. Los números se capturan y se pueden enviar a la hoja de trabajo o usarse de otra manera.	Si nuestra cadena es 12b345j2r, capturamos 345 y 2

Anclas

Ancla	Ejemplo	Descripción	Partidos
^	^ Una vez	Coincide con cualquier cadena que comience con «Once»	Érase una vez
$	tiempo $	Coincide con cualquier cadena que termine con «tiempo»	Érase una vez

Propiedades y métodos de VBA RegEx

Para usar RegEx en VBA, usará el objeto RegExp. Para usar esto, puede establecer una referencia en el editor de VBA o usar la función CreateObject.

Preferimos establecer una referencia, ya que significa que IntelliSense funciona para tu nuevo objeto.

Propiedades

El objeto RegExp tiene estas propiedades:

1. Patrón: el patrón utilizado para hacer coincidir con una cuerda
2. IgnoreCase: si es verdadero, la coincidencia no distingue entre mayúsculas y minúsculas
3. Global: si es verdadero, busca todas las coincidencias del patrón en la cadena. De lo contrario, solo se encuentra la primera coincidencia
4. MultiLine: si es True, los patrones se hacen coincidir a través de los saltos de línea en cadenas de varias líneas

Métodos

Los métodos que podemos utilizar son:

1. Prueba: comprueba si el patrón se puede encontrar en la cadena. Devuelve True si se encuentra, False si no lo es.
2. Ejecutar: devuelve apariciones del patrón que coincide con la cadena.
3. Reemplazar: reemplaza las apariciones del patrón en la cadena

Funciones RegEx

Debemos escribir nuestras propias funciones para usar los métodos RegEx disponibles. Estas funciones y muchos patrones de muestra están disponibles para descargar.

Coincidir

La función RegExMatch es la más simple de las tres que se han escrito.

Se pasa la cadena a buscar y el patrón. Después de establecer la propiedad Pattern, simplemente llame al método Test y devuelva el resultado, ya sea Verdadero o Falso.

Extraer

La función RegExExtract toma 5 argumentos:

1. Str – la cadena que se buscará
2. Patrón: el patrón de expresiones regulares
3. MatchIndex: indica qué ocurrencia coincidente queremos que devuelva la función
4. SubMatchIndex: esto es opcional. Indica qué SubMatch queremos que devuelva la función, más sobre esto a continuación
5. IgnoreCase: esto es opcional. Si es Verdadero, indica que queremos que la coincidencia no distinga entre mayúsculas y minúsculas. El valor predeterminado es verdadero

Partidos y sub-partidos

Cuando se ejecuta el método Execute, puede devolver varios grupos de captura, según el patrón que se utilice. Veamos un ejemplo.

Si nuestra cadena es Ella vende conchas marinas en la orilla del mar y nuestro patrón es s (…) (…) (…) Entonces estamos tratando de hacer coincidir la letra s seguida de 2 caracteres cualesquiera, otros 2 caracteres y otro 2. Debes tener en cuenta que esto incluye espacios.

El paréntesis, (…), indica que queremos capturar estos personajes en grupos separados.

Cuando ejecuto el patrón s (…) (…) (…) Contra Ella vende conchas marinas en la orilla del mar, estamos almacenando el resultado en un objeto que hemos llamado Coincidencias.

Este objeto tiene una propiedad SubMatches donde se almacenan todos los demás grupos capturados.

Para acceder a las coincidencias y subpartidas usamos números que llamamos MatchIndex y SubmatchIndex.

Algo un poco peculiar aquí es que las coincidencias se indexan desde 0, lo que no es inusual en programación. Pero las subpartidas están indexadas desde 1.

Entonces, si miramos lo que coincide con nuestro patrón

y queremos acceder a la segunda subpartida de la tercera partida, usaríamos Matches (2) .SubMatches (2) y esto me daría th .

Si solo queremos que el primer partido sea él, usaría Coincidencias (0).

• Coincidencias (1) .SubMatches (0)me da s – esto incluye el espacio antes de la s.
• Coincidencias (3) .SubMatches (2)me da ho.

Recuerda que el patrón especifica que buscamos hacer coincidir la letra s seguida de 3 conjuntos de 2 caracteres, pero solo capturamos los 3 conjuntos de 2 caracteres. No estamos capturando la s

Consumiendo la cuerda

A medida que se realiza cada coincidencia, la expresión regular se abre camino a lo largo de la cadena en busca de la siguiente coincidencia. Lo que es importante entender es que cada vez que se hace una coincidencia, la parte de la cadena que coincide se ‘consume’ y luego no se usa para buscar la siguiente coincidencia.

Si miramos nuestra cadena Ella vende conchas marinas en la orilla del mar, la primera combinación es Ella sel, por lo que podría pensar que la siguiente combinación es vende s pero no lo es.

En la coincidencia de She sel, esta parte de la cadena no se utiliza cuando se busca la siguiente coincidencia. Efectivamente, la siguiente coincidencia se busca en ls sea shells en la orilla del mar, lo que significa que la siguiente coincidencia es en realidad s sea s.

Reemplazar

La función RegExReplace toma cuatro argumentos:

1. Str – la cadena que se buscará
2. Patrón: el patrón de expresiones regulares
3. ReplaceWith: la cadena para reemplazar cualquier coincidencia
4. Reemplazar todo: esto es opcional. Si es Verdadero, indica que desea reemplazar todas las coincidencias. El valor predeterminado es falso.

Usos prácticos de la expresión regular

Ahora que sabe cómo usar Regex, démosle un uso.

Tal vez te puede interesar leer sobre: Cómo Usar La Función Contar En Excel

Validación de datos

Un uso muy común es validar la entrada / formato de datos. Puede usar la función RegExMatch para esto, aquí hay algunos ejemplos.

Patrón	Descripción	Partidos
^[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}$	Una dirección IP V4	1.2.3.4 10.55.55.90 192.168.0.254
^(\+61) ([1-9]) ([0-9]{4}) ([0-9]{4})$	Línea fija australiana que incluye código de marcación internacional	+61 7 1111 2222
^3[47][0-9]{13}$	Tarjeta de crédito American Express. Las tarjetas Amex comienzan con 34 o 37 y tienen 15 dígitos	341111111111111 371111111111111

Reemplazo de partes de cuerdas

Si la cadena en la celda A1 es ABC-123-XYZ-45-A-6789, puedes usar RegExReplace para:

Descripción	Llamada de función	Resultado
Reemplazar una secuencia de números con 000	RegExReplace (A1, «\ d +», «000», TRUE)	ABC-000-XYZ-000-A-000
Eliminar caracteres no alfabéticos	RegExReplace (A1, «- \ d + – *», «», TRUE)	ABCXYZA
Quitar no numérico	RegExReplace (A1, «- * [a-zA-Z] * – +», «», TRUE)	123456789

Extrayendo cadenas

Si la cadena en la celda A1 es IV: INV-32278ABC Widgets PTY LTD Puedes usar RegExExtract para encontrar coincidencias y sub-coincidencias que me permiten:

Descripción	Llamada de función	Resultado
Extrae el número de factura	Extractoreg (A1, «(. * [0-9] +) (. *)», 0)	IV: INV-32278
Extrae el nombre de la empresa	RegExExtract (A1, «(. * [0-9] +) (. *)», 0,1)	ABC Widgets PTY LTD

Conclusión

Como puedes ver, esta es la forma correcta de usar las expresiones regulares en excel. Las expresiones y sintaxis expuestas aquí te pueden ayudar en tus futuros proyectos. Te aconsejamos que las practiques hasta que salga de manera natural. Esperamos haberte ayudado con esta información.