Tutoriels > Tutoriels & Astuces Excel > Fonctions & Formules > Purgez les espaces inutiles !

Purgez les espaces inutiles !

Publié par Mth le 05-04-2010 (66816 lectures)

On se confronte régulièrement au problème de suppression des espaces inutiles dans les cellules de feuille de calcul.

Les cas de figure rencontrés peuvent être différents, les solutions aussi, comme nous allons le voir dans cet article.

Au fil des exemples, nous évoquerons un certain nombre de fonctions comme SUPPRESPACE() - SUBSTITUE() - CAR() - CODE()STXT() ou encore CNUM() . Pour découvrir ces fonctions, consultez les articles qui leur sont consacrés ainsi que les fichiers d'exercices corrigés qui les accompagnent. Ces derniers vous permettront de vous entraîner avec des cas concrets.


Nous allons aborder les points suivants :



• Supprimer les espaces inutiles en conservant les séparateurs de mots:

La fonction SUPPRESPACE() est spécialement adaptée à cette situation comme nous pouvons le voir dans l'exemple ci-dessous, où nous disposons d'une liste de noms, parsemée d'espaces simples ou doubles, à droite ou à gauche.

Cette présentation est gênante, notamment pour effectuer des tris, mais la fonction SUPPRESPACE() permet de restructurer proprement ces données:
Supprimer les espaces - 1

Aussi nombreux soient-ils, tous les espaces à droite et à gauche ont été éliminés, et un seul espace a été conservé entre les mots.

Pour en savoir plus, consultez l'article consacré à la fonction SUPPRESPACE() .


• Supprimer tous les espaces:

Dans l'exemple suivant, nous héritons de deux listes de ventes par article reprenant les mêmes références. Dans la première liste les codes contiennent des espaces, alors qu'ils n'en contiennent pas dans la seconde liste.

Avant de pouvoir synthétiser les chiffres sur la base du code article, il est donc nécessaire d'éliminer tous les espaces du code de la liste 1; nous allons pour cela utiliser la fonction SUBSTITUE():
 

Supprimer les espaces - 2

Dans la colonne C, tous les espaces (notés dans la fonction par un espace entouré de guillemets : " "), quels qu'ils soient ,ont donc été éliminés, ou plus exactement remplacés par du vide (noté dans la fonction avec deux guillemets l'un à coté de l'autre :  "" ).

Sans préciser la position, la fonction élimine tous les espaces.

Pour en savoir plus, consultez l'article consacré à la fonction SUBSTITUE() ainsi que le fichier d'exercices corrigés qui l'accompagne.

 

 

 

• Supprimer un seul espace:

Imaginons maintenant que nos codes articles soient des « EAN13 », dans la liste 1, le préfixe et la clé sont séparés par un espace, dans l'autre liste, seul le préfixe est isolé. Nous voulons retirer le deuxième espace dans la liste 1, et devons pour cela préciser dans la fonction SUBSTITUE() la position 2:

 

Supprimer les espaces - 3 


• Supprimer un espace dit « caractère spécial » :

Ce cas s'avère relativement fréquent lorsqu'on importe des données provenant d'autres systèmes ou d'internet. Il faut y penser dès qu'on s'aperçoit que les fonctions SUPPRESPACE() ou SUBSITUE(), telles que présentées ci-dessus, ne produisent pas l'effet escompté.

Dans cet exemple, nous avons rapatrié une liste de codes articles dont nous voulons éliminer ces espaces particuliers:

Supprimer les espaces - 4

Ampoule astuce  Astuce:

Point bleu  Pour connaître le numéro (code ASCII) du caractère, utilisez la fonction CODE(). Cette fonction indique le code du premier caractère d'un texte. Dans notre exemple ci-dessus, on peut effacer dans la barre de formule les trois premiers caractères de la cellule A4 et laisser l'espace qui apparaît après « 978 ». Dans une cellule quelconque ,entrez alors la formule =CODE(A4) qui, dans notre cas, renverra comme résultat « 160 » (au lieu de « 32 » correspondant au code ASCII habituel pour le caractère espace).

Point bleu  On peut aussi utiliser la fonction STXT() qui permet d'extraire dans un texte un nombre de caractères à partir d'une position: STXT(texte; n°départ; nombre caractères).

Ici "texte" serait notre cellule A4, le caractère à extraire est l'espace soit le numéro 4, la chaine à extraire est d'une longueur 1 pour 1 seul caractère. STXT(A4 ;4 ;1) nous renvoie le caractère espace, et nous pouvons l'intégrer dans la fonction SUBSTITUE() pour obtenir notre résultat en une seule opération. Au lieu de SUBSTITUE(A6;CAR(160);"") nous pouvons écrire SUBSTITUE(A6;STXT(A6;4;1);"").

 

L'avantage d'utiliser ici la fonction STXT(), est qu'elle permet à SUBSTITUE() de remplacer n'importe quel caractère présent en position 4 par du vide. En revanche si la position varie, il est plus simple d'utiliser la fonction CAR().

 

Remarque : en dehors du code 160, on peut aussi rencontrer le code 127, ou encore des espaces entre guillemets, etc...


Pour en savoir plus sur les fonctions CAR() et Code(), consultez l'article qui aborde ces fonctions, ainsi que le fichier d'exercices corrigés qui l'accompagne.



• Supprimer tous les caractères espace d'une zone par le menu « Remplacer »: 

Pour supprimer tous les espaces en une seule opération, sélectionner la zone concernée, puis choisir le menu « Remplacer » :
  • Pour Excel 2000 et 2003: cliquer sur le menu Edition / Remplacer
  • Pour Excel 2007, dans le premier l'onglet Accueil, groupe Edition sur la droite, cliquer sur Rechercher
  • Pour toutes les versions, on peut aussi utiliser le raccourci clavier : CTRL + H

 

La fenêtre de recherche apparaît:
 

Supprimer les espaces - Menu Chercher Remplacer


La renseigner comme indiqué puis cliquer sur « Remplacer tout ».



• Les espaces peuvent correspondre à un format et non pas à des espaces réels:

Pour poursuivre sur notre dernier exemple, si dans la barre de formule est inscrit « 9782742968534 » sans espace, et que visuellement la cellule présente des espaces, cela signifie que nous sommes en présence d'un format de cellules qui crée les espaces à l'affichage alors qu'il n'y en a pas physiquement dans la cellule.

Par exemple, le format personnalisé 0##-0000" "00 appliqué sur 123456789 affiche 123-4567 89 alors qu'il n'y ni tiret, ni espace dans le nombre.

Pour afficher les données sans "espace", il suffit dans ce cas de choisir un format « standard » dans la cellule. Pour Excel 2007 : onglet Accueil /Groupe Nombre / Format standard, et pour les versions antérieures : menu Format / Cellules / Onglet Nombres et choisir Standard)



Icone info  A noter:  
 

Après l'application des différentes fonctions ci-dessus, une suite numérique sans espace peut-être considérée par excel comme du texte et non pas comme des chiffres (on s'en aperçoit car dans ce cas, au lieu de s'aligner à droite de la cellule, les chiffres restent alignés à gauche).

Pour transformer le résultat « texte » de nos formules en résultat « numérique » afin de pouvoir faire des calculs, il suffit de multiplier le résultat obtenu par 1, ou d'encadrer nos formules par la fonction CNUM().


Par exemple:

Supprimer les espaces - 5




trait bleu


Pour voir d'autres exemples et vous entrainer sur le thème de la suppression d'espaces, le fichier Excel qui accompagne cet article vous présente quelques exercices corrigés.

 

Extrait du fichier d'exercices:

Téléchargez le fichier d'exercices



Téléchargez le fichier d'exercices !

Pour toutes vos questions ou si vous rencontrez des difficultés, n'hésitez pas à rejoindre nos Forums de Discussions !

Tags Fonction   SUBSTITUE()   CNUM()   CAR()   STXT()   CODE()   SUPPRESPACE()   ASCII  

 

Autres articles dans cette catégorie Publié le Vues
Rechercher une Fonction 28-05-2013 3258
T() - Tester et renvoyer du texte 02-07-2012 11142
EPURAGE() - Supprimer certains caractères indésirables 02-06-2012 24714
REMPLACER() - Echanger une chaîne de caractères par une autre 02-06-2012 10511
CTXT() - Convertir un nombre en texte 23-05-2012 36555
REPT() - Répéter des caractères un certain nombre de fois 26-04-2012 10609
L'Assistant Somme Conditionnelle 01-11-2011 8017
CELLULE() - Nom de l'onglet, du fichier ou du répertoire dans une cellule 03-10-2010 29452
EXACT() - Comparer deux chaînes de texte 26-04-2010 46539
Purgez les espaces inutiles ! 05-04-2010 66817
SUPPRESPACE() - Supprimer les espaces inutiles dans une cellule 05-04-2010 32342
STXT() - Extraire une chaîne de caractères au sein d'une autre 13-03-2010 77957
CHERCHE() TROUVE() - Retourne la position d'une chaîne de caractères 04-01-2010 71267
NBCAR() - Compter les caractères d'une chaîne de texte ou d'une plage de cellules 21-11-2009 18394
SUBSTITUE() - Remplacer une chaîne de texte par une autre 08-11-2009 78225
CAR() - CODE() : Utilisation des codes caractères 25-10-2009 19131
CONCATENER() - Assembler plusieurs chaînes de texte en une seule. 10-10-2009 19231
TEXTE() - Convertir une valeur numérique en texte formaté 29-09-2009 58336
DROITE() - GAUCHE() : Extraire les premiers ou derniers caractères d'une chaîne. 12-09-2009 94931
MAJUSCULE() - MINUSCULE() - NOMPROPRE(): Modifier la casse d'un texte 23-08-2009 6584
CNUM() - Convertir une chaîne en valeur numérique 19-08-2009 32204
DATEDIF() : Calculer la différence entre deux dates 26-07-2009 65165
Nommer une Plage Dynamique 06-12-2008 45408
Nommer une Plage de Cellules 05-12-2008 60038
Références Relatives & Absolues 05-12-2008 13567
Formule pour Nombres Premiers 29-08-2007 12395