Comment nettoyer une chaîne de caractères - Supprimer les espaces indésirables et les caractères illisibles sur Excel ?

Souvent lorsque nous sommes amenés à importer des données d’une application tierce, il est nécessaire d’effectuer un certain nombre de traitement sur ces données avant de pouvoir les exploiter correctement dans Excel.

L’une des causes récurrentes de ces retraitements est liée à la présence d’espaces non désirées à l’intérieur des chaînes de caractères, ce qui pourra éventuellement provoquer des bugs dans l’utilisation des formules.

Il peut également arriver que nous ayons à faire à des caractères indésirables, qui viendraient entraver la bonne exploitation des données.

 

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

Tutoriel Vidéo

 

1. Supprimer les espaces répétées

Tout d’abord, voyons comment supprimer les espaces non souhaitées d’une chaîne de caractères.

Pour cela, Excel nous propose la fonction SUPPRESPACE().

Cette fonction nous permettra alors de supprimer :

  • Les espaces présentes en début et en fin de cellule
  • Mais également les espaces répétées à l’intérieur d’une cellule, lesquelles seront alors remplacées par des espaces simples

La fonction SUPPRESPACE() est très simple à utiliser :

 =SUPPRESPACE(texte) 

Où l’argument texte correspond au texte que nous souhaitons nettoyer des espaces en trop.

Pour illustrer le fonctionnement de la fonction SUPPRESPACE(), nous disposons d’une liste des noms et prénoms du personnel d’une entreprise :

Excel formation - supprimer les espaces - 01

Comme nous pouvons le constater, de nombreuses espaces se sont immiscées, au début, au milieu ou encore à la fin des éléments de cette liste.

Pour exploiter cette liste, nous allons donc utiliser la fonction SUPPRESPACE() :

 =SUPPRESPACE(A7) 

Excel formation - supprimer les espaces - 02

Nous pouvons contrôler que toutes les espaces ont bien été supprimées, y compris ceux se trouvant en bout de cellule, en sélectionnant l’une des cellules, puis en cliquant sur [F2] afin de nous placer à l’intérieur de la formule et enfin en appuyant sur la touche [F9] pour obtenir directement la valeur de la formule :

Excel formation - supprimer les espaces - 03

Nous pouvons alors constater qu’il n’y a pas d’espace à la toute fin de la cellule.

Attention toutefois, il faut savoir que la fonction SUPPRESPACE() va supprimer automatiquement les espaces répétées, mais uniquement les espaces classiques.

Cela signifie que la fonction n’aura aucun effet sur les espaces insécables, qui bien que visuellement identiques sont un caractère totalement différent :

Excel formation - supprimer les espaces - 04

Pour inclure ces dernières dans le champ d’application de la fonction SUPPRESPACE, nous pourrons envisager de les transformer en utilisant la fonction SUBSTITUE(), qui permet de remplacer un caractère contenu dans un texte par un autre :

  =SUBSTITUE(texte;ancien_texte;nouveau_texte;no_position) 

  • Texte : est le texte dans lequel se trouve le ou les caractères à modifier,
  • Ancien_texte : est le texte à remplacer (ici l’espace insécable, que nous pouvons obtenir en utilisant la fonction UNICAR(160)),
  • Nouveau_texte : est le texte à insérer à la place d’ancien_texte (ici l’espace classique)

 

 =SUPPRESPACE(SUBSTITUE(A11;" ";"  ")) 

Les espaces insécables ont bien été nettoyées :

Excel formation - supprimer les espaces - 05

 

2. Supprimer toutes les espaces d’une cellule

Maintenant pour supprimer toutes les espaces d’une cellule et non pas seulement celles répétées, nous ne pourrons pas utiliser la fonction SUPPRESPACE(), ce qui aurait pour but de conserver une espace simple à l’intérieur de chaque texte.

Pour cela, nous allons devoir utiliser une technique que nous avons déjà utilisée dans un précédent tutoriel qui permet de supprimer un caractère donné dans une chaîne de caractère.

Cette technique repose tout simplement sur la fonction SUBSTITUE() que nous venons tout juste d’utiliser dans la partie précédente, et dans laquelle nous allons remplacer les espaces par des chaînes de caractères vides :

 =SUBSTITUE(A12;" ";"") 

Excel formation - supprimer les espaces - 06

 

3. Supprimer les caractères illisibles

Dans le cadre de l’importation d’un fichier de données généré par une application tierce, il peut également arriver que celui-ci génère des caractères spéciaux, qui seront illisibles et non inexploitables en tant que tels.

Pour les supprimer rapidement, nous pouvons utiliser la fonction EPURAGE() :

 =EPURAGE(texte) 

Ici encore, l’argument texte correspond au texte que nous souhaitons retraiter, afin d’y retirer tous les caractères illisibles.

 =EPURAGE(A13) 

Excel formation - supprimer les espaces - 07

La fonction, EPURAGE() permet d’automatiser la suppression des principaux caractères illisibles (les 32 premiers de la table ASCII).

Mais il existe encore des caractères illisibles contenus hors de cette plage (notamment les caractères 127, 129, 141, 143, 144, 157 et 160 de cette même plage ASCII) et qui ne seront donc pas traités par la fonction EPURAGE().

Excel formation - supprimer les espaces - 08

Pour cela, nous allons devoir utiliser une fois de plus la fonction SUBSTITUE() pour nous débarrasser de ces caractères :

 =SUBSTITUE(A14;"";"") 

Ici, pour obtenir la valeur de l’argument ancien_texte, nous avons simplement effectué un copier-coller du caractère concerné, que nous avons encadré par des guillemets.

Une autre méthode qui permet de supprimer rapidement tous les caractères non voulus va consister à utiliser la fenêtre de remplacement d’Excel :

  • Pour commencer, nous sélectionnons le caractère à supprimer dans la cellule
  • Ensuite, nous lançons la fenêtre de remplacement d’Excel (menu Accueil > Rechercher et sélectionner > Remplacer) ou plus rapidement encore en utilisant le raccourci clavier : [Ctrl]+[H]
  • Puis nous venons effectuer un collage du caractère dans la zone Rechercher, et nous laissons la zone « Remplacer par » vide
  • Et pour finir, nous cliquons sur le bouton « Remplacer tout » :

Excel formation - supprimer les espaces - 09

 

4. Supprimer des espaces cachées

Parfois, les caractères à supprimer, qu’il s’agisse d’espace ou d’un autre type de caractère, ne sont pas directement visibles dans la cellule.

Dans l’exemple qui suit, dans lequel nous retrouvons un numéro de sécurité sociale, nous avons bien des espaces affichés dans la cellule :

Excel formation - supprimer les espaces - 10

Par contre, si nous inspectons la barre de formule, en sélectionnant la cellule, nous pouvons nous rendre compte que ces espaces n’ont pas été saisis directement :

Excel formation - supprimer les espaces - 11

En fait, il s’agit d’un format de texte particulier appliqué directement à la cellule.

Nous pouvons nous en rendre compte en effectuant un clic droit, puis en cliquant sur « format de cellule » :

Excel formation - supprimer les espaces - 12

Nous y trouvons alors le format « #" "##" "##" "##" "###" "###" "## » qui permet d’afficher ces espaces.

Pour les supprimer, il suffit d’appliquer le format standard :

Excel formation - supprimer les espaces - 13

 

 

 



Articles qui pourraient vous intéresser

Comment calculer le PGCD et le PPCM (« Plus Grand Commun Diviseur » et « Plus Petit Commun Multiple ») sur Excel ?
Comment utiliser la fonction SOMMEPROD d’Excel
Comment nettoyer une chaîne de caractères - Supprimer les espaces indésirables et les caractères illisibles sur Excel ?
Comment compter le nombre de feuilles, de lignes ou de colonnes Excel ?
Comment extraire des parties de texte avec les fonctions DROITE() - GAUCHE() - STXT() d'Excel
Comment effectuer un test sur Excel : la fonction SI() (ainsi que ET() et OU())
Comment convertir des unités de mesure sur Excel ? La fonction CONVERT()
Comment modifier le résultat d’une formule renvoyant une erreur : la fonction SIERREUR() d’Excel ?
Comment obtenir des informations sur le classeur (nom et répertoire d’enregistrement) et sur les cellules (nom de l’onglet,…) sur Excel ?
Comment effectuer un test logique sur Excel : découvrir les fonctions ESTERREUR, ESTERR, ESTNA, ESTREF, ESTNUM, ESTTEXTE, ESTNONTEXTE,…
Comment classer simplement des données avec la fonction RANG (avec ou sans ex æquo) Excel ?
Oubliez RECHERCHEV() ! La fonction INDEX-EQUIV pour réaliser des recherches complexes sur Excel

Contact - Plan du site - À propos - Contact

© Excel-Formation 2018 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.