Nettoyer les données dans Excel (sans VBA)

Vous avez copiés des données externes dans votre fichier Excel et le résultat ne ressemble à rien? Il y a des espaces un peu partout? Pas de panique et pas de long nettoyage manuel à vue!

Pour "nettoyer" une chaîne de caractères (par exemple copiée/collée à partir d'internet), une solution facile existe. La voici pas à pas…

Nettoyage des données dans Excel sans VBA

Il est vrai que le plus simple pour nettoyer des données est de faire usage du VBA. Sa flexibilité permet de créer un nettoyage "sur mesure". Mais tout le monde ne maîtrise pas le VBA – et dans le cas de nettoyage de données dans Excel, il est quand même possible de s'en passer. Pour cela, on peut combiner plusieurs formules Excel et faire du nettoyage directement dans la Feuille de calculs.

Remarque: si vous devez travailler régulièrement avec des données externes (copiées & collées dans Excel), pensez quand même à (apprendre à) utiliser le VBA. À long terme, cela ne peut que vous économiser le temps et rendre votre travail beaucoup plus efficace.

Formule Excel pour nettoyer des données

Le plus souvent, il est possible de déterminer quel caractère "pollue" vos données. Habituellement, il s'agit d'espaces insécables ou d'autres caractères non-visibles (retour chariot, fin de ligne, etc.). Une fois le caractère(s) à nettoyer déterminé(s), on peut composer la formule Excel pour s'en débarrasser.

Prenons un exemple: en supposant que le texte se trouve dans la cellule "A1" et que les caractères à supprimer sont les espaces et les espaces insécables, la formule à utiliser sera la suivante:

Notez bien que cette formule est utilisable dans la version francophone d'Excel. Pour la version anglophone, vous devez utiliser la formule suivante:

Trouver la formule Excel pour nettoyer les données: Pas à pas

Comme vous pouvez le voir, cette formule est composée de 4 formules différentes. Voici l'explication comment cette formule est construite.

1. Déterminer le caractère à supprimer: fonction CODE

Pour déterminer exactement le caractère à éliminer, on va obtenir son numéro de code grâce à la formule CODE qui contient le caractère en question. Si vous voulez, par exemple, supprimer tout les surlignés (_) il faudra utiliser la formule suivante: CODE("_"). Elle donnera comme résultat le chiffre 95 (ce qui correspond au code Windows-1252 pour le caractère "_").

Ce chiffre nous permettra de remplacer le caractère en question dans l'étape suivante.

2. Remplacer les caractères à supprimer: fonctions SUBSTITUE et CAR

Une fois identifiés, on remplace les caractères "pollueurs" grâce à la formule SUBSTITUE. Dans la plupart de cas, on va les remplacer par "rien": SUBSTITUE(A1;"_";"") ce qui correspond en fait à les supprimer.

Tant qu'il s'agit de caractères visibles, on peut les substituer/supprimer directement. Par contre, pour substituer les caractères "invisibles", on sera obligés de passer par la formule CAR parce qu'il est difficile de les insérer dans la formule (ils ne se trouvent pas sur le clavier et on ne peut donc pas les "écrire" dans la formule. La formule CAR permet de les reproduire.

Si on veut donc supprimer les espaces insécables, il faudra passer par la formule: SUBSTITUE(A1;CAR(160);"")

Ici, un autre problème se pose: si on supprime les espaces insécables (qui sont souvent multiples), on aura le texte collé (exemple: "1    abc    def    " va se transformer en "abcdef") ce qui n'est probablement pas le résultat souhaité. Pour résoudre ce problème, on remplacera simplement l'espace insécable par un espace normal. Les multiples espaces "normaux" consécutifs seront "corrigés" à l'étape suivante.

3. Nettoyage des données: fonctions EPURAGE et SUPPRESPACE

Nous enlèverons ensuite les caractères non-imprimables (sauts de ligne,…) avec la fonction EPURAGE et pour terminer, nous supprimerons les espaces multiples avec la fonction SUPPRESPACE. De cette manière, les données devraient être nettoyées.

Si on imbrique les différentes fonctions, on obtiendra la formule mentionnée en haut:

4. Nettoyage multiple en une seule formule Excel

Si vos données contiennent plusieurs caractères à nettoyer, vous pouvez les nettoyer successivement dans une seule formule. Mais attention à la lisibilité de vos formules. A partir du 3^ème nettoyage, cette solution devient assez complexe et également gourmande en ressources de mémoire.

Exemple: voici comment on peut nettoyer des données en se débarrassant des espaces insécables, des soulignés et des barres obliques…

Dans un Excel anglophone, la formule sera:

Comme vous pouvez constater, la formule devient vraiment complexe – dans des cas pareils, l'utilisation de VBA est fortement recommandée!

Solution alternative: Ctrl+H

Une autre solution simple consiste en l'utilisation de l'outil de remplacement dans Excel: Il vous suffit de choisir la plage contenant les données et activer l'écran de remplacement avec le raccourci clavier Ctrl + H. Dans cette fenêtre, vous indiquez quel caractère doit être remplacé et par quoi. Rien de plus simple…

Attention cependant: cette méthode ne fonctionnera pas pour certains caractères – les caractères "spéciaux" tel que l'espace insécable, retour à la ligne etc.

Et une autre remarque: si les cellules contiennent plusieurs espaces consécutifs (donc le nombre varie) et vous vous voulez en garder un seul, mettez dans le champ "Rechercher" DEUX espaces consécutifs et dans le champ "Remplacer par" UN espace. Cliquez sur le bouton "Remplacer tout" plusieurs fois d'affilé jusqu'à ce que le message "Nous n'avons pas trouvé ce que vous recherchez…" s'affiche. Cela voudra dire que le texte ne contient plus d'espaces consécutifs…

Pour aller plus loin en Microsoft Excel

Pour clôturer cette astuce Excel, je vous propose quelques autres articles que pourraient vous être utiles…

• • Liste de toutes les fonctions Excel (déjà plus de 480 fonctions!)
  • Tout sur Excel Online Gratuit
  • Calcul du POURCENTAGE en Excel
  • Grand tutoriel sur les Tableaux Croisés Dynamiques dans Excel
  • Les principales fonctions Excel en détail: RECHERCHEV, RECHERCHEX, SI, NB.SI, SOMME.SI,…
  • … et n'oubliez pas à consulter la section "Téléchargements" – vous y trouverez sans doute plusieurs outils pratiques qui pourront vous servir