Bonjour à tous,
Je vous invite à une petite discussion sur 3 nouvelles fonctions disponibles en insider, et aussi sur comment faire la même chose en VBA (donc pour toutes les versions d'excel !).
Microsoft vient d'annoncer en bêta (Insider) trois nouvelles fonctions qui vont permettre de gérer les REGEX ou encore expression régulière. La traduction française n'est pas encore disponible, mais au moins on peut déjà utiliser les versions anglaises.
Le fichier est disponible ici.
Ne pas oublier le clic droit, propriétés pour le déverrouiller.
En anglais, on a donc 3 fonctions :
REGEXTEST, REGEXREPLACE et REGEXEXTRACT
J'imagine qu'en Français, on aura ceci :
- TESTER.REGEX
- REMPLACER.REGEX
- EXTRAIRE.REGEX
Comme ces fonctions sont encore en béta-test, je vais juste donner quelques exemples simples, mais elles ont l'air très puissantes.
Exemple classique : le code postal dans une adresse.
La règle implicite est 5 chiffres qui se suivent. Par exemple, d pour décimal et { } pour indiquer la fréquence. Voici les deux formules. La première est disponible en mode insider, et la deuxième est liée au code VBA du fichier téléchargeable plus haut.
=REGEXEXTRACT(A2;"\d{5}")
=fctEXTRAIRE_REGEX(A2;"\d{5}")
Bref, les expressions régulières existent déjà depuis très très très longtemps dans Excel, en VBA. Il suffit d'ajouter une référence dans le VBE.
Et ensuite, on a accès à pleins de choses.
Mon post favori sur ce sujet est la réponse à la première réponse (en Anglais) de ce lien. C'est une mine d'or, qui va directement à l'essentiel.
En attendant Excel, voici comment faire en VBA
Voici un code VBA d'une fonction quasi-équivalente à REGEXEXTRACT (merci au lien ci-dessus et à ChatGPT pour l'aide à l'écriture de cette fonction). On peut encore beaucoup améliorer, mais je pense que c'est suffisant pour 90% des usages.
Pour l'utiliser, voici les arguments :
- la source,
- l'expression REGEX, et
- (optionnel) 0 ou 1 pour retourner la première valeur ou la liste des valeurs trouvées.
Par exemple : =fctEXTRAIRE_REGEX(A2;"\d{5}";1) pour extraire tous les codes postaux à 5 chiffres.
Note : je n'ai pas testé le retour de plusieurs résultats dans les anciennes versions d'Excel sans gestion de la propagation.
Function fctEXTRAIRE_REGEX(sSource As String, sRegex As String, Optional iChoix As Integer) As Variant
' Ajoutez une référence à Microsoft VBScript Regular Expressions (Menu: Tools > References > Cochez "Microsoft VBScript Regular Expressions 5.5")
' Rendre la fonction volatile pour recalculer à chaque modification
' Cette option peut ralentir les calculs sur de gros fichiers, donc a utiliser en fonction du contexte
Application.Volatile
' Définir un objet de type RegExp
Dim regEx As New RegExp
regEx.Global = True
regEx.MultiLine = True ' Permettre la correspondance sur plusieurs lignes
regEx.IgnoreCase = False ' Sensible à la casse, peut être ajusté selon les besoins
regEx.Pattern = sRegex
' Vérification et traitement basé sur le choix
' Retourner le premier résultat
On Error Resume Next ' Gérer les erreurs pour éviter les plantages si aucune correspondance n'est trouvée
fctEXTRAIRE_REGEX = regEx.Execute(sSource)(0).Value ' Retourne la première valeur trouvée
On Error GoTo 0 ' Réinitialiser la gestion des erreurs
If IsEmpty(fctEXTRAIRE_REGEX) Then
fctEXTRAIRE_REGEX = CVErr(xlErrNA) ' Si pas de correspondance, retourner la valeur d'erreur N/A
Exit Function
End If
If iChoix = 0 Then
'on ne fait rien, la valeur est deja retournée
Else
Dim matches As Object
Dim match As Object
Dim aResults() As String
Dim i As Integer
' Exécution du regex sur la source
Set matches = regEx.Execute(sSource)
' Si aucune correspondance n'est trouvée, retourner un tableau vide
If matches.Count = 0 Then
fctEXTRAIRE_REGEX = Array()
Exit Function
End If
' Redimensionner le tableau pour contenir les résultats
ReDim aResults(matches.Count - 1)
' Boucler à travers les correspondances et remplir le tableau
i = 0
For Each match In matches
aResults(i) = match.Value
i = i + 1
Next match
' Retourner le tableau de résultats
fctEXTRAIRE_REGEX = aResults
End If
End FunctionAutre exemple : le numéro de téléphone
La règle implicite est un format du style 99 99 99 99 99
=REGEXEXTRACT(A6;"\d{2} \d{2} \d{2} \d{2} \d{2}")
=fctEXTRAIRE_REGEX(A8;"\d{2} \d{2} \d{2} \d{2} \d{2}")Ou encore, si on veut s'amuser un peu :
=REGEXEXTRACT(A7;"(\d{2} ){4}\d{2}")
Gestion de plages multiples en entrée et sortie.
La fonction peut s'utiliser sur une plage en entrée :
Le résultat peut être propagé en mettant 1 comme troisième argument. Ici on propage sur B et C
L'expression ne trouve rien
Dans ce cas, Excel va retourner #N/A. On peut capturer cette erreur avec la fonction SI.NON.DISP.
[Mode râleur ON] Je ne comprendrai jamais pourquoi on a ESTNA et pas SI.NA. Cela aurait été tellement plus logique et simple. Bref.[Mode râleur OFF]
Évidemment, ce sont des Regex un peu simple, mais déjà vous pouvez déjà voir la puissance de ce langage.
Dans la vraie vie, je les ai utilisées pour éclater une bibliographie et séparer les auteurs, dates de publications, titre de l'article, titre du journal, etc.
Ce qui est très bien aussi, c'est que la base de connaissance internet sur les Regex sur internet est excellente, donc les GenAI (e.g. ChatGPT) sont déjà très bien entrainées pour ce type de problèmes.
Je vous souhaite de beaucoup vous amuser avec ce fichier !
S'il y a suffisamment d'intérêt, je vais aussi écrire la fonction pour les anciennes versions d'excel, et ajouter les fonctions de test et de remplacement.
N'hésitez-pas à partager vos trouvailles et cas d'usage.
A bientôt