regular - regex replace online

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

Question

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

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

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

Answer 1

Ampliando la answer patszim para aquellos que tienen prisa.

1. Abra el libro de Excel.
2. Alt + F11 para abrir la ventana VBA / Macros.
3. Agregar referencia a expresiones regulares en Herramientas luego Referencias
   
4. y seleccionando Microsoft VBScript Regular Expression 5.5
   
5. Inserte un nuevo módulo (el código debe residir en el módulo, de lo contrario no funcionará).
   
6. En el módulo recién insertado,
   

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 expresiones regulares se coloca en una de las celdas y se utiliza la referencia absoluta . La función estará vinculada al libro de trabajo que se creó en.
   Si es necesario que se use en diferentes libros, almacene la función en Personal.XLSB

Answer 2

Aquí está mi intento:

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

Answer 3

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 averiguar cómo construir un patrón de salida adecuado usando lo anterior para funcionar 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

Answer 4

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

1. Asegúrese de estar en un archivo de Excel habilitado para macros (guardar como xlsm).
2. Abrir herramientas de desarrollo 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") (por ejemplo: "A 243" a "A243")

Answer 5

Para utilizar 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 expresión regular como una función de excel.

Cómo funciona

Toma 2-3 parámetros.

1. Un texto para usar la expresión regular.
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

Extraer una dirección de correo electrónico:

=regex("Peter Gordon: [email protected], 47", "/w+@/w+/./w+")
=regex("Peter Gordon: [email protected], 47", "/w+@/w+/./w+", "$0")

Resultados en: [email protected]

Extracción de varias subcadenas:

=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (/d+)$", "E-Mail: $2, Name: $1")

Resultados en: E-Mail: [email protected], Name: Peter Gordon

Para separar una cadena combinada en una sola celda en sus componentes en múltiples celdas:

=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (/d+)$", "$" & 1)
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (/d+)$", "$" & 2)

Resultados en: Peter Gordon [email protected] ...

Cómo utilizar

Para usar este UDF, haga lo siguiente (basado aproximadamente en esta página de Microsoft . ¡Tienen una buena información adicional allí!):

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 la referencia de VBA a la biblioteca de Expresiones regulares (copiada de la respuesta de Portland Runners ++ ):
   1. Haga clic en Herramientas -> Referencias (disculpe la captura de pantalla alemana)
   2. Busque Microsoft VBScript Regular Expressions 5.5 en la lista y marque la casilla de verificación junto a él.
   3. Haga clic en Aceptar .

3. Haga clic en Insertar módulo . Si le da a su módulo un nombre diferente, asegúrese de que el Módulo no tenga el mismo nombre que el UDF a continuación (por ejemplo, al nombrar el Módulo Regex y la función regex causa errores #NAME! ).

4. En la ventana de texto grande en el medio inserte lo siguiente:

   Function regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") As Variant
   Dim inputRegexObj As New VBScript_RegExp_55.RegExp, outputRegexObj As New VBScript_RegExp_55.RegExp, outReplaceRegexObj As New VBScript_RegExp_55.RegExp
   Dim inputMatches As Object, replaceMatches As Object, replaceMatch As Object
   Dim replaceNumber As Integer
   With inputRegexObj
   .Global = True
   .MultiLine = True
   .IgnoreCase = False
   .Pattern = matchPattern
   End With
   With outputRegexObj
   .Global = True
   .MultiLine = True
   .IgnoreCase = False
   .Pattern = "/$(/d+)"
   End With
   With outReplaceRegexObj
   .Global = True
   .MultiLine = True
   .IgnoreCase = False
   End With
   Set inputMatches = inputRegexObj.Execute(strInput)
   If inputMatches.Count = 0 Then
   regex = False
   Else
   Set replaceMatches = outputRegexObj.Execute(outputPattern)
   For Each replaceMatch In replaceMatches
   replaceNumber = replaceMatch.SubMatches(0)
   outReplaceRegexObj.Pattern = "/$" & replaceNumber
   If replaceNumber = 0 Then
   outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)
   Else
   If replaceNumber > 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

5. Guarde y cierre la ventana del Editor de Microsoft Visual Basic para Aplicaciones .

Answer 6

Las expresiones regulares se utilizan para la coincidencia de patrones.

Para usar en Excel sigue estos pasos:

Paso 1 : Agregue la referencia de VBA a "Microsoft VBScript Regular Expressions 5.5"

• Seleccione la pestaña "Desarrollador" ( no tengo esta pestaña, ¿qué hago? )
• 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 A a la Z
• Por ejemplo, 0-5 coincide con cualquier número del 0 al 5

[] Coincide exactamente con 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, b o c
• Por ejemplo, [az] coincide con cualquier letra minúscula del alfabeto.

() Agrupa diferentes partidos para devoluciones. Vea los 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

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

• Por ejemplo, a+ coincidirá con las a consecutivas a , aa , aaa , etc.

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

• Por ejemplo, el patrón puede o no estar presente, pero solo se puede hacer coincidir una vez.
• Por ejemplo, [az]? coincide con una cadena vacía o cualquier letra minúscula.

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

. Coincide con cualquier carácter, excepto nueva línea /n

• Ej 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 puede 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 ni mayúscula A

/ Escapa del carácter especial que sigue (anula el comportamiento anterior)

• Ej /. , // , /( , /? /$ , /^

Patrones de anclaje:

^ coincidencia debe ocurrir al comienzo de la cadena

• Ej. ^a primer carácter debe ser 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$ Último carácter debe ser minúscula a

Tabla de precedencia:

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

Abreviaturas de caracteres predefinidas:

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 mira 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 resto de la cadena. Si no es así, aparece un cuadro que le indica que no se ha encontrado ninguna coincidencia. Los valores de la celda A1 de 12abc devolverán abc , el valor de 1abc devolverá abc , el valor de abc123 devolverá "No coincidente" 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 en la celda

Este ejemplo es el mismo que el ejemplo 1, pero está configurado para ejecutarse como una función dentro de la celda. Para usar, cambia 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 cuerdas ("12abc") en la celda A1 . Ingrese esta fórmula =simpleCellRegex(A1) en la celda B1 y el resultado será "abc".

Ejemplo 3 : rango de bucle

Este ejemplo es el mismo que el ejemplo 1, pero pasa por 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 : Separando diferentes patrones

Este ejemplo recorre un rango ( A1 , A2 y A3 ) y busca una cadena que comienza con tres dígitos seguidos de un solo carácter alfa y luego 4 dígitos numéricos. La salida divide las coincidencias de patrones en celdas adyacentes mediante el uso de () . $1 representa el primer patrón que coincide con el 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:

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
</i8> /<//[a-zA-Z][0-9]/> Exact non-word character except any single alpha followed by any single digit