Come posso usare le espressioni regolari in Excel e trarre vantaggio dalla potente configurazione a griglia di Excel per la manipolazione dei dati?
Capisco che Regex non è l'ideale per molte situazioni (Usare o non usare le espressioni regolari?) dato che excel può usare comandi di tipo Left
, Mid
, Right
, Instr
per manipolazioni simili.
Le espressioni regolari sono usate per il Pattern Matching.
Per usarle in Excel seguite questi passi:
Passo 1: Aggiungere il riferimento VBA a "Microsoft VBScript Regular Expressions 5.5"
Passo 2: Definisci il tuo modello
Definizioni di base:
-
Range.
a-z
corrisponde a lettere minuscole dalla a alla z0-5
corrisponde a qualsiasi numero da 0 a 5[]
corrisponde esattamente a uno degli oggetti dentro queste parentesi.
[a]
corrisponde alla lettera a[abc]
corrisponde ad una singola lettera che può essere a, b o c[a-z]
corrisponde a qualsiasi singola lettera minuscola dell'alfabeto.()
Raggruppa diverse corrispondenze ai fini della restituzione. Vedi gli esempi qui sotto.
{}
Moltiplicatore per copie ripetute del pattern definito prima di esso.
[a]{2}
corrisponde a due lettere minuscole a consecutive: aa
.[a]{1,3}
corrisponde ad almeno una e fino a tre lettere minuscole a
, aa
, aaa
+
Corrisponde ad almeno uno o più dei pattern definiti prima di esso.
a+
corrisponderà alle lettere consecutive a
, aa
, aaa
, e così via?
Abbina zero o uno dei pattern definiti prima di esso.
[a-z]?
corrisponde alla stringa vuota o a qualsiasi singola lettera minuscola.*
Corrisponde a zero o più dello schema definito prima di esso.
[a-z]*
corrisponde a una stringa vuota o a una stringa di lettere minuscole..
Corrisponde a qualsiasi carattere eccetto il newline \n
.
a.
Corrisponde a una stringa di due caratteri che inizia con a e finisce con qualsiasi cosa eccetto \n
.Operatore OR
a|b
significa che sia a
che b
possono essere abbinati.rosso|bianco|arancione
corrisponde esattamente a uno dei colori.Operatore ^
NOT
[^0-9]
non può contenere un numero[^aA]
non può essere minuscolo a
o maiuscolo A
.Escapes il carattere speciale che segue (sovrascrive il comportamento precedente)
[A]
, [A]
, [A]
, [A]
, [A]
, [A]
, [A]
, [A]
,[A],[A]
,[A],[A]
,[A],[A]
.Modelli di ancoraggio:
^
La corrispondenza deve avvenire all'inizio della stringa
^a
Il primo carattere deve essere una lettera minuscola a
.^[0-9]
Il primo carattere deve essere un numero.$
La corrispondenza deve avvenire alla fine della stringa
a$
L'ultimo carattere deve essere una lettera minuscola a
.Tabella di precedenza:
Order Name Representation
1 Parentheses ( )
2 Multipliers ? + * {m,n} {m, n}?
3 Sequence & Anchors abc ^ $
4 Alternation |
Abbreviazioni dei 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 guarda il valore nella cella A1
per vedere se i primi 1 o 2 caratteri sono cifre. Se è così, vengono rimossi e viene visualizzato il resto della stringa. In caso contrario, appare una casella che dice che non è stata trovata alcuna corrispondenza. I valori della 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-cellulare
Questo esempio è lo stesso dell'esempio 1 ma è impostato per essere eseguito come funzione in-cell. Per usarlo, cambiate il codice in 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
Metti la tua stringa ("12abc") nella cella A1
. Inserite questa formula =simpleCellRegex(A1)
nella cella B1
e il risultato sarà "abc".
Esempio 3: Loop Through Range
Questo esempio è lo stesso dell'esempio 1, ma esegue il loop attraverso 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: Scomposizione di modelli diversi
Questo esempio scorre un intervallo (A1
, A2
& A3
) e cerca una stringa che inizi con tre cifre seguita da un singolo carattere alfa e poi 4 cifre numeriche. L'output divide le corrispondenze del modello in celle adiacenti usando il simbolo ()
. $1
rappresenta il primo pattern trovato all'interno della prima serie 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:
Esempi di schemi 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
</i8> \<\/[a-zA-Z][0-9]\> Exact non-word character except any single alpha followed by any single digit
Per fare uso delle espressioni regolari direttamente nelle formule di Excel, la seguente UDF (user defined function) può essere d'aiuto. Espone più o meno direttamente la funzionalità delle espressioni regolari come una funzione di Excel.
Prende 2-3 parametri.
$0
, $1
, $2
, e così via. $0
è l'intera corrispondenza, $1
e superiori corrispondono ai rispettivi gruppi di corrispondenza nell'espressione regolare. Il valore predefinito è $0
.Estrazione di un indirizzo email:
=regex("Peter Gordon: [email protected], 47", "\w+@\w+\.\w+")
=regex("Peter Gordon: [email protected], 47", "\w+@\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 scomporre 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)
I risultati sono: Peter Gordon
[email protected]
...
Per usare questa UDF fate quanto segue (approssimativamente basato su questa pagina di Microsoft. Hanno alcune buone informazioni aggiuntive lì!)
ALT+F11
per aprire l'editor Microsoft Visual Basic for Applications.Regex
e la funzione regex
causa errori #NAME!).4. Nella grande finestra di testo al centro inserisci quanto segue:
Function regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") Come Variante
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
Con inputRegexObj
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = matchPattern
Fine con
Con outputRegexObj
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = "\$(\d+)"
Fine con
Con outReplaceRegexObj
.Global = True
.MultiLine = True
.IgnoreCase = False
Fine con
Set inputMatches = inputRegexObj.Execute(strInput)
Se inputMatches.Count = 0 Allora
regex = False
Altrimenti
Set replaceMatches = outputRegexObj.Execute(outputPattern)
Per ogni replaceMatch in replaceMatches
replaceNumber = replaceMatch.SubMatches(0)
outReplaceRegexObj.Pattern = "\$" & replaceNumber
Se replaceNumber = 0 Allora
outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)
Altrimenti
Se replaceNumber > inputMatches(0).SubMatches.Count Allora
'regex = "Trovato un tag $ alto. Il più grande permesso è $" & inputMatches(0).SubMatches.Count & "."
regex = CVErr(xlErrValue)
Esci dalla funzione
Altrimenti
outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))
Fine Se
Fine Se
Avanti
regex = outputPattern
Fine Se
Fine Funzione
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