Cómo usar expresiones regulares (Regex) en Microsoft Excel tanto en la celda como en los bucles

¿Cómo puedo usar expresiones regulares en Excel y aprovechar la potente configuración de cuadrícula de Excel para la manipulación de datos?

  • Función dentro de la celda para devolver el patrón coincidente o el valor reemplazado en la cadena.
  • Sub para recorrer una columna de datos y extraer coincidencias con celdas adyacentes.
  • ¿Qué configuración es necesaria?
  • ¿Cuáles son los caracteres especiales de Excel para expresiones regulares?

Entiendo que Regex no es ideal para muchas situaciones (¿ Para usar o no usar expresiones regulares? ) Ya que Excel puede usar comandos de tipo Left , Mid , Right , Instr para manipulaciones similares.

Las expresiones regulares se usan para la coincidencia de patrones.

Para usar en Excel, siga estos pasos:

Paso 1 : Agregue referencia de VBA a “Microsoft VBScript Regular Expressions 5.5”

  • Seleccione la pestaña “Desarrollador” ( no tengo esta pestaña, ¿qué debo hacer? )
  • Seleccione el icono “Visual Basic” de la sección de cinta ‘Código’
  • En la ventana “Microsoft Visual Basic para Aplicaciones”, seleccione “Herramientas” en el menú superior.
  • Seleccione “Referencias”
  • Marque la casilla junto a “Microsoft VBScript Regular Expressions 5.5” para incluir en su libro de trabajo.
  • Haga clic en Aceptar”

Paso 2 : define tu patrón

Definiciones basicas:

- Rango.

  • Por ejemplo, az coincide con letras minúsculas de la aa la z
  • Por ejemplo, 0-5 coincide con cualquier número del 0 al 5

[] Empareja exactamente uno de los objetos dentro de estos corchetes.

  • Por ejemplo, [a] coincide con la letra a
  • Por ejemplo, [abc] coincide con una sola letra que puede ser a, boc
  • Por ejemplo, [az] coincide con cualquier letra minúscula del alfabeto.

() Agrupa diferentes coincidencias para propósitos de devolución. Ver ejemplos a continuación.

{} Multiplicador para copias repetidas del patrón definido antes.

  • Por ejemplo, [a]{2} coincide con dos letras minúsculas consecutivas a: aa
  • Por ejemplo, [a]{1,3} coincide con al menos una y hasta tres letras minúsculas a , aa , aaa

+ Combina al menos uno, o más, del patrón definido antes.

  • Por ejemplo, a+ coincidirá con a’s a , aa , aaa consecutivos, y así sucesivamente

? Coincide con cero o uno de los patrones definidos antes.

  • Ej. El patrón puede o no estar presente, pero solo puede coincidir una vez.
  • Ej. [az]? coincide con una cadena vacía o una sola letra minúscula.

* Coincide con cero o más del patrón definido antes. – Por ejemplo, comodín para el patrón que puede estar presente o no. – Por ejemplo, [az]* coincide con una cadena vacía o una cadena de letras minúsculas.

. Coincide con cualquier carácter excepto newline \n

  • Ej a. A. Coincide con una cadena de dos caracteres que comienza con a y termina con cualquier cosa excepto \n

| O operador

  • Por ejemplo, a|b significa que a o b pueden coincidir.
  • Por ejemplo, red|white|orange coincide exactamente con uno de los colores.

^ NO operador

  • Por ejemplo, el carácter [^0-9] no puede contener un número
  • Por ejemplo, el carácter [^aA] no puede ser minúscula a mayúscula A

\ Escapes del carácter especial que sigue (anula el comportamiento anterior)

  • Eg \. , \\ , \( , \? \$ , \^

Patrones de anclaje:

^ coincidencia debe ocurrir al comienzo de la cadena

  • Por ejemplo ^a primer personaje debe ser a letra minúscula a
  • Por ejemplo, ^[0-9] primer carácter debe ser un número.

$ Match debe ocurrir al final de la cadena

  • Por ejemplo, a$ Last debe ser a letra minúscula a

Tabla de precedencia:

 Order Name Representation 1 Parentheses ( ) 2 Multipliers ? + * {m,n} {m, n}? 3 Sequence & Anchors abc ^ $ 4 Alternation | 

Abreviaciones predefinidas de caracteres:

 abr same as meaning \d [0-9] Any single digit \D [^0-9] Any single character that's not a digit \w [a-zA-Z0-9_] Any word character \W [^a-zA-Z0-9_] Any non-word character \s [ \r\t\n\f] Any space character \S [^ \r\t\n\f] Any non-space character \n [\n] New line 

Ejemplo 1 : ejecutar como macro

La siguiente macro de ejemplo observa el valor en la celda A1 para ver si los primeros 1 o 2 caracteres son dígitos. Si es así, se eliminan y se muestra el rest de la cadena. Si no, aparece un cuadro que le dice que no se encuentra ninguna coincidencia. Los valores de Cell A1 de 12abc devolverán abc , el valor de 1abc devolverá abc , el valor de abc123 devolverá “No coincide” porque los dígitos no estaban al principio de la cadena.

 Private Sub simpleRegex() Dim strPattern As String: strPattern = "^[0-9]{1,2}" Dim strReplace As String: strReplace = "" Dim regEx As New RegExp Dim strInput As String Dim Myrange As Range Set Myrange = ActiveSheet.Range("A1") If strPattern <> "" Then strInput = Myrange.Value With regEx .Global = True .MultiLine = True .IgnoreCase = False .Pattern = strPattern End With If regEx.Test(strInput) Then MsgBox (regEx.Replace(strInput, strReplace)) Else MsgBox ("Not matched") End If End If End Sub 

Ejemplo 2 : ejecutar como una función dentro de la célula

Este ejemplo es igual que el ejemplo 1, pero está configurado para ejecutarse como una función dentro de la célula. Para usar, cambie el código a esto:

 Function simpleCellRegex(Myrange As Range) As String Dim regEx As New RegExp Dim strPattern As String Dim strInput As String Dim strReplace As String Dim strOutput As String strPattern = "^[0-9]{1,3}" If strPattern <> "" Then strInput = Myrange.Value strReplace = "" With regEx .Global = True .MultiLine = True .IgnoreCase = False .Pattern = strPattern End With If regEx.test(strInput) Then simpleCellRegex = regEx.Replace(strInput, strReplace) Else simpleCellRegex = "Not matched" End If End If End Function 

Coloque sus cadenas (“12abc”) en la celda A1 . Ingrese esta fórmula =simpleCellRegex(A1) en la celda B1 y el resultado será “abc”.

enter image description here


Ejemplo 3 : rango de bucle

Este ejemplo es el mismo que en el ejemplo 1, pero recorre un rango de celdas.

 Private Sub simpleRegex() Dim strPattern As String: strPattern = "^[0-9]{1,2}" Dim strReplace As String: strReplace = "" Dim regEx As New RegExp Dim strInput As String Dim Myrange As Range Set Myrange = ActiveSheet.Range("A1:A5") For Each cell In Myrange If strPattern <> "" Then strInput = cell.Value With regEx .Global = True .MultiLine = True .IgnoreCase = False .Pattern = strPattern End With If regEx.Test(strInput) Then MsgBox (regEx.Replace(strInput, strReplace)) Else MsgBox ("Not matched") End If End If Next End Sub 

Ejemplo 4 : separar diferentes patrones

Este ejemplo recorre un rango ( A1 , A2 y A3 ) y busca una cadena que comienza con tres dígitos seguidos por un solo carácter alfabético y luego 4 dígitos numéricos. La salida divide las coincidencias del patrón en celdas adyacentes usando el () . $1 representa el primer patrón coincidente dentro del primer conjunto de () .

 Private Sub splitUpRegexPattern() Dim regEx As New RegExp Dim strPattern As String Dim strInput As String Dim Myrange As Range Set Myrange = ActiveSheet.Range("A1:A3") For Each C In Myrange strPattern = "(^[0-9]{3})([a-zA-Z])([0-9]{4})" If strPattern <> "" Then strInput = C.Value With regEx .Global = True .MultiLine = True .IgnoreCase = False .Pattern = strPattern End With If regEx.test(strInput) Then C.Offset(0, 1) = regEx.Replace(strInput, "$1") C.Offset(0, 2) = regEx.Replace(strInput, "$2") C.Offset(0, 3) = regEx.Replace(strInput, "$3") Else C.Offset(0, 1) = "(Not matched)" End If End If Next End Sub 

Resultados:

enter image description here


Ejemplos de patrones adicionales

 String Regex Pattern Explanation a1aaa [a-zA-Z][0-9][a-zA-Z]{3} Single alpha, single digit, three alpha characters a1aaa [a-zA-Z]?[0-9][a-zA-Z]{3} May or may not have preceeding alpha character a1aaa [a-zA-Z][0-9][a-zA-Z]{0,3} Single alpha, single digit, 0 to 3 alpha characters a1aaa [a-zA-Z][0-9][a-zA-Z]* Single alpha, single digit, followed by any number of alpha characters  \<\/[a-zA-Z][0-9]\> Exact non-word character except any single alpha followed by any single digit 

Para hacer uso de expresiones regulares directamente en fórmulas de Excel, la siguiente UDF (función definida por el usuario) puede ser de ayuda. Expone más o menos directamente la funcionalidad de expresiones regulares como una función de Excel.

Cómo funciona

Toma 2-3 parámetros.

  1. Un texto para usar la expresión regular en.
  2. Una expresión regular.
  3. Una cadena de formato que especifica cómo debe verse el resultado. Puede contener $0 , $1 , $2 , etc. $0 es la coincidencia completa, $1 y más corresponden a los grupos de coincidencia respectivos en la expresión regular. El valor predeterminado es $0 .

Algunos ejemplos

Extrayendo una dirección de correo electrónico:

 =regex("Peter Gordon: some@email.com, 47", "\w+@\w+\.\w+") =regex("Peter Gordon: some@email.com, 47", "\w+@\w+\.\w+", "$0") 

Resultados en: some@email.com

Extrayendo varias subcadenas:

 =regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1") 

Resultados en: E-Mail: some@email.com, Name: Peter Gordon

Descomponer una cadena combinada en una sola celda en sus componentes en varias celdas:

 =regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "$" & 1) =regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "$" & 2) 

Resultados en: Peter Gordon some@email.com

Cómo utilizar

Para usar este UDF haga lo siguiente (más o menos basado en esta página de Microsoft . ¡Aquí tienen alguna buena información adicional!):

  1. En Excel en un archivo habilitado para macros (‘.xlsm’) presione ALT+F11 para abrir el Editor de Microsoft Visual Basic para Aplicaciones .
  2. Agregue referencia de VBA a la biblioteca de Expresiones regulares (descaradamente copiada de la respuesta de Portland Runners ++ ):
    1. Haga clic en Herramientas -> Referencias (disculpe la captura de pantalla alemana) Herramientas -/> Referencias”></li>
<li>  Busque <em>Microsoft VBScript Regular Expressions 5.5</em> en la lista y marque la checkbox junto a él. </li>
<li>  Haga clic en <em>Aceptar</em> . </li>
</ol>
</li>
<li>
<p>  Haga clic en <em>Insertar módulo</em> .  Si le da a su módulo un nombre diferente, asegúrese de que el Módulo <em>no</em> tenga el mismo nombre que el UDF a continuación (por ejemplo, nombrar el Módulo <code>Regex</code> y la función <code>regex</code> causa errores <em># ¡NOMBRE!</em> ). </p>
<p><img src= inputMatches(0).SubMatches.Count Then 'regex = "A to high $ tag found. Largest allowed is $" & inputMatches(0).SubMatches.Count & "." regex = CVErr(xlErrValue) Exit Function Else outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1)) End If End If Next regex = outputPattern End If End Function
    2. Guarde y cierre la ventana del Editor de Microsoft Visual Basic para Aplicaciones .

Ampliando la respuesta de patszim para los que están apurados .

  1. Abre el libro de Excel.
  2. Alt + F11 para abrir la ventana de VBA / Macros.
  3. Agregar referencia a regex en Herramientas y luego Referencias
    ! [Excel VBA Form add references
  4. y seleccionando Microsoft VBScript Regular Expression 5.5
    ! [Excel VBA agregar referencia regex
  5. Inserte un nuevo módulo (el código debe residir en el módulo, de lo contrario, no funciona).
    ! [Módulo de código de inserción de Excel VBA
  6. En el módulo recién insertado,
    ! [Excel VBA inserte el código en el módulo
  7. agregue el siguiente código:

     Function RegxFunc(strInput As String, regexPattern As String) As String Dim regEx As New RegExp With regEx .Global = True .MultiLine = True .IgnoreCase = False .pattern = regexPattern End With If regEx.Test(strInput) Then Set matches = regEx.Execute(strInput) RegxFunc = matches(0).Value Else RegxFunc = "not matched" End If End Function 
  8. El patrón de expresión regular se coloca en una de las celdas y se utiliza una referencia absoluta en él. ! [Función regex de Excel en el uso de la célula La función estará vinculada al libro de trabajo en el que se creó.
    Si es necesario utilizarlo en diferentes libros, almacene la función en Personal.XLSB.

Aquí está mi bash:

 Function RegParse(ByVal pattern As String, ByVal html As String) Dim regex As RegExp Set regex = New RegExp With regex .IgnoreCase = True 'ignoring cases while regex engine performs the search. .pattern = pattern 'declaring regex pattern. .Global = False 'restricting regex to find only first match. If .Test(html) Then 'Testing if the pattern matches or not mStr = .Execute(html)(0) '.Execute(html)(0) will provide the String which matches with Regex RegParse = .Replace(mStr, "$1") '.Replace function will replace the String with whatever is in the first set of braces - $1. Else RegParse = "#N/A" End If End With End Function 

Aquí hay una función regex_subst() . Ejemplos:

 =regex_subst("watermellon", "[aeiou]", "") ---> wtrmlln =regex_subst("watermellon", "[^aeiou]", "") ---> aeeo 

Aquí está el código simplificado (más simple para mí, de todos modos). No pude encontrar la forma de construir un patrón de salida adecuado usando lo anterior para que funcione como mis ejemplos:

 Function regex_subst( _ strInput As String _ , matchPattern As String _ , Optional ByVal replacePattern As String = "" _ ) As Variant Dim inputRegexObj As New VBScript_RegExp_55.RegExp With inputRegexObj .Global = True .MultiLine = True .IgnoreCase = False .Pattern = matchPattern End With regex_subst = inputRegexObj.Replace(strInput, replacePattern) End Function 

Necesitaba usar esto como una función de celda (como SUM o VLOOKUP ) y descubrí que era fácil:

  1. Asegúrese de estar en un archivo de Excel habilitado para macros (guardar como xlsm).
  2. Abra las herramientas de desarrollador Alt + F11
  3. Agregue Microsoft VBScript Regular Expressions 5.5 como en otras respuestas
  4. Cree la siguiente función en el libro de trabajo o en su propio módulo:

     Function REGPLACE(myRange As Range, matchPattern As String, outputPattern As String) As Variant Dim regex As New VBScript_RegExp_55.RegExp Dim strInput As String strInput = myRange.Value With regex .Global = True .MultiLine = True .IgnoreCase = False .Pattern = matchPattern End With REGPLACE = regex.Replace(strInput, outputPattern) End Function 
  5. Luego puede usar en la celda con =REGPLACE(B1, "(\w) (\d+)", "$1$2") (ej: “A 243” a “A243”)