Comment utiliser les expressions régulières dans Excel et tirer parti de la puissante configuration en grille d'Excel pour la manipulation des données ?
Je comprends que Regex n'est pas idéal pour de nombreuses situations (To use or not to use regular expressions ?) puisque Excel peut utiliser des commandes de type Left
, Mid
, Right
, Instr
pour des manipulations similaires.
Les [expressions régulières] (http://en.wikipedia.org/wiki/Regular_expressions) sont utilisées pour la correspondance de motifs.
Pour les utiliser dans Excel, suivez les étapes suivantes :
Etape 1 : Ajouter la référence VBA à "Microsoft VBScript Regular Expressions 5.5" ;
Étape 2 : Définissez votre modèle
Définitions de base:
-
Gamme.
a-z
correspond aux lettres minuscules de a à z.[]
Correspond exactement à l'un des objets contenus dans ces crochets.
[a]
correspond à la lettre a.()
Regroupe les différentes correspondances à des fins de retour. Voir les exemples ci-dessous.
{}
Multiplicateur pour les copies répétées du motif défini avant lui.
[a]{2}
correspond à deux lettres minuscules a consécutives : aa
[a]{1,3}
correspond à au moins une et jusqu'à trois lettres minuscules a
, aa
, aaa
.+
Correspond à au moins un, ou plus, du motif défini avant lui.
a+
correspondra aux a's consécutifs a
, aa
, aaa
, et ainsi de suite.?
Correspond à zéro ou un des motifs définis avant lui.
[a-z]?
correspond à une chaîne vide ou à une seule lettre minuscule.*
Correspond à zéro ou plus du motif défini avant lui.
[a-z]*
correspond à une chaîne vide ou à une chaîne de lettres minuscules..
Correspond à n'importe quel caractère sauf le saut de ligne \n
a.
correspond à une chaîne de deux caractères commençant par a et se terminant par n'importe quoi sauf \n
.|
Opérateur OR
a|b
signifie que l'on peut faire correspondre soit a
soit b
.rouge|blanc|orange
correspond à une seule des couleurs.Opérateur ^
NOT
[^0-9]
ne peut pas contenir un nombre.[^aA]
ne peut pas être un a
minuscule ou un A
majuscule.Le caractère spécial qui suit est échappé (remplace le comportement ci-dessus).
.`, `\\\`,
(,
\?,
\$,
\^`Modèles d'ancrage:
^
La correspondance doit se produire au début de la chaîne de caractères.
^a
Le premier caractère doit être la lettre minuscule a
.^[0-9]
Le premier caractère doit être un chiffre.$
La correspondance doit intervenir à la fin de la chaîne de caractères.
a$
Le dernier caractère doit être la lettre minuscule a
.Tableau de préséance:
Order Name Representation
1 Parentheses ( )
2 Multipliers ? + * {m,n} {m, n}?
3 Sequence & Anchors abc ^ $
4 Alternation |
Abréviations de caractères prédéfinis:
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
Exemple 1 : Exécuter en tant que macro
L'exemple de macro suivant examine la valeur de la cellule A1 pour voir si les 1 ou 2 premiers caractères sont des chiffres. Si c'est le cas, ils sont supprimés et le reste de la chaîne est affiché. Si ce n'est pas le cas, une boîte apparaît pour indiquer qu'aucune correspondance n'a été trouvée. Dans la cellule A1
, la valeur 12abc
renvoie abc
, la valeur 1abc
renvoie abc
, la valeur abc123
renvoie "Not Matched" ; parce que les chiffres ne sont pas au début de la chaîne.
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
Exemple 2 : Exécuter comme une fonction dans la cellule
Cet exemple est le même que l'exemple 1 mais il est configuré pour être exécuté comme une fonction in-cell. Pour l'utiliser, modifiez le code comme suit :
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
Placez votre chaîne de caractères ("12abc" ;) dans la cellule A1
. Entrez cette formule =simpleCellRegex(A1)
dans la cellule B1
et le résultat sera "abc" ;.
! [entrer la description de l'image ici] [1]
Exemple 3 : Loop Through Range
Cet exemple est identique à l'exemple 1, mais boucle sur une plage de cellules.
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
Exemple 4 : Séparation de différents motifs
Cet exemple parcourt en boucle une plage (A1
, A2
& ; A3
) et recherche une chaîne de caractères commençant par trois chiffres, suivie d'un seul caractère alphabétique, puis de quatre chiffres. La sortie divise les correspondances de motifs en cellules adjacentes à l'aide du signe "()".
$1représente le premier motif trouvé dans le premier ensemble 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
Résultats :
![entrez la description de l'image ici][2]
Exemples supplémentaires de motifs
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
[1] : http://i.stack.imgur.com/q3RRC.png [2] : http://i.stack.imgur.com/9eCZ5.png
Pour utiliser les expressions régulières directement dans les formules Excel, l'UDF (user defined function) suivante peut être utile. Elle expose plus ou moins directement la fonctionnalité des expressions régulières comme une fonction Excel.
Elle prend 2 ou 3 paramètres.
$0
, $1
, $2
, et ainsi de suite. $0
est la correspondance entière, $1
et plus correspondent aux groupes de correspondance respectifs dans l'expression régulière. La valeur par défaut est $0
.Extraction d'une adresse électronique :
=regex("Peter Gordon: [email protected], 47", "\w+@\w+\.\w+")
=regex("Peter Gordon: [email protected], 47", "\w+@\w+\.\w+", "$0")
Résultats : [email protected]
Extraction de plusieurs sous-chaînes :
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1")
Résultats : Mail électronique : [email protected], Nom : Peter Gordon
Pour décomposer une chaîne combinée dans une seule cellule en ses composants dans plusieurs cellules :
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 1)
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 2)
Résultats : Peter Gordon
[email protected]
...
Pour utiliser cette UDF, procédez comme suit (en vous basant sur [cette page Microsoft][1]. Elle contient des informations complémentaires intéressantes) :
ALT+F11
pour ouvrir l'éditeur Microsoft Visual Basic for Applications.Regex
et la fonction regex
provoque des erreurs #NAME!).![Deuxième icône dans la rangée d'icônes -> Module][4] 4. Dans la grande fenêtre de texte au milieu, insérez ce qui suit :
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
Avec inputRegexObj
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = matchPattern
End With
Avec outputRegexObj
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = "\$(\d+)" ;
End With
Avec outReplaceRegexObj
.Global = True
.MultiLine = True
.IgnoreCase = False
End With
Set inputMatches = inputRegexObj.Execute(strInput)
Si inputMatches.Count = 0 Alors
regex = False
Else
Set replaceMatches = outputRegexObj.Execute(outputPattern)
For Each replaceMatch In replaceMatches
replaceNumber = replaceMatch.SubMatches(0)
outReplaceRegexObj.Pattern = "\$" ; & ; replaceNumber
Si nombre de remplacement = 0, alors
outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)
Sinon,
Si replaceNumber > inputMatches(0).SubMatches.Count Alors
'regex = "Une à haute balise $ trouvée. Le plus grand autorisé est $" ; & ; inputMatches(0).SubMatches.Count & ; " ;." ;
regex = CVErr(xlErrValue)
Quitter la fonction
Else
outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))
End If
End If
Suivant
regex = modèle de sortie
End If
Fin de la fonction
[1] : https://support.office.com/en-in/article/Create-Custom-Functions-in-Excel-2007-2f06c10b-3622-40d6-a1b2-b6748ae8231f "Cette page Microsoft" ; [2] : /questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops#answer-22542835 [3] : http://i.stack.imgur.com/etyH4.png [4] : http://i.stack.imgur.com/nlTpn.png
Voici ma tentative :
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