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

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.

Le espressioni regolari vengono utilizzate per la corrispondenza dei modelli.

Per utilizzare in Excel attenersi alla seguente procedura:

Passaggio 1 : aggiungere il riferimento VBA a “Microsoft VBScript Regular Expressions 5.5”

  • Seleziona la scheda “Sviluppatore” ( non ho questa scheda cosa devo fare? )
  • Seleziona l’icona “Visual Basic” dalla sezione della barra multifunzione “Codice”
  • Nella finestra “Microsoft Visual Basic, Applications Edition” selezionare “Strumenti” dal menu principale.
  • Seleziona “Riferimenti”
  • Seleziona la casella accanto a “Microsoft VBScript Regular Expressions 5.5” da includere nella cartella di lavoro.
  • Fai clic su “OK”

Passaggio 2 : definire il modello

Definizioni di base:

- Gamma.

  • Per esempio az corrisponde a lettere minuscole dalla a alla z
  • Ad esempio 0-5 corrisponde a qualsiasi numero da 0 a 5

[] Trova esattamente uno degli oggetti all’interno di queste parentesi.

  • Ad esempio [a] corrisponde alla lettera a
  • Ad esempio [abc] corrisponde a una singola lettera che può essere a, boc
  • Ad esempio [az] corrisponde a qualsiasi lettera minuscola dell’alfabeto.

() Raggruppa diverse corrispondenze a scopo di restituzione. Vedi esempi di seguito.

{} Moltiplicatore per copie ripetute di pattern definiti prima di esso.

  • Ad esempio [a]{2} corrisponde a due lettere minuscole consecutive a: aa
  • Ad esempio [a]{1,3} corrisponde almeno a una e fino a tre lettere minuscole a , aa , aaa

+ Abbina almeno uno, o più, del modello definito prima di esso.

  • Ad esempio, a+ corrisponderà a consecutivi a a , aa , aaa e così via

? Corrisponde a zero o a uno dei pattern definiti prima di esso.

  • Per esempio Pattern può essere o non essere presente ma può essere abbinato solo una volta.
  • Ad esempio [az]? corrisponde a una stringa vuota oa una singola lettera minuscola.

* Abbina zero o più del modello definito prima di esso. – Ad esempio Wildcard per pattern che può essere o non essere presente. – Ad esempio [az]* corrisponde a stringa vuota o stringa di lettere minuscole.

. Trova qualsiasi carattere tranne newline \n

  • Ad esempio a. Corrisponde a una stringa di due caratteri che inizia con a e termina con qualsiasi cosa tranne \n

| Operatore OR

  • Ad esempio a|b significa che a o b possono essere abbinati.
  • Ad esempio, red|white|orange corrisponde esattamente a uno dei colors.

^ NON operatore

  • Ad esempio [^0-9] carattere non può contenere un numero
  • Per esempio il carattere [^aA] non può essere minuscolo a o maiuscolo A

\ Escapes carattere speciale che segue (sovrascrive il comportamento sopra)

  • Ad esempio \. , \\ , \( , \? \$ , \^

Modelli di ancoraggio:

^ corrispondenza deve avvenire all’inizio della stringa

  • Per esempio ^a primo carattere deve essere lettera minuscola a
  • Es .: ^[0-9] primo carattere deve essere un numero.

$ partita deve essere eseguita alla fine della stringa

  • Ad esempio, a$ ultimo carattere deve essere lettera minuscola a

Tabella di precedenza:

 Order Name Representation 1 Parentheses ( ) 2 Multipliers ? + * {m,n} {m, n}? 3 Sequence & Anchors abc ^ $ 4 Alternation | 

Abbreviazioni di caratteri predefinite:

 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 

Esempio 1 : Esegui come macro

La seguente macro di esempio esamina il valore nella cella A1 per verificare se i primi 1 o 2 caratteri sono cifre. In tal caso, vengono rimossi e viene visualizzato il resto della stringa. In caso contrario, viene visualizzata una finestra che ti informa che non è stata trovata alcuna corrispondenza. I valori di cella A1 di 12abc restituiranno abc , il valore di 1abc restituirà abc , il valore di abc123 restituirà “Not Matched” perché le cifre non erano all’inizio della stringa.

 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 

Esempio 2 : Esegui come funzione in-cell

Questo esempio è uguale all’esempio 1, ma è configurato per essere eseguito come funzione in-cell. Per usare, cambia il codice con questo:

 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 

Inserisci le stringhe (“12abc”) nella cella A1 . Immettere questa formula =simpleCellRegex(A1) nella cella B1 e il risultato sarà “abc”.

inserisci la descrizione dell'immagine qui


Esempio 3 : Loop Through Range

Questo esempio è uguale all’esempio 1, ma scorre in un intervallo di celle.

 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 

Esempio 4 : divisione di modelli diversi

Questo esempio scorre in un intervallo ( A1 , A2 e A3 ) e cerca una stringa che inizia con tre cifre seguite da un singolo carattere alfa e quindi da 4 cifre numeriche. L’uscita divide le corrispondenze del modello in celle adiacenti usando () . $1 rappresenta il primo modello abbinato all’interno del primo set di () .

 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 

risultati:

inserisci la descrizione dell'immagine qui


Esempi di modelli aggiuntivi

 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  \<\/[a-zA-Z][0-9]\> Exact non-word character except any single alpha followed by any single digit 

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) Strumenti -/> Riferimenti”></li>
<li>  Trova <em>Microsoft VBScript Regular Expressions 5.5</em> nell’elenco e spunta la casella accanto ad esso. </li>
<li>  Clicca <em>OK</em> . </li>
</ol>
</li>
<li>
<p>  Clicca su <em>Inserisci modulo</em> .  Se assegni al tuo modulo un nome diverso, assicurati che il modulo <em>non</em> abbia lo stesso nome della UDF di seguito (ad es. Nominando il modulo <code>Regex</code> e la funzione <code>regex</code> causi errori <em>#NAME!</em> ). </p>
<p><img src= 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
    2. Salvare e chiudere la finestra Editor di Microsoft Visual Basic .

Espansione della risposta di 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
    ! [Excel VBA Form aggiunge riferimenti
  4. e selezionando Microsoft VBScript Regular Expression 5.5
    ! [Excel VBA aggiunge riferimento regex
  5. Inserisci un nuovo modulo (il codice deve risiedere nel modulo altrimenti non funziona).
    ! [Modulo codice inserto Excel VBA
  6. Nel modulo appena inserito,
    ! [Excel VBA inserisce il codice nel modulo
  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 . ! [Uso regolare delle espressioni regolari in cella 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

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 

Ecco una funzione regex_subst() . Esempi:

 =regex_subst("watermellon", "[aeiou]", "") ---> wtrmlln =regex_subst("watermellon", "[^aeiou]", "") ---> aeeo 

Ecco il codice semplificato (più semplice per me, comunque). Non riuscivo a capire come build un modello di output adatto utilizzando quanto sopra per funzionare come i miei esempi:

 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 

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