Excel Mania

Regex vba pour automatiser le nettoyage de données et l’analyse de texte dans excel

Nathan
Regex vba pour automatiser le nettoyage de données et l’analyse de texte dans excel

Regex vba pour automatiser le nettoyage de données et l’analyse de texte dans excel

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 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 :

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 :

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 :

Exemples rapides :

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 :

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 :

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

Stratégie avec Regex :

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 :

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 :

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 :

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 :

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 :

On peut créer une macro qui :

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 :

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 :

Avantages :

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

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 :

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 :

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.

Quitter la version mobile