On se confronte régulièrement au problème de suppression des espaces inutiles dans les cellules de feuille de calcul.
Nous allons aborder les points suivants :
- Supprimer les espaces inutiles en conservant les séparateurs de mots
- Supprimer tous les espaces
- Supprimer un seul espace
- Supprimer un espace dit « caractère spécial »
- Supprimer tous les caractères espace d'une zone par le menu « Remplacer »
- Particularité de format : les espaces peuvent correspondre à un format et non pas à des caractères « espace » réels.
• 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.
• 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():
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 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é.
Astuce:
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). 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 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:
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.
A noter:
Par exemple:
Extrait du fichier d'exercices:
