tutorial - Come utilizzare le espressioni regolari(Regex) in Microsoft Excel sia in-cell che loop




rex101 (4)

Come posso utilizzare le espressioni regolari in Excel e sfruttare la potente griglia di Excel come la configurazione per la manipolazione dei dati?

  • Funzione in-cell per restituire pattern abbinato o valore sostituito in stringa.
  • Sub per eseguire il ciclo attraverso una colonna di dati ed estrarre le corrispondenze alle celle adiacenti.
  • Quale configurazione è necessaria?
  • Quali sono i caratteri speciali di Excel per le espressioni regolari?

Capisco che Regex non è l'ideale per molte situazioni ( usare o non usare espressioni regolari? ) Poiché excel può usare i comandi di tipo Left , Mid , Right , Instr per manipolazioni simili.


Avevo bisogno di usarlo come una funzione di cella (come SUM o VLOOKUP ) e ho scoperto che era facile:

  1. Assicurati di essere in un file Excel abilitato per la macro (salva come xlsm).
  2. Apri gli strumenti per sviluppatori Alt + F11
  3. Aggiungi Microsoft VBScript Regular Expressions 5.5 come in altre risposte
  4. Creare la seguente funzione nella cartella di lavoro o nel proprio modulo:

    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. Quindi puoi usare nella cella con =REGPLACE(B1, "(\w) (\d+)", "$1$2") (es: "A 243" a "A243")


Ecco il mio tentativo:

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

Espansione della answer patszim per quelli di fretta.

  1. Aprire la cartella di lavoro di Excel.
  2. Alt + F11 per aprire la finestra VBA / Macro.
  3. Aggiungi riferimento alla regex in Strumenti, quindi Riferimenti
  4. e selezionando Microsoft VBScript Regular Expression 5.5
  5. Inserisci un nuovo modulo (il codice deve risiedere nel modulo altrimenti non funziona).
  6. Nel modulo appena inserito,
  7. aggiungi il seguente codice:

    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. Il modello regex è posto in una delle celle e su di esso viene utilizzato il riferimento assoluto . La funzione sarà legata alla cartella di lavoro in cui è stata creata.
    Se è necessario utilizzarlo in diverse cartelle di lavoro, memorizzare la funzione in Personal.XLSB


Per utilizzare le espressioni regolari direttamente nelle formule di Excel, può essere utile la seguente UDF (funzione definita dall'utente). Espone più o meno direttamente la funzionalità delle espressioni regolari come una funzione di Excel.

Come funziona

Ci vogliono 2-3 parametri.

  1. Un testo per utilizzare l'espressione regolare su.
  2. Un'espressione regolare.
  3. Una stringa di formato che specifica come dovrebbe apparire il risultato. Può contenere $0 , $1 , $2 e così via. $0 è l'intera corrispondenza, $1 e superiore corrispondono ai rispettivi gruppi di corrispondenza nell'espressione regolare. Il valore predefinito è $0 .

Qualche esempio

Estrazione di un indirizzo email:

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

Risultati in: [email protected]

Estrazione di diverse sottostringhe:

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

Risultati in: E-Mail: [email protected], Name: Peter Gordon

Per separare una stringa combinata in una singola cella nei suoi componenti in più celle:

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

Risultati in: Peter Gordon [email protected] ...

Come usare

Per utilizzare questa UDF, procedi nel seguente modo (basandoti approssimativamente su questa pagina di Microsoft . Qui ci sono alcune buone informazioni aggiuntive!):

  1. In Excel in un file abilitato alla macro ('.xlsm'), premere ALT+F11 per aprire Microsoft Visual Basic per Editor di applicazioni .
  2. Aggiungi riferimento VBA alla libreria di espressioni regolari (copiata senza vergogna dalla risposta Portland Runners ++ ):
    1. Fare clic su Strumenti -> Riferimenti (si prega di scusare lo screenshot tedesco)
    2. Trova Microsoft VBScript Regular Expressions 5.5 nell'elenco e spunta la casella accanto ad esso.
    3. Clicca OK .
  3. Clicca su Inserisci modulo . Se assegni al tuo modulo un nome diverso, assicurati che il modulo non abbia lo stesso nome della UDF di seguito (ad es. Nominando il modulo Regex e la funzione regex causi errori #NAME! ).

  4. Nella finestra di testo grande al centro inserisci quanto segue:

    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. Salvare e chiudere la finestra Editor di Microsoft Visual Basic .





excel-vba