Nettoyer les données dans Excel (sans VBA) 2


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

=SUPPRESPACE(EPURAGE(SUBSTITUE(A1;CAR(160);" ")))

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

=TRIM(CLEAN(SUBSTITUTE(A1;CHAR(160);" ")))

Trouver la formule Excel: 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:

=SUPPRESPACE(EPURAGE(SUBSTITUE(A1;CAR(160);" ")))

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…

=SUPPRESPACE(EPURAGE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;"/";" ");"_";" ");CAR(160);" ")))

Dans un Excel anglophone, la formule sera:

=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"/";" ");"_";" ");CHAR(160);" ")))

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

 

9 0

Partagez cette page...
Share on FacebookShare on Google+Share on LinkedInTweet about this on TwitterShare on RedditShare on TumblrDigg thisEmail this to someone

Laissez un commentaire

Votre adresse de messagerie ne sera pas publiée.

2 commentaires sur “Nettoyer les données dans Excel (sans VBA)

  • Trirème

    Les fonctions EPURAGE et SUPPRESPACE peuvent être utilisées une seule fois, et le troisième argument de la fonction SUBSTITUE peut être tout simplement "" (sans espace entre les guillemets)

    • excel-malin.com Auteur du billet

      Bonjour. Merci pour la remarque.
      Pour les EPURAGE et les SUPPRESPACE qui se répétaient, c'est juste – j'ai fait trop de "replace" en écrivant l'article. Je l'ai déjà modifié.
      Par contre, pour le remplacement de " " par "", je ne suis pas d'accord. Les raisons sont expliquées à la fin du point n°2…

      Bàv, Martin