Comment supprimer des caractères dans les cellules sélectionnées sur Excel ?

Dans ce tutoriel, je vais vous montrer comment supprimer des caractères donnés (lettres, nombres ou caractères spéciaux) dans une cellule très rapidement.

Cette astuce nous permettra de nettoyer rapidement des données en éliminant facilement les lettres, les chiffres ou les caractères spéciaux contenus dans les cellules.

Cela nous permettra ainsi de travailler avec des données propres et bien formatées en économisant du temps et des efforts.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, prenons le tableau suivant dans lequel nous pouvons constater que certaines cellules sont mal formatées :

Dans ce tableau, nous pouvons constater que les prix sont mal formatés avec des espaces en trop, et des symboles inutiles et même des caractères non visibles, comme le retour à la ligne que nous retrouvons sur la dernière cellule

Si nous devions travailler avec ces données, cela prendrait beaucoup de temps et d'efforts pour les nettoyer manuellement.

C'est là qu'intervient la macro que nous allons créer.

Celle-ci va en effet permettre d’automatiser le processus de nettoyage, ce qui nous fera gagner du temps et nous permettra de travailler plus efficacement avec des données propres et bien formatées.

2. Préparation de la macro de nettoyage des données

Comme nous venons de le voir, le nettoyage de ces cellules va donc passer par la mise en place d’une macro commande en VBA.

Une macro VBA (Visual Basic for Applications) est une série d'instructions ou de codes que nous allons écrire pour automatiser des tâches dans Excel.

Elle permet de faire des opérations complexes et répétitives en un seul clic, comme le nettoyage de données que nous allons mettre en place ici, mais également la génération de rapports ou la création de tableaux de bord interactifs.

Si vous ne connaissez pas du tout ce langage, pas de panique, je vais vous expliquer chaque ligne de code afin que tout le monde puisse suivre ce tutoriel.

Mais si vous souhaitez aller plus loin dans la découverte de ce langage et découvrir comment celui-ci permet de décupler les possibilités offertes par Excel, je vous invite à découvrir mon livre « Apprendre le VBA» en cliquant ici. Dans ce livre, vous apprendrez les bases du VBA et vous découvrirez comment il peut transformer votre façon de travailler avec Excel.

Maintenant, pour créer notre macro VBA, nous allons devoir lancer l'éditeur VBA en utilisant le raccourci clavier [Alt]+[F11]. Cela ouvrira l'interface de développement VBA.

Cet éditeur permet d’écrire, éditer et organiser des macros VBA à l’intérieur d’un classeur Excel.

Ensuite, nous allons pouvoir ajouter un nouveau module dans notre projet en sélectionnant « Insérer » > « Module ».

Un module de code est une feuille dans laquelle nous allons écrire les instructions pour la macro que nous allons créer.

Chaque module peut contenir plusieurs macros, et nous pourrons les organiser selon vos besoins.

Maintenant que nous avons les bases en place, passons à la création de notre macro de nettoyage de données Excel.

3. Création de la macro de nettoyage des données

Maintenant que le module est bien inséré dans le projet, nous allons pouvoir créer la macro pour nettoyer des caractères indésirables dans les cellules sélectionnées.

Pour cela, nous utilisons le mot-clé « Sub », suivi du nom de la macro que nous souhaitons créer :

Sub supprimerCaracteresDansSelection() 
 End Sub

Comme vous pouvez le constater, lorsque nous appuyons sur la touche [Entrée], la macro est insérée, ce qui se traduit par deux choses ici :

• Des parenthèses ouvrantes et fermantes sont ajoutées à la suite du nom de la macro. Nous verrons juste après que celles-ci vont nous permettre de venir saisir des arguments pour notre macro.
• La ligne « End Sub » a également été ajoutée. Celle-ci permet de venir fermer notre macro. Ainsi, tout ce que nous viendrons saisir entre ces deux lignes sera exécuté automatiquement à chaque fois que nous appellerons la macro.

Maintenant que la macro est créée, nous allons pouvoir définir des arguments à préciser lorsque nous ferons appel à celle-ci. Cette notion d’argument signifie que nous pouvons transmettre des informations à la macro pour personnaliser son comportement en fonction de nos besoins.

Ici, nous allons en définir trois arguments :

• lettres : Cet argument nous permettra de spécifier si nous souhaitons conserver ou supprimer les lettres dans les cellules sélectionnées.
• nombres : Cet argument nous permettra de spécifier si nous souhaitons conserver ou supprimer les chiffres dans les cellules sélectionnées.
• speciaux : Cet argument nous permettra de spécifier si nous souhaitons conserver ou supprimer les caractères spéciaux (c’est-à-dire tous les caractères qui ne sont ni des lettres, ni des chiffres) dans les cellules sélectionnées.

Nous allons également spécifier le type de ces arguments, c’est-à-dire les informations que nous allons souhaiter utiliser avec ces arguments.

Sub supprimerCaracteresDansSelection(lettres As  Boolean, nombres As Boolean, speciaux As Boolean)

Maintenant, à l’intérieur de la macro, nous allons avoir besoin de variables supplémentaires, que nous allons déclarer avec le mot-clé « Dim » et sur lesquelles nous allons également définir un type :

Dim c As Range, valeur As String, caractere As String,  i As Integer

Chacune de ces variables aura un rôle spécifique dans le fonctionnement de notre macro :

• c (Range) : La variable c sera utilisée pour boucler à travers toutes les cellules sélectionnées et de traiter leur contenu individuellement.
• valeur (String) : La variable « valeur » sera utilisée pour stocker la valeur nettoyée de chaque cellule après avoir supprimé les caractères indésirables.
• caractere (String) : La variable « caractere » sera utilisée pour stocker chaque caractère de la cellule actuellement en cours de traitement. Nous l'utiliserons pour vérifier si le caractère doit être conservé ou supprimé.
• i (Integer) : La variable i sera utilisée comme compteur pour parcourir chaque caractère de la cellule. Elle nous permettra de traiter un caractère à la fois en itérant à travers la chaîne de texte.

En utilisant ces variables, nous pourrons parcourir le contenu de chaque cellule, caractère par caractère, en vérifiant si chaque caractère doit être conservé ou supprimé en fonction des options spécifiées (lettres, nombres, speciaux). Une fois le nettoyage terminé, nous stockerons la valeur nettoyée dans la variable valeur et mettrons à jour la cellule avec cette valeur nettoyée. Cela permettra de nettoyer efficacement les données indésirables dans les cellules sélectionnées.

Nous pouvons à présent mettre en place la boucle qui permet de passer en revue chaque cellule de la sélection, en utilisant l’instruction « For Each ».

Comme nous venons de le voir, chaque cellule en cours d’analyse sera retournée par la variable « c » :

For Each c In Selection

Ensuite, à l’intérieur de cette boucle, nous allons commencer par initialiser la variable « valeur » comme étant une chaîne vide, ce qui nous permettra ensuite d’y stocker la valeur nettoyée de chaque cellule.

valeur = ""

Cela étant fait, nous allons maintenant boucler à travers chaque caractère correspondant au contenu de la cellule en cours de traitement, en utilisant cette fois-ci l’instruction « For… Next » avec la variable i.

Dans ce cas, la variable i est un compteur que nous utilisons pour itérer à travers chaque caractère de la chaîne de texte présente dans la cellule actuelle.

Elle aura dans un premier temps pour valeur 1, ce qui représente la première position de la chaîne de texte, et elle se déplace progressivement vers la droite à chaque itération de la boucle.

Cela nous permet d'examiner chaque caractère un par un pour déterminer s'il doit être conservé ou supprimé en fonction de nos critères de nettoyage.

For i = 1 To Len(c.Value)

C’est la fonction Len(c.Value) qui va permettre de connaître la longueur totale de la chaîne de texte dans la cellule c. Pendant chaque itération de cette boucle, nous extrayons le caractère situé à la position i dans la chaîne de texte à l'aide de la fonction Mid() :

caractere = Mid(c.Value, i, 1)

Ce caractère est ensuite stocké dans la variable caractere.

En vérifiant ce caractère avec nos critères spécifiés (lettres, nombres, speciaux), nous déciderons s'il doit être conservé ou supprimé.

Pour cela, nous allons déclarer deux nouvelles variables de chaîne de caractères, « listeLettres » et « listeNombres », dans lesquelles nous allons stocker les caractères autorisés :

    Dim listeLettres As String, listeNombres As String

Maintenant, nous allons définir la liste des lettres autorisées, que nous enregistrons dans la variable « listeLettres ».

Nous y enregistrons toutes les lettres, avec les accents, mais inutiles de tenir compte des majuscules :

    listeLettres = "  abcdefghijklmnopqrstuvwxyzàâäçéèêëîïôöùûü"

Nous faisons de même pour l’enregistrement des nombres, en ajoutant les caractères « , » et « - » pour les nombres décimaux ou négatifs :

    listeNombres = "0123456789,-"

Maintenant, nous pouvons vérifier si le caractère en cours d’analyse est effectivement une lettre ET que l'option « lettres » est activée, puis le supprimer si nécessaire :

    If lettres And InStr(1, listeLettres,  LCase(caractere)) > 0 Then
        caractere = ""
    End If

Pour effectuer cette vérification, nous utilisons une structure conditionnelle « If », qui permet ici d’évaluer si les deux conditions sont effectivement remplies simultanément grâce à l’utilisation de l’opérateur « And » :

• Le premier test consiste à vérifier si la valeur de l’argument « lettres » est activée (dans ce cas, l’argument vaut « True ») ou désactivée (« False »). Pour rappel, l’argument « lettres » est une variable booléenne qui indique si nous devons conserver les lettres de la cellule.
• Ensuite, le deuxième test utilise la fonction VBA InStr() pour effectuer une recherche du « caractere » dans la chaîne « listeLettres ». Pour simplifier la recherche, nous convertissons la valeur de « caractere » en minuscule avec la fonction VBA LCase() afin de rendre cette comparaison soit insensible à la casse. Si le « caractere » est bien identifié dans la chaîne « listeLettres », la fonction InStr() reverra sa position, et dans le cas contraire, elle renverra la position 0. Nous regardons donc si ce résultat est effectivement supérieur à zéro.

Et donc si ces deux conditions sont effectivement remplies, nous attribuons une chaîne vide « "" » à la variable « caractere ».

Cela équivaut à supprimer le caractère de la chaîne en cours d'analyse.

Ensuite, nous effectuons la même opération pour vérifier si le caractère est un nombre, en regardant tout d’abord si la valeur de l’argument « nombres » est égale à « True » ET également si la valeur de « caractères » est identifiée dans la chaîne « listeNombre » :

    If nombres And InStr(1, listeNombres, caractere)  > 0 Then
        caractere = ""
    End If

Évidemment, ici il n’est pas utile de passer la valeur de la variable « caractere » dans la fonction LCase() étant donné qu’il s’agit d’une valeur numérique.

Pour finir, il va nous rester à vérifier si la valeur de l’argument « speciaux » est égale à « True » ET si le caractère est un caractère spécial.

Pour simplifier, ici nous allons considérer qu’un caractère spécial est un caractère qui n’apparaît ni dans la chaîne « listeLettres », ni dans « listeNombres ».

Pour cela, nous allons simplement utiliser une esperluette (le symbole « & ») pour accoler les deux chaînes entre elles, et cette fois-ci nous allons souhaiter nous assurer que le caractère ne se trouve pas sur cette chaîne combinée, le résultat de la fonction InStr() doit alors être égal à zéro :

    If speciaux And InStr(1, listeLettres &  listeNombres, caractere) = 0 Then
        caractere = ""
    End If

Pour finir, il va nous rester à ajouter le caractère restant éventuellement dans la variable « caractere » à la suite de la variable « valeur », afin de reconstituer la chaîne de caractères correctement nettoyée.

Pour cela, nous utilisons à nouveau l’esperluette qui – comme nous venons de le voir – est l'opérateur de concaténation de chaînes en VBA.

Ainsi, lorsque nous utilisons le symbole « & », nous fusionnons le contenu de « valeur » et celui de « caractere » pour former la nouvelle chaîne :

    valeur = valeur & caractere

De cette manière chaque caractère valide est progressivement accumulé dans « valeur » au fur et à mesure que nous parcourons la cellule. Les caractères indésirables sont supprimés de la chaîne finale.

Nous retrouvons ensuite l’instruction « Next » qui permet de passer au caractère suivant de la cellule :

Next

Et il ne nous reste plus qu’à mettre à jour la valeur de la cellule avec la valeur nettoyée :

c = valeur

4. Utilisation de la macro

Une des particularités de la macro que nous venons de créer, c’est que nous ne pouvons pas l’utiliser directement en tant que telle.

En effet, celle-ci contenant des arguments, il est indispensable qu’elle soit appelée par une macro intermédiaire dans laquelle nous allons inclure ces arguments.

Nous allons donc créer les différentes macros correspondantes : « supprimerLettres », « supprimerChiffres », « supprimerCaracteresSpeciaux » et « supprimerToutSaufChiffres ».

Ces macros intermédiaires permettent en effet de personnaliser le nettoyage en fonction des besoins spécifiques de l'utilisateur, en activant ou en désactivant les options `lettres`, `nombres`, `speciaux` selon les cas.

Cela rend la macro de nettoyage de texte flexible et polyvalente, adaptée à différentes situations où il est nécessaire de traiter des données diverses.

Une fois ces macros mises en place, il ne nous reste plus qu’à sélectionner les cellules que nous souhaitons nettoyer, puis utiliser le raccourci clavier [Alt]+[F8] pour afficher la fenêtre de sélection des macros.

Ensuite, nous sélectionnons la macro « supprimerToutSaufChiffres » pour que les cellules soient correctement nettoyées