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

regex excel vba


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

  • Función dentro de la célula para devolver un patrón coincidente o un valor sustituido en una cadena.
  • Sub para hacer un bucle a través de una columna de datos y extraer coincidencias con las células 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 , Instr para manipulaciones similares.




Answer 1 Portland Runner


Las expresiones regulares se utilizan para la coincidencia de patrones.

Para usar en Excel siga 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é debo hacer? )
  • Selecciona el icono de "Visual Basic" en la sección de la cinta "Código".
  • En la ventana de "Microsoft Visual Basic for Applications" selecciona "Tools" en el menú superior.
  • Seleccione "Referencias"
  • Marque la casilla junto a "Microsoft VBScript Regular Expressions 5.5" para incluirla en su libro de trabajo.
  • Haga clic en "OK"

Paso 2 : define tu patrón

Definiciones basicas:

- Alcance.

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

[] Haga coincidir 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, b o c
  • Por ejemplo, [a-z] 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

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

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

? Haga coincidir cero o uno de los patrones definidos antes.

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

* Haga coincidir cero o más del patrón definido antes. - Por ejemplo, comodín para un patrón que puede o no estar presente. - Por ejemplo, [a-z]* 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. A. Coincide con una cadena de dos caracteres que comienza con ay termina con cualquier cosa excepto \n

| Operador OR

  • 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, [^aA] carácter no puede ser minúscula a o mayúsculas A

\ Se escapa el carácter especial que sigue (anula el comportamiento anterior)

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

Patrones de anclaje:

^ La coincidencia debe ocurrir al comienzo de la cadena

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

$ La coincidencia debe ocurrir al final de la cadena

  • Por ejemplo, a$ último carácter 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         |

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 examina 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. De lo contrario, aparece un cuadro que le indica que no se encontró 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 comienzo 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 funcionar 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".

results image


Ejemplo 3 : Bucle a través del rango

Este ejemplo es el mismo que el ejemplo 1 pero en bucle a través de un rango de células.

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 : dividir diferentes patrones

Este ejemplo recorre un rango ( A1 , A2 y A3 ) y busca una cadena que comience 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 usando () . $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

Results:

results image


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 preceding 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



Answer 2 Patrick Böker


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

Cómo funciona

Se necesitan 2 ó 3 parámetros.

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

Algunos ejemplos

Extrayendo 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 cadenas:

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

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

Desmontar una cadena combinada en una sola célula en sus componentes en múltiples células:

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

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

Cómo se usa

Para usar este UDF, haga lo siguiente (aproximadamente basado en esta página de Microsoft . ¡Tienen 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 descaradamente de la respuesta de Portland Runners ++ ):
    1. Haga clic en Herramientas -> Referencias (disculpe la captura de pantalla alemana)Tools -> References
    2. Busque Microsoft VBScript Regular Expressions 5.5 en la lista y marque la casilla de verificación al lado.
    3. Haz 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, nombrar el Módulo Regex y la función regex causa errores # ¡NOMBRE! ).

    Second icon in the icon row -> Module

  4. En la gran ventana de texto del 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 3 SAm


Ampliando la respuesta de patszim para aquellos apurados .

  1. Abre el libro de trabajo de Excel.
  2. Alt+F11para abrir la ventana de VBAMacros.
  3. Agregar referencia a expresiones regulares en Herramientas y luego en Referencias
    ![Excel VBA Form add references
  4. y seleccionando Microsoft VBScript Regular Expression 5.5
    ![Excel VBA add regex reference
  5. Inserte un nuevo módulo (el código debe residir en el módulo,de lo contrario no funciona).
    ![Excel VBA insert code module
  6. En el módulo recién insertado,
    ![Excel VBA insert code into module
  7. añade 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. ![Excel regex function in-cell usageEl patrón regex se coloca en una de las celdas y se usa referencia absoluta en él. La función estará vinculada al libro de trabajo en el que se creó.
    Si es necesario que se use en diferentes libros, almacene la función en Personal.XLSB




Answer 4 Vikas Gautam


Este es 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 5 DeezCashews


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

  1. Asegúrate de que estás en un archivo de Excel habilitado para macros (guárdalo como xlsm).
  2. Herramientas de desarrollo abiertasAlt+F11
  3. Agregue Microsoft VBScript Regular Expressions 5.5 como en otras respuestas
  4. Crear 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")