Supprimer les espaces et caractères invisibles dans Excel : SUPPRESPACE, EPURAGE et l’espace insécable

« Client A » et « Client A » semblent identiques, mais pour Excel ce sont deux textes différents. Cette différence invisible fait renvoyer #N/A à RECHERCHEV, affiche le même client deux fois dans un tableau croisé dynamique ou laisse des lignes répétées après la suppression des doublons.

Les espaces en trop et les caractères cachés arrivent presque toujours avec des données copiées de pages web, exportées d’autres systèmes ou saisies à la main. Vous verrez ici quels caractères posent problème, comment les repérer et comment les nettoyer avec SUPPRESPACE, EPURAGE et SUBSTITUE, y compris le plus insaisissable : l’espace insécable.

Quels caractères invisibles posent problème

Tous les « espaces » ne se valent pas. Chaque caractère a un code, et savoir lequel vous avez sous les yeux détermine la fonction qui le supprime :

Comment repérer les espaces et caractères cachés

Avant de nettoyer, mieux vaut savoir combien de cellules sont concernées. Ces vérifications vous le disent en quelques secondes.

Comparer les longueurs

NBCAR compte tous les caractères, y compris les invisibles. Si la longueur change quand vous appliquez SUPPRESPACE, la cellule contenait des espaces en trop :

=NBCAR(A2)-NBCAR(SUPPRESPACE(A2))

La formule renvoie le nombre d’espaces en trop : tout résultat supérieur à 0 signale une cellule à vérifier. Vous pouvez aussi l’utiliser comme règle dans Accueil › Mise en forme conditionnelle › Nouvelle règle › Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué afin de les surligner en couleur.

Identifier le caractère exact

Si SUPPRESPACE ne change rien mais que la cellule ne correspond toujours pas, trouvez le code du dernier caractère :

=UNICODE(DROITE(A2;1))

Un 160 signale un espace insécable ; un 10, un saut de ligne. Remplacez DROITE par GAUCHE pour examiner le premier caractère. UNICODE est disponible depuis Excel 2013 ; dans les versions antérieures, utilisez CODE.

Comparer deux cellules avec précision

Quand deux valeurs semblent identiques sans correspondre, =A2=B2 renvoie FAUX même si vous ne voyez pas la différence. Comparez alors leurs longueurs avec NBCAR : celle qui a le plus de caractères cache quelque chose.

SUPPRESPACE : supprime les espaces en trop

SUPPRESPACE fait trois choses à la fois :

• Elle supprime les espaces au début du texte.
• Elle supprime les espaces à la fin.
• Elle réduit à un seul les espaces répétés entre les mots.

=SUPPRESPACE(A2)

La dernière ligne montre sa limite : SUPPRESPACE ne supprime que l’espace normal (code 32). L’espace insécable reste en place.

EPURAGE : supprime les caractères de contrôle

EPURAGE supprime les caractères non imprimables de code 0 à 31, dont le saut de ligne, le retour chariot et la tabulation :

=EPURAGE(A2)

Attention aux sauts de ligne : EPURAGE les efface sans rien mettre à leur place, si bien que « Rue Principale » sur une ligne et « Local 3 » sur la suivante deviennent « Rue PrincipaleLocal 3 ». Si vous préférez les séparer par un espace, remplacez d’abord le saut de ligne :

=SUPPRESPACE(SUBSTITUE(A2;CAR(10);" "))

L’espace insécable et autres cas délicats

L’espace insécable (code 160) est le coupable habituel quand rien ne marche. Ni SUPPRESPACE ni EPURAGE ne l’éliminent. La solution consiste à le transformer d’abord en espace normal avec SUBSTITUE :

=SUPPRESPACE(SUBSTITUE(A2;CAR(160);" "))

Dans Excel pour Mac, CAR utilise un autre jeu de caractères ; il est donc plus sûr d’utiliser UNICAR (Excel 2013 et versions ultérieures), qui fonctionne de la même façon sur toutes les plateformes :

=SUPPRESPACE(SUBSTITUE(A2;UNICAR(160);" "))

L’espace de largeur nulle (code 8203) n’occupe même pas de place à l’écran. Pour l’enlever, remplacez-le par rien :

=SUBSTITUE(A2;UNICAR(8203);"")

La formule de nettoyage complète

En combinant le tout, cette formule règle la grande majorité des cas d’un seul coup :

=SUPPRESPACE(EPURAGE(SUBSTITUE(A2;UNICAR(160);" ")))

Si le résultat doit être un nombre, entourez-la de CNUM. N’oubliez pas qu’après le nettoyage, vous devez copier la colonne auxiliaire et la coller en valeurs sur l’originale.

Nettoyer sans formules

Rechercher et remplacer

Pour une correction rapide directement sur les données d’origine :

1. Sélectionnez la plage et allez dans Accueil › Rechercher et sélectionner › Remplacer.
2. Dans « Rechercher », saisissez le caractère à supprimer. Pour un saut de ligne, appuyez sur Ctrl+J. Pour un espace insécable, maintenez Alt et tapez 0160 sur le pavé numérique.
3. Dans « Remplacer par », tapez un espace ou laissez vide.
4. Cliquez sur Remplacer tout.

Ne remplacez pas tous les espaces normaux par rien : vous colleriez les mots entre eux. Pour ceux-là, utilisez SUPPRESPACE.

Power Query

Si vous répétez le même nettoyage à chaque fichier, Power Query (Excel 2016 et versions ultérieures) l’enregistre comme une étape. Sélectionnez la colonne et, dans Transformer › Format, choisissez Supprimer les espaces puis Nettoyer. Attention : dans Power Query, Supprimer les espaces n’enlève que ceux des extrémités, pas les espaces doubles au milieu.

Erreurs fréquentes

• Garder les formules au lieu des valeurs : si vous supprimez la colonne d’origine, la colonne nettoyée se remplit de #REF!.
• Croire qu’EPURAGE supprime le code 160 : ce n’est pas le cas ; il faut SUBSTITUE.
• Ne nettoyer qu’un des deux tableaux : si vous croisez des données avec RECHERCHEV ou RECHERCHEX, nettoyez à la fois la colonne de recherche et les valeurs cherchées.
• Oublier les en-têtes : un espace à la fin d’un nom de colonne casse aussi les références et les tableaux croisés dynamiques.

Après le nettoyage : doublons et lignes vides

En nettoyant, on découvre souvent que des valeurs qui semblaient différentes étaient en fait identiques, et que certaines lignes ne contenaient rien d’utile. S’il vous reste des lignes vides dispersées dans la feuille, notre outil gratuit pour supprimer les lignes vides les enlève de tout le fichier en une fois, sans trier ni filtrer à la main. Et pour les enregistrements qui se répètent désormais, vous pouvez utiliser notre outil de suppression des doublons.

Conclusion

Les caractères invisibles ne se voient pas, mais ils cassent recherches, comparaisons et comptages. Repérez-les en comparant NBCAR avant et après SUPPRESPACE, identifiez le caractère avec UNICODE et éliminez-le avec la bonne combinaison : SUPPRESPACE pour les espaces normaux, EPURAGE pour les caractères de contrôle et SUBSTITUE pour l’espace insécable et l’espace de largeur nulle.

Questions fréquentes