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