Tutorial su Regex in Excel: padroneggia il pattern matching con le espressioni regolari

Usare le RegEx in Excel

Sebbene Excel non supporti nativamente le RegEx, puoi abilitarle scrivendo una tua definizione personalizzata con uno script VBA (per Windows) o attivando componenti aggiuntivi (per Mac). Vediamo come abilitare una funzione regex per estrarre testo da una colonna in Excel.

Per Windows

Per iniziare, premi Alt+F11 per aprire l'Editor di Visual Basic. Puoi anche raggiungerlo tramite Strumenti > Macro > Editor di Visual Basic. Quindi, fai clic su Inserisci > Modulo.

Incolla la seguente funzione nel modulo:

Public Function CustomRegExpExtract(inputText As String, regexPattern As String, Optional instanceNumber As Integer = 0, Optional matchCase As Boolean = True) As Variant
    Dim textMatches() As String
    Dim matchesIndex As Integer
    Dim regex As Object
    Dim matches As Object
    
    On Error GoTo ErrHandler
    
    CustomRegExpExtract = ""
    
    ' Create a regular expression object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = regexPattern
    regex.Global = True
    regex.MultiLine = True
    
    ' Set case sensitivity
    If matchCase Then
        regex.IgnoreCase = False
    Else
        regex.IgnoreCase = True
    End If
    
    ' Execute the regex pattern on the input text
    Set matches = regex.Execute(inputText)
    
    If matches.Count > 0 Then
        If instanceNumber = 0 Then
            ' If instanceNumber is 0, return all matches in an array
            ReDim textMatches(matches.Count - 1, 0)
            For matchesIndex = 0 To matches.Count - 1
                textMatches(matchesIndex, 0) = matches.Item(matchesIndex)
            Next matchesIndex
            CustomRegExpExtract = textMatches
        Else
            ' If instanceNumber is specified, return the match at that instance
            CustomRegExpExtract = matches.Item(instanceNumber - 1)
        End If
    End If
    
    Exit Function

ErrHandler:
    ' Handle errors by returning an error value
    CustomRegExpExtract = CVErr(xlErrValue)
End Function

' Modified from an original script by Patrick Matthews

Salva la funzione e torna al foglio di lavoro. Ora salva la cartella di lavoro come abilitata alle macro, con estensione file .xlsm.

Usare la funzione in un foglio di lavoro Excel

Abilitando le macro nella tua cartella di lavoro Excel, ora puoi usare la funzione CustomRegExpExtract come una normale funzione di Excel per analizzare pattern di stringhe complessi, dove:

• inputText è la stringa originale da analizzare;

• regexPattern è il pattern dell'espressione regolare usato per analizzare inputText;

• instanceNumber per impostazione predefinita restituisce tutte le occorrenze del pattern, ma è possibile inserire un intero per specificare il numero di occorrenze da restituire; e

• matchCase specifica se considerare la distinzione tra maiuscole e minuscole durante il matching (TRUE o omesso) oppure ignorarla (FALSE).

Poniamo di voler estrarre i numeri di telefono da un campo. Possiamo digitare il pattern regex in una qualsiasi cella (nel caso sotto, si trova in A2). In A6 c'è il tuo esempio di stringa libera, "Sophia, 1111111111". In B6, digitiamo =CustomRegExpExtract(A6,$A$2). La funzione estrae il numero di telefono confrontando la stringa con il pattern regex in A2 e restituisce "1111111111", come previsto.

Per Mac OS

Purtroppo, Mac OS non riconosce la libreria MS VBScript Regular Expressions 5.5. Per abilitare il pattern matching complesso in Excel su Mac, ci sono due opzioni: usare i filtri avanzati oppure installare componenti aggiuntivi.

Uso dei filtri avanzati

I filtri avanzati in Excel aiutano a eseguire operazioni di filtraggio dati complesse basate su un insieme di criteri multipli. Questa funzionalità è particolarmente utile quando si lavora con dataset di grandi dimensioni. Ecco come usare i filtri avanzati in Excel:

• Prepara i dati: Assicurati che i dati siano in un formato simile a una tabella con intestazioni chiare.

• Crea un intervallo di criteri: Imposta un'area separata nel foglio per definire i criteri di filtro. Quest'area deve avere le stesse intestazioni delle colonne da filtrare. Sotto queste intestazioni, specifica i criteri di filtro. Puoi inserire più criteri sotto la stessa intestazione per una logica "OR", oppure su righe separate per una logica "AND".

• Seleziona i dati: Fai clic su una cella all'interno del dataset o seleziona l'intero intervallo da filtrare.

• Configura il filtro: Fai clic sulla scheda Dati nella barra multifunzione di Excel e vai all'opzione Filtro avanzato. Clicca su Avanzate nel gruppo Ordina e filtra. Nella finestra Filtro avanzato, scegli se filtrare l'elenco in posizione oppure copiare i risultati in un'altra posizione. Specifica l'Intervallo elenco (i tuoi dati). Specifica l'Intervallo criteri (dove hai impostato le condizioni di filtro). Fai clic su OK per applicare il filtro.

Supponiamo di avere un dataset con le colonne Nome, Età, Città. Vuoi filtrare per mostrare le persone con più di 30 anni a New York o chiunque si trovi a Los Angeles. Il tuo intervallo di criteri potrebbe essere così:

Installazione di componenti aggiuntivi

• Kutools for Excel: Kutools è un componente aggiuntivo completo che include molte funzioni, tra cui la possibilità di trovare e sostituire dati usando le regex. Aggiunge una nuova scheda alla barra multifunzione di Excel con un'ampia gamma di strumenti, incluse funzioni regex.

• RegEx Find/Replace: È un componente aggiuntivo per Excel che permette di trovare e sostituire testo usando le espressioni regolari. Si integra in Excel e offre funzionalità di ricerca/sostituzione più avanzate rispetto all'opzione integrata.

• PowerGREP: Pur non essendo un componente aggiuntivo diretto di Excel, PowerGREP può lavorare con file Excel. È un potente strumento grep per Windows che consente operazioni regex complesse su file di testo e binari. È più un'applicazione autonoma, ma può essere usata insieme a Excel per una gestione avanzata delle regex. Non è economico, però. Una licenza per singolo utente costa 159 $ e il prezzo sale a 5.300 $ per una licenza da 100 utenti.

Alcuni componenti aggiuntivi si trovano direttamente in Excel tramite la scheda Inserisci > Ottieni componenti aggiuntivi.

Altri devono essere scaricati da una fonte esterna e installati seguendo le relative istruzioni.

Alcune considerazioni:

• Compatibilità: Assicurati che il componente aggiuntivo sia compatibile con la tua versione di Excel e il tuo sistema operativo.
• Sicurezza: Scarica e installa componenti aggiuntivi solo da fonti affidabili.
• Esigenze funzionali: Valuta i compiti regex specifici che devi svolgere e scegli un componente aggiuntivo che si adatti al meglio a tali esigenze.

Conclusione

Nella maggior parte dei casi, funzioni native come TROVA, CERCA e SOSTITUISCI copriranno il 90% delle tue esigenze di manipolazione del testo in Excel. Tuttavia, per il restante 10%, nei casi che coinvolgono pattern di stringhe più complessi, le regex possono aiutare moltissimo. Per saperne di più sull'applicazione delle espressioni regolari con i linguaggi di programmazione più diffusi, dai un'occhiata ai nostri corsi su Regular Expressions in Python e Intermediate Regular Expressions in R.

Per rimetterti in pari con tutto ciò che riguarda Excel, dai un'occhiata al nostro percorso di competenze Excel Fundamentals, che ti guida attraverso le competenze essenziali per diventare competente con lo strumento.