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




regular expression creator online (4)

¿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.


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


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

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")


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", "\[email protected]\w+\.\w+")
=regex("Peter Gordon: [email protected], 47", "\[email protected]\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 .







excel-vba