Fonction CLEAN() dans Excel : La meilleure façon de supprimer les caractères non imprimables

Vous est-il déjà arrivé d'importer des données dans Excel et de constater que des caractères étranges et cachés gâchaient votre travail ? Il s'agit de caractères non imprimables qui posent des problèmes de formatage et peuvent même fausser les calculs.  

Vous pouvez supprimer ces caractères indésirables à l'aide de la fonction CLEAN(). Dans cet article, je vais vous présenter la fonction CLEAN(), vous expliquer quand l'utiliser et comment elle peut vous faciliter la tâche.

Réponse rapide : La fonction Excel CLEAN()

Voici comment utiliser la fonction CLEAN 

• Type =CLEAN(.

• Saisissez le texte à nettoyer ou la référence de la cellule.

• Fermez la fonction à l'aide de ).

• Appuyez sur Entrer.

=CLEAN(text)

text peut être n'importe quelle chaîne que vous souhaitez nettoyer.

=CLEAN(A1)

A1 peut être remplacée par une autre référence de cellule.

Un exemple de la fonction CLEAN() en action

Voyons plus en détail comment nous pouvons utiliser la fonction CLEAN(). Cette fois-ci, je vais inclure des captures d'écran.

Ici, j'ai des données dans la cellule A2 avec quelques caractères indésirables. J'introduis donc la formule suivante dans la cellule B2 pour nettoyer mon texte :

=CLEAN(A2)

Utilisez la fonction CLEAN() dans une seule cellule. Image par l'auteur.

Outre le nettoyage d'une seule cellule, nous pouvons même utiliser la fonction CLEAN() pour nettoyer plusieurs cellules simultanément. Il vous suffit, après avoir écrit la formule dans la cellule appropriée, de faire glisser le petit carré vert situé dans le coin de la cellule jusqu'à la dernière ligne. De cette façon, la formule sera appliquée à l'ensemble des données et vous disposerez de données correctes en quelques secondes. 

Nettoyez plusieurs cellules avec la fonction CLEAN(). Image par l'auteur.

Je trouve la fonction CLEAN() particulièrement utile après avoir importé des données à partir de sources externes telles que des bases de données ou des pages web. En effet, lorsque vous importez des données, des caractères non imprimables portant les codes ASCII 0-31 peuvent apparaître. Bien que nous ne puissions pas les voir, ils peuvent perturber nos calculs ou donner un aspect étrange à nos données. 

La fonction CLEAN() d'Excel supprime ces caractères indésirables. Il nettoie même les retours à la ligne inutiles et les autres caractères cachés qui peuvent causer des problèmes de formatage. Il est donc parfois utile d'utiliser le site CLEAN() même si vous ne voyez pas de problèmes évidents.

Excel CLEAN() vs. TRIM()

Si vous avez suivi l'un de nos cours, comme Excel Fundamentals, vous connaissez peut-être une autre fonction Excel courante , la fonction TRIM().

Bien que les fonctions CLEAN() et TRIM() mettent toutes deux de l'ordre dans le texte, elles fonctionnent différemment. CLEAN() supprime les caractères non imprimables (ASCII 0-31) et TRIM() supprime les espaces supplémentaires (ASCII 32) avant, après ou entre les mots (sauf, commodément, un seul espace entre les mots). 

En d'autres termes, nous pouvons utiliser CLEAN() pour nettoyer les données présentant des problèmes de formatage cachés, mais si nos données présentent un espacement incohérent, nous pouvons y remédier à l'aide de la fonction TRIM().

Plus loin, je vous montrerai comment combiner CLEAN() avec TRIM() afin de supprimer les caractères non imprimables et les espaces supplémentaires.

Combinaison de CLEAN() avec d'autres fonctions Excel 

Maintenant que vous savez ce qu'est la fonction CLEAN(), voyons comment nous pouvons l'utiliser dans la vie réelle :

Combiner CLEAN() avec TRIM() 

Si vous remarquez dans l'exemple ci-dessus, CLEAN() n'a pas supprimé l'espacement bizarre entre les textes. Pour résoudre ce problème, je l'intègre dans la fonction TRIM() et vous pouvez voir que nous avons maintenant des données correctement formatées dans la colonne B:

=TRIM(CLEAN(A2))

Combinez les fonctions TRIM() et CLEAN(). Image par l'auteur.

Utilisation de CLEAN() avec SUBSTITUTE()

Il existe quelques caractères uni-code non imprimables autres que les premiers 32 caractères ASCII, et CLEAN() seul ne peut les gérer. Mais si nous combinons la fonction SUBSTITUTE() avec CLEAN(),, nous pouvons les supprimer facilement. Comprenons-le à l'aide d'un exemple :

J'ai un texte aléatoire dans la cellule A2 où il y a un problème d'espacement sur le côté gauche du texte. Mais lorsque j'ai essayé de l'enlever à l'aide de TRIM(), il n'est pas parti. Cela signifie qu'il ressemble à un espace, mais qu'il ne s'agit pas d'un espace, mais d'un caractère spécial. Dans ce cas, identifiez d'abord le code de caractère à l'aide de la formule suivante :

=CODE(LEFT(A2,1))

Cela donne le code du caractère, qui est 160. Vous pouvez maintenant l'enlever à l'aide de la formule suivante :

=SUBSTITUTE(CLEAN(A2),CHAR(160),"")

Pour un texte plus propre, insérez la formule ci-dessus à l'intérieur de TRIM().

=TRIM(SUBSTITUTE(CLEAN(A2),CHAR(160),""))

Nettoyez les cellules en utilisant CLEAN() et SUBSTITUTE(). Image par l'auteur.

Utilisation de CLEAN() avec REPLACE()

Si vous souhaitez remplacer tout autre délimiteur par des caractères non imprimables, vous pouvez combiner la fonction CLEAN() avec la fonction REPLACE(). La fonction REPLACE() d'Excel remplace une partie d'un texte par un nouveau texte en fonction d'une position de départ et d'une longueur spécifiées. 

=REPLACE(old_text, start_num, num_chars, new_text)

Par exemple, si je souhaite remplacer le signe - par un espace et supprimer tous les caractères non imprimables, j'utiliserai la formule suivante :

=REPLACE(CLEAN(A2), 4, 1, " ")

Cette formule nettoie d'abord l'ancien texte et remplace - à la position 4 par un espace.

Nettoyez le texte avec les fonctions REPLACE() et CLEAN(). Image par l'auteur.

Utilisation de CLEAN() avec REPT()

Nous pouvons également combiner la fonction REPT() avec CLEAN() pour nettoyer le texte. La fonction REPT() répète un texte un certain nombre de fois. 

=REPT(text, number_of_times)

Par exemple, j'applique la formule suivante à mon ensemble de données : 

=TRIM(CLEAN(SUBSTITUTE(A2, " ", REPT(" ", 2))))

Ici, le site SUBSTITUTE() remplace chaque espace par deux espaces à l'aide de REPT(" ", 2), ce qui rend l'espacement plus uniforme. Ensuite, CLEAN() supprime les caractères non imprimables cachés et TRIM() supprime les espaces supplémentaires, ne laissant qu'un espace entre les mots.

Nettoyez le texte en utilisant REPT() et CLEAN(). Image par l'auteur.

Comme vous pouvez le constater, CLEAN() apparaît à de nombreux endroits et fonctionne bien avec de nombreuses autres fonctions.

Quelques éléments à prendre en compte

Bien que CLEAN() et SUBSTITUTE() soient très utiles pour supprimer les caractères indésirables, il peut arriver que nous supprimions quelque chose d'important si nous n'y prêtons pas attention. Par exemple : 

• Perte accidentelle de données : Évitez d'utiliser des formules trop générales. Si nous remplaçons tous les espaces par rien, les mots peuvent être fusionnés. Et si nous supprimons tous les caractères spéciaux, notre formule peut supprimer les traits d'union des numéros de téléphone ou les décimales des prix. Par conséquent, avant d'appliquer une fonction, vérifiez que la formule fonctionne correctement. 
• Test sur les sous-ensembles : Par sécurité, je commence toujours par tester ma formule sur un petit ensemble de données. Au lieu de l'appliquer à des milliers de lignes en une seule fois, je l'essaie sur quelques-unes d'entre elles et je vérifie qu'il fonctionne comme prévu. Si tout semble bon, ce n'est qu'à ce moment-là que je l'applique à l'ensemble des données.

Réflexions finales

La fonction CLEAN() permet de nettoyer rapidement les données textuelles en supprimant les caractères non imprimables. Utilisé seul, il garantit des données propres et structurées. Combinée à la fonction TRIM(), elle améliore la lisibilité en éliminant également les espaces inutiles.

Si vous souhaitez apprendre d'autres trucs et astuces sur Excel, nous avons des cours très intéressants pour vous aider. Commencez par le cursus Excel Fundamentals pour apprendre les bases, puis passez aux cours Data Analysis in Excel et Data Preparation in Excel pour affiner vos compétences.