Conserver des données propres dans Excel n’est pas seulement une question d’esthétique : c’est une condition pour des calculs fiables, des jointures correctes et des tableaux exploitables. Entre espaces invisibles, caractères non imprimables, erreurs de casse et séparateurs mal placés, les fichiers importés sont souvent un terrain miné. Ici je montre, pas à pas, comment repérer ces pièges et les corriger avec des formules simples, des astuces modernes et l’outil Power Query quand les jeux de données deviennent sérieux.
Somaire
En bref
🧹 SUPPRESPACE élimine les espaces superflus (espaces en début/fin et doubles espaces), mais il ne règle pas tous les cas : les espaces insécables (CAR 160) ou certains caractères non imprimables persistent et exigent une combinaison avec SUBSTITUE ou NETTOYER.
🔁 SUBSTITUE remplace une chaîne par une autre sans toucher à la casse. Pour supprimer des caractères précis (comme des points-virgules ou des tirets), c’est souvent la réponse la plus directe.
⚙️ Pour des volumes importants ou des transformations répétées, préférez Power Query : interface visuelle, historique des étapes et meilleures performances sur des tables longues.
🧩 En Excel récent, des fonctions avancées (p. ex. REGEXREPLACE, TEXTSPLIT) simplifient des cas complexes, mais les techniques classiques restent indispensables pour garantir la portabilité et la compréhension des feuilles.
Pourquoi nettoyer ses données ?
Un tableur bâclé produit des résultats trompeurs : des totaux qui s’écartent, des recherches impossibles avec RECHERCHEV ou INDEX/EQUIV, et des tableaux croisés dynamiques qui comptent plus de lignes qu’il n’en faut. Les causes courantes sont les espaces inattendus, les retours chariot cachés, les caractères copiés depuis le web ou les formats numériques stockés en texte. Nettoyer ne consiste pas à « corriger » toutes les valeurs manuellement, mais à appliquer une série d’opérations reproductibles qui rendent le fichier fiable et auditable.
Les fonctions de base à connaître
SUPPRESPACE (TRIM)
SUPPRESPACE supprime les espaces en début et fin de chaîne et remplace les multiples espaces par un seul. Exemple d’utilisation : =SUPPRESPACE(A2). C’est souvent la première étape car elle normalise l’espacement entre mots. Attention : SUPPRESPACE n’efface pas forcément les espaces non standards (p. ex. espace insécable), ni les caractères non imprimables.
SUBSTITUE (SUBSTITUTE)
SUBSTITUE remplace une sous-chaîne par une autre, sans tenir compte de la casse. C’est l’outil de choix pour enlever des caractères précis : =SUBSTITUE(A2; », »; » ») supprime toutes les virgules. Pour supprimer un espace insécable (CAR 160), on combine : =SUPPRESPACE(SUBSTITUE(A2;CAR(160); » « )). La logique est simple : on remplace d’abord la valeur problématique par un espace standard, puis on normalise les espaces.
NETTOYER (CLEAN)
NETTOYER élimine les caractères non imprimables (souvent issus de transferts depuis des systèmes ou du web). Si votre cellule affiche des carrés ou provoque des retours chariot intempestifs, NETTOYER est un bon réflexe : =NETTOYER(A2). Utilisé en combinaison avec SUPPRESPACE, il donne un résultat beaucoup plus propre.
MAJUSCULE, MINUSCULE, NOMPROPRE
Uniformiser la casse améliore la lisibilité et évite les doublons apparents. MAJUSCULE met tout en majuscules, MINUSCULE en minuscules, NOMPROPRE passe la première lettre en majuscule pour chaque mot. Exemples : =MAJUSCULE(A2) ou =NOMPROPRE(A2). Ces transformations sont essentielles avant des comparaisons de chaînes.
Formules pratiques et exemples
Voici des formules que vous pouvez coller et adapter. Je propose aussi une logique d’enchaînement : nettoyer les caractères invisibles → remplacer les caractères gênants → normaliser les espaces → ajuster la casse.
| But | Formule (fr) | Résultat attendu |
|---|---|---|
| Retirer retours chariot et tabulations | =NETTOYER(A2) | Texte sans caractères non imprimables |
| Remplacer espace insécable (CAR 160) | =SUBSTITUE(A2;CAR(160); » « ) | Espaces standard |
| Combiner nettoyage et trim | =SUPPRESPACE(NETTOYER(SUBSTITUE(A2;CAR(160); » « ))) | Texte net et espacé correctement |
| Supprimer un caractère spécifique | =SUBSTITUE(A2; »-« ; » ») | Retire tous les tirets |
| Uniformiser la casse | =NOMPROPRE(SUPPRESPACE(A2)) | Nom propre standardisé |
Cas pratique : nettoyer une colonne « Adresse »
Supposons une colonne contenant des adresses importées d’un CRM qui ont des espaces insécables et des retours chariot. La formule ci-dessous applique successivement les corrections :
=SUPPRESPACE(NETTOYER(SUBSTITUE(C2;CAR(160); » « )))
On commence par remplacer les CAR(160) par des espaces, puis NETTOYER enlève les caractères invisibles, enfin SUPPRESPACE standardise les espaces. Résultat : une adresse exploitable pour les tris et les fusions.
Techniques avancées et Excel moderne
Si vous travaillez avec Microsoft 365 ou une version récente, de nouvelles fonctions accélèrent le nettoyage. REGEXREPLACE permet d’effacer des motifs complexes (par exemple supprimer toutes les ponctuations). TEXTSPLIT et TEXTJOIN facilitent la recomposition après séparation. Mais deux remarques : 1) toutes les organisations n’ont pas ces versions, 2) la robustesse des formules classiques reste utile pour la portabilité.
Remplacer des motifs répétés
Sans REGEX, on peut imbriquer SUBSTITUE pour supprimer plusieurs caractères différents. Exemple :
- =SUBSTITUE(SUBSTITUE(SUBSTITUE(A2; ». »; » »); », »; » »); »; »; » ») supprime points, virgules et points-virgules.
- Si vous avez REGEXREPLACE : =REGEXREPLACE(A2; »[[:punct:]] »; » ») est plus élégant (selon disponibilité).
Power Query : quand l’utiliser
Power Query (Obtenir et transformer les données) est l’outil adapté si vous traitez des fichiers volumineux ou des imports réguliers. L’interface propose des opérations prêtes à l’emploi : transformer en type texte, supprimer les espaces, remplacer les valeurs, découper les colonnes, appliquer une action sur plusieurs colonnes simultanément. Chaque étape est enregistrée et peut être rejouée automatiquement lors de la mise à jour des données.
- Étapes fréquentes : Supprimer les lignes vides, transformer les types, remplacer les valeurs, supprimer les espaces et nettoyer les colonnes.
- Avantage : traçabilité — vous voyez la liste d’étapes et pouvez revenir en arrière.
- Inconvénient : légère courbe d’apprentissage pour écrire des transformations personnalisées (langage M).
Exemple simple dans Power Query
Importer un CSV → Cliquer sur la colonne → Transformations → Supprimer les espaces → Remplacer les valeurs → Fermer et charger. Les opérations deviennent reproductibles et évitent les erreurs manuelles.
Bonnes pratiques et checklist
Avant d’appliquer des transformations irréversibles, je recommande :
- Travailler sur une copie des données originales.
- Documenter les étapes (une feuille « README » ou commentaires dans le classeur).
- Appliquer les transformations de façon réversible : utiliser des colonnes auxiliaires plutôt que d’écraser.
- Valider un échantillon à chaque étape pour éviter d’introduire des erreurs (p. ex. supprimer un signe « – » qui faisait partie d’un code).
Table de références rapides
| Problème | Outils/formule | Remarque |
|---|---|---|
| Espaces en trop | SUPPRESPACE | Combinez avec SUBSTITUE pour CAR(160) |
| Caractères invisibles | NETTOYER | Idéal pour retours chariot et tabulations |
| Remplacer un caractère | SUBSTITUE | Ne modifie pas la casse |
| Opérations répétées / gros volumes | Power Query | Plus robuste et traçable que des colonnes de formules |
Conseils pour éviter les pièges
On pourrait croire qu’une seule formule résout tout, mais en réalité chaque source a ses particularités. Copie depuis un site web → attention aux entités HTML, export CSV depuis un logiciel → attention aux séparateurs locaux, import depuis un système comptable → attention aux formats numériques stockés en texte. Tester sur un sous-ensemble, puis automatiser via Power Query ou des macros si nécessaire.
FAQ
SUPPRESPACE supprime-t-il tous les espaces invisibles ?
Non. SUPPRESPACE gère les espaces classiques et les multiples espaces, mais certains caractères comme l’espace insécable (CAR 160) ou d’autres caractères non imprimables peuvent résister. On utilise alors SUBSTITUE et/ou NETTOYER en complément.
Que faire si mes nombres sont stockés en texte ?
Utilisez VALEUR pour convertir en nombre, ou dans Power Query changez le type en Nombre. Attention aux séparateurs décimaux locaux (virgule vs point) au moment de la conversion.
REGEX est-il préférable à SUBSTITUE ?
REGEX est plus puissant pour les motifs complexes, mais il n’est pas disponible partout. Pour garantir la portabilité et la compréhension, SUBSTITUE et des formules classiques restent un bon compromis.
Power Query remplace-t-il complètement les formules ?
Power Query est souvent préférable pour l’ingestion et la transformation initiale. Les formules restent utiles pour des calculs dynamiques au sein de la feuille. Les deux approches se complètent.
C’est si important de garder nos données propres dans Excel ! Cela évite tant de problèmes et rend notre travail plus efficace. Merci pour ces astuces pratiques !