Pourquoi parler de Regex en VBA dans Excel ?

Vous avez déjà passé 30 minutes à bricoler des GAUCHE(), DROITE(), STXT() et des SUBSTITUE() juste pour récupérer un numéro de téléphone propre dans une cellule ? Si oui, c’est que les expressions régulières (Regex) sont probablement l’outil qui vous manque.

Les Regex, c’est un langage ultra puissant pour rechercher, nettoyer et analyser du texte. Combinées à VBA dans Excel, elles transforment des tâches fastidieuses en automatisations élégantes… et surtout réutilisables.

Dans cet article, on va voir comment :

  • Activer et utiliser l’objet Regex en VBA
  • Nettoyer des données “sales” (téléphones, emails, codes clients…)
  • Extraire des informations précises depuis des textes
  • Créer des fonctions personnalisées Excel basées sur Regex

Activer l’objet Regex en VBA : la base

En VBA, les Regex passent par l’objet VBScript.RegExp. Deux façons de l’utiliser : avec ou sans référence.

Option 1 : avec référence (plus confortable)

Dans l’éditeur VBA :

  • Menu Outils > Références…
  • Cochez Microsoft VBScript Regular Expressions 5.5

Vous pouvez ensuite déclarer simplement :

Dim reg As RegExp

Option 2 : sans référence (plus portable)

Vous n’avez rien à cocher, mais vous utilisez CreateObject :

Dim reg As Object
Set reg = CreateObject("VBScript.RegExp")

Dans les deux cas, la logique d’utilisation est la même :

  • Définir un motif (pattern)
  • Appliquer ce motif à une chaîne de texte
  • Récupérer soit un booléen (Match / No Match), soit les correspondances

Comprendre les bases d’une Regex (sans se faire peur)

Les Regex peuvent faire un peu peur au début, mais pour 80 % des usages Excel, quelques motifs suffisent largement :

  • \d : un chiffre (0–9)
  • \D : tout sauf un chiffre
  • \w : une lettre, un chiffre ou underscore
  • \s : un espace (ou tabulation, retour à la ligne…)
  • + : une ou plusieurs fois
  • * : zéro ou plusieurs fois
  • ? : zéro ou une fois
  • {n,m} : entre n et m répétitions
  • ^ : début de chaîne
  • $ : fin de chaîne
  • [...] : un caractère parmi la liste

Exemples rapides :

  • \d+ : une suite de chiffres
  • [A-Za-z]+ : une suite de lettres (sans accents)
  • ^\d{5}$ : exactement 5 chiffres (utile pour un code postal)
  • \d{2}/\d{2}/\d{4} : une date au format jj/mm/aaaa

Créer une fonction VBA générique pour tester un motif

Commençons par un utilitaire simple : une fonction qui teste si une cellule correspond à un motif Regex.

Function RegexMatch(texte As String, motif As String, _
    Optional ignorerCasse As Boolean = True) As Boolean
  Dim reg As Object
  Set reg = CreateObject("VBScript.RegExp")

  With reg
    .Pattern = motif
    .IgnoreCase = ignorerCasse
    .Global = False
  End With

  RegexMatch = reg.Test(texte)
End Function

Utilisation directement dans Excel :

  • =RegexMatch(A2;"^\d{5}$") → teste si A2 est un code postal 5 chiffres
  • =RegexMatch(A2;"^\d{2}/\d{2}/\d{4}$") → teste si A2 ressemble à une date texte

Pratique pour contrôler la qualité de vos données en un coup d’œil.

Nettoyer des numéros de téléphone avec Regex VBA

Un cas classique en entreprise : les numéros de téléphone dans tous les formats possibles :

  • 06 12 34 56 78
  • +33 (0)6 12 34 56 78
  • 0612345678
  • +33-6-12-34-56-78

Objectif : obtenir un format propre et homogène, par exemple 0612345678.

Stratégie avec Regex :

  • Supprimer tout ce qui n’est pas un chiffre
  • Gérer le cas du +33 pour le convertir en 0

Function NettoyerTelephone(texte As String) As String
  Dim reg As Object
  Dim tel As String

  ' 1) Supprimer tout sauf les chiffres
  Set reg = CreateObject("VBScript.RegExp")
  With reg
    .Pattern = "\D"
    .Global = True
  End With
  tel = reg.Replace(texte, "")

  ' 2) Gérer le cas +33… devenu 33…
  If Left(tel, 2) = "33" Then
    tel = "0" & Mid(tel, 3)
  End If

  NettoyerTelephone = tel
End Function

Dans Excel :

  • =NettoyerTelephone(A2)

Et vous obtenez enfin une colonne de téléphones cohérente, prête à être utilisée pour des recherches, des exports CRM, etc.

Valider ou extraire des emails avec Regex

Les emails, autre grand classique. Pas toujours au bon format, parfois avec des espaces, parfois manquants.

Motif simple (et volontairement pas parfait, mais suffisant dans beaucoup de cas) :

^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$

Avec IgnoreCase = True, on ne s’embête pas avec la casse.

Exemple de fonction de validation

Function EmailValide(texte As String) As Boolean
  Dim reg As Object
  Set reg = CreateObject("VBScript.RegExp")

  With reg
    .Pattern = "^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$"
    .IgnoreCase = True
    .Global = False
  End With

  EmailValide = reg.Test(texte)
End Function

Dans Excel :

  • =EmailValide(A2) → VRAI / FAUX

Vous faites ensuite un filtre sur FAUX pour repérer les emails suspects à corriger manuellement. Rapide et efficace.

Extraire une partie de texte : codes clients, numéros, références

Une autre force des Regex : l’extraction ciblée. Par exemple, vos commentaires de commande contiennent :

  • Client: CLT-2023-4589 - urgent
  • [CLT-2022-9876] à traiter
  • CLT-2024-0012 - relance

Et vous voulez isoler le code client au format CLT-AAAA-NNNN.

Motif possible :

CLT-\d{4}-\d{4}

Fonction d’extraction du premier match

Function RegexExtract(texte As String, motif As String) As String
  Dim reg As Object, matches As Object
  Set reg = CreateObject("VBScript.RegExp")

  With reg
    .Pattern = motif
    .IgnoreCase = True
    .Global = False
  End With

  If reg.Test(texte) Then
    Set matches = reg.Execute(texte)
    RegexExtract = matches(0).Value
  Else
    RegexExtract = ""
  End If
End Function

Utilisation :

  • =RegexExtract(A2;"CLT-\d{4}-\d{4}")

Et d’un coup, vos codes clients sont isolés dans une colonne dédiée, exploitables pour des recherches ou des tableaux croisés dynamiques.

Nettoyer un texte “sale” : garder uniquement ce qui vous intéresse

Autre cas très courant : des données importées pleines de caractères inutiles : retours à la ligne, tabulations, symboles, espaces multiples…

Imaginons un texte avec :

  • Plusieurs espaces consécutifs
  • Retours à la ligne intempestifs
  • Caractères non imprimables

On peut créer une macro qui :

  • Remplace tous les espaces multiples par un seul espace
  • Supprime les retours à la ligne
  • Supprime les caractères non alphanumériques (optionnel)

Function NettoyerTexte(texte As String) As String
  Dim reg As Object

  Set reg = CreateObject("VBScript.RegExp")

  ' 1) Remplacer les espaces multiples par un seul
  With reg
    .Pattern = "\s+"
    .Global = True
  End With
  texte = reg.Replace(texte, " ")

  ' 2) Supprimer les retours à la ligne (si besoin)
  texte = Replace(texte, vbCr, " ")
  texte = Replace(texte, vbLf, " ")

  ' 3) Optionnel : garder uniquement lettres, chiffres et espaces
  With reg
    .Pattern = "[^A-Za-z0-9À-ÖØ-öø-ÿ ]"
    .Global = True
  End With
  texte = reg.Replace(texte, "")

  NettoyerTexte = Trim(texte)
End Function

Pratique sur les exports de systèmes externes, les données web copiées-collées, ou les fichiers CSV un peu “exotiques”.

Analyser du texte : trouver des motifs récurrents

Les Regex ne servent pas qu’à nettoyer, elles sont aussi utiles pour analyser. Par exemple :

  • Compter combien de cellules contiennent un numéro de commande
  • Repérer quelles lignes contiennent une date sous forme de texte
  • Identifier des hashtags, mentions, références produits, etc.

Voici un exemple de procédure pour surligner en jaune toutes les cellules d’une plage contenant un motif donné :

Sub SurbrillanceRegex(plage As Range, motif As String)
  Dim reg As Object, c As Range
  Set reg = CreateObject("VBScript.RegExp")

  With reg
    .Pattern = motif
    .IgnoreCase = True
    .Global = False
  End With

  For Each c In plage.Cells
    If Not IsError(c.Value) Then
      If reg.Test(CStr(c.Value)) Then
        c.Interior.Color = vbYellow
      End If
    End If
  Next c
End Sub

Utilisation depuis une macro :

Sub ExempleSurbrillance()
  Call SurbrillanceRegex(Selection, "CLT-\d{4}-\d{4}")
End Sub

Vous sélectionnez d’abord votre plage, lancez la macro, et toutes les cellules contenant un code client sont surlignées. Très visuel pour un audit rapide.

Créer une petite “bibliothèque” Regex réutilisable

Si vous commencez à utiliser Regex en VBA, un bon réflexe est de vous créer un module dédié dans lequel vous stockez :

  • Une fonction de test (*RegexMatch*)
  • Une fonction d’extraction (*RegexExtract*)
  • Quelques fonctions métiers : *NettoyerTelephone*, *EmailValide*, *NettoyerTexte*, etc.

Avantages :

  • Vous ne réécrivez pas le même code à chaque fichier
  • Vous standardisez vos règles de nettoyage
  • Vous pouvez les partager facilement avec vos collègues

Astuce : gardez en commentaire, en haut du module, une petite “cheat sheet” de vos motifs préférés. Par exemple :

  • Tel FR : "\d{10}"
  • Code postal : "^\d{5}$"
  • Date jj/mm/aaaa : "^\d{2}/\d{2}/\d{4}$"
  • Code client : "CLT-\d{4}-\d{4}"

Limiter les pièges : ce que Regex n’est pas

Même si Regex est très puissant, ce n’est pas une baguette magique. Quelques points à garder en tête :

  • Une Regex ne “comprend” pas le sens, elle ne fait que reconnaître des motifs
  • Un motif trop permissif risque de laisser passer des données incorrectes
  • Un motif trop strict risque de rejeter des données valides
  • La lisibilité compte : commentez vos motifs complexes en VBA

Exemple : pour les emails, une Regex peut filtrer 95 % des cas grossiers, mais elle ne vous dira jamais si la boîte existe réellement. Pour ça, il faudra une autre couche de validation (test d’envoi, API, etc.).

Et maintenant, que faire avec tout ça ?

Quelques idées d’usages concrets dans vos fichiers Excel :

  • Nettoyer automatiquement toutes les données importées avant de les charger dans un tableau croisé dynamique
  • Créer un bouton “Nettoyer les téléphones” sur un onglet de suivi commercial
  • Valider les emails d’un fichier d’inscription avant de les intégrer à votre outil d’emailing
  • Extraire des références produits depuis des descriptions longues pour alimenter une base produit propre
  • Mettre en couleur toutes les lignes de log contenant une erreur spécifique

Si vous jonglez souvent avec du texte dans Excel, prendre une heure pour apprivoiser Regex en VBA est un investissement qui se rembourse très vite. Une fois vos fonctions en place, vous ne verrez plus jamais vos colonnes de texte de la même façon.