Nettoyer les espaces invisibles d’un export Excel avec une macro

Écrire la formule SUPPRESPACE dans une colonne voisine

La formule SUPPRESPACE s’écrit dans une colonne vide à côté des données, en F2, puis se recopie jusqu’en F7, parce qu’une cellule ne peut pas se nettoyer elle-même. Écrite dans A2, =SUPPRESPACE(A2) ferait en effet référence à sa propre cellule. Dans la colonne F, elle rend alors « Martin Dupont » et « Sophie Bernard » sans leurs espaces de trop, mais laisse en place les espaces insécables des lignes 4 à 6.

Coller le texte nettoyé en valeurs sur la colonne d’origine

Pour remettre les noms nettoyés à leur place, colle-les en valeurs sur la colonne A. Le collage spécial en valeurs recopie en effet le texte que la formule affiche, sans la formule elle-même, si bien que la colonne d’aide n’a plus de raison d’être. Un collage ordinaire serait ici un piège, car les formules collées sur la colonne A, après Ctrl+C comme après Ctrl+X, deviennent =SUPPRESPACE(#REF!) et n’affichent plus que #REF!.

1. 01

   Sélectionne les formules de F2 à F7, puis copie-les avec Ctrl+C.

2. 02

   Fais un clic droit sur A2, choisis Collage spécial, coche Valeurs dans la fenêtre qui s’ouvre, puis valide par OK.

   

3. 03

   Supprime la colonne F, dont les formules ne servent plus.

Enlever chaque espace d’un texte avec SUBSTITUE

Pour enlever chaque espace d’un texte, SUBSTITUE le remplace par un texte vide, et comme elle traite toutes les occurrences, l’espace disparaît partout où il se trouve. Sur un nom, la formule colle pourtant le prénom au nom, comme sur la ligne de Sophie Bernard, ce qui la réserve aux codes et aux numéros.

Remplacer un espace par rien avec Ctrl+H

Avec Ctrl+H, qui ouvre la fenêtre Rechercher et remplacer, il suffit de remplacer un espace par rien pour retirer d’un seul geste tous les espaces ordinaires de la sélection. Le résultat est celui de SUBSTITUE, mais obtenu sur place, puisque la fenêtre réécrit chaque cellule sélectionnée qui contient le texte cherché. Tu n’as donc ni colonne d’aide ni collage à faire, mais les espaces insécables restent, et la section suivante les traite.

1. 01

   Sélectionne les cellules à nettoyer, par exemple la colonne A, pour que le remplacement ne touche pas au reste de la feuille.

2. 02

   Appuie sur Ctrl+H pour ouvrir la fenêtre Rechercher et remplacer sur son onglet Remplacer.

   

3. 03

   Tape un espace dans le champ Rechercher, laisse le champ Remplacer par vide, puis clique sur Remplacer tout.

Si tu veux seulement réduire les espaces doublés, sans coller les mots, tape plutôt deux espaces dans Rechercher et un seul dans Remplacer par. Clique ensuite sur Remplacer tout autant de fois qu’il le faut, soit deux fois pour les trois espaces qui séparent « Sophie » de « Bernard ». Un espace isolé en début ou en fin de cellule reste pourtant en place, et c’est là que SUPPRESPACE garde l’avantage.

Repérer un espace invisible avec NBCAR et UNICODE

Un espace invisible se repère en deux temps, en le comptant d’abord avec NBCAR, puis en demandant son numéro à UNICODE. Pour « Emma Leroy », qui n’affiche que 10 caractères, NBCAR rend 11. Le caractère en trop est ici le dernier, que DROITE isole pour le passer à UNICODE.

Préfère UNICODE à CODE, qui rend bien 160 pour l’espace insécable, mais pas le numéro des deux autres espaces invisibles de l’exemple. Pour l’insécable étroit du montant de E2 comme pour l’espace de largeur nulle qui suit « Hugo Garnier », CODE rend en effet 63, le code du point d’interrogation. UNICODE donne au contraire leurs vrais numéros, 8239 et 8203, que la suite de cette section emploie.

Changer CAR(160) en espace ordinaire avant SUPPRESPACE

La formule sûre commence par changer chaque espace insécable en espace ordinaire, avec SUBSTITUE et la fonction CAR, qui désigne l’insécable par CAR(160). SUPPRESPACE n’a plus ensuite qu’à retirer les espaces en trop, comme celui qui suit « Emma Leroy ». Dans cet ordre, « Lucas Roux » garde l’espace qui sépare son prénom de son nom.

Méfie-toi de l’ordre inverse, =SUBSTITUE(SUPPRESPACE(A4);CAR(160);""), qui supprime l’espace insécable au lieu de le remplacer et rend donc « LucasRoux », les deux mots collés. Cet ordre ne convient qu’à un espace insécable placé au début ou à la fin du texte, et devient un piège sur une colonne de noms.

Nettoyer l’insécable étroit et l’espace de largeur nulle avec UNICAR

Pour l’insécable étroit et l’espace de largeur nulle, il faut UNICAR, qui les désigne par leur numéro, puisque la fonction CAR s’arrête à 255. Ces deux caractères invisibles résistent en effet à SUPPRESPACE, même précédée de SUBSTITUE et de CAR(160). Le premier, le caractère 8239, sépare les milliers du montant de E2, et le second, le caractère 8203, suit « Hugo Garnier » sans prendre aucune place à l’écran. Trois SUBSTITUE imbriqués traitent alors les trois espaces invisibles d’un coup, avant que SUPPRESPACE ne retire ce qui reste en trop.

Taper l’espace insécable avec Alt+0160 dans Ctrl+H

Pour traiter l’espace insécable sans formule, tape-le dans le champ Rechercher de la fenêtre que Ctrl+H ouvre, où il ressemble à un espace ordinaire. D’après l’aide de Microsoft, un caractère se tape en maintenant la touche Alt pendant qu’on tape son code sur le pavé numérique, 0160 pour l’espace insécable. Remplace-le ensuite par un espace, puis passe SUPPRESPACE, car le remplacement laisse un espace ordinaire devant « Nantes ». L’insécable étroit et l’espace de largeur nulle se traitent en revanche plus sûrement avec SUBSTITUE et UNICAR, qui les désignent par leur numéro sans qu’il faille les taper.

Retirer les sauts de ligne d’une cellule sans coller les mots

Pour retirer un saut de ligne sans coller les mots, change-le d’abord en espace avec SUBSTITUE et CAR(10), puis laisse SUPPRESPACE finir le travail. EPURAGE semble pourtant faite pour ça, puisqu’elle retire les caractères de contrôle, numérotés de 0 à 31 d’après l’aide de Microsoft. Le saut de ligne et la tabulation en font partie, mais elle les supprime sans rien mettre à leur place. « Bordeaux » et « Cedex », écrits sur deux lignes en B7 avec Alt+Entrée, deviennent ainsi « BordeauxCedex ».

Décocher le séparateur de milliers du format de cellule

Sur un vrai nombre, l’espace des milliers disparaît dès qu’on décoche une seule case, alors qu’aucune formule ne peut le retirer. Il ne vient en effet que du format de la cellule, et ne fait pas partie de sa valeur. Sélectionne les cellules, ouvre le format de cellule avec Ctrl+1, puis, dans la catégorie Nombre de l’onglet Nombre, décoche la case Utiliser le séparateur de milliers.

Convertir avec CNUM un montant collé en texte

Un montant resté en texte, que SOMME laisse de côté, se convertit avec CNUM, à condition d’en retirer d’abord les espaces insécables. Tant que l’insécable étroit sépare ses milliers, =CNUM(E2) renvoie en effet #VALEUR!, alors qu’un espace ordinaire ne gêne pas la fonction. CNUM lit ainsi le texte « 1 250,00 » comme le nombre 1 250.

Colle ensuite ces résultats en valeurs sur la colonne d’origine, comme tu l’as fait pour SUPPRESPACE. Après la macro de la section suivante, qui change les insécables en espaces ordinaires, =CNUM(E2) suffit à lui seul.

Installer la macro dans un module neuf

Ne nettoyer que les cellules texte, sans toucher aux formules

• Elle ne réécrit que les cellules texte qui ont besoin d’un nettoyage. Les formules, les nombres, les dates et les textes déjà propres restent tels quels, et relancée sur une sélection propre, elle annonce 0 cellule nettoyée.

• Le résultat reste un texte. Elle le réécrit précédé de l’apostrophe qui force le texte à la saisie, invisible dans la cellule, si bien que 01000 garde son zéro, que 01/02 ne devient pas une date et que +33 6 12 34 56 78 ne devient pas une formule.

• Un montant collé en texte reste donc un texte, mais sans ses espaces insécables, et =CNUM(E2) le convertit ensuite en 1 250.

• Une cellule qui ne contenait que des espaces devient vide.

• Elle coupe les événements le temps du nettoyage, si bien qu’une macro qui réagit aux changements de la feuille ne se déclenche pas à chaque cellule.

• Elle annonce à la fin le nombre de cellules nettoyées. Sur une feuille protégée, elle s’arrête à la première cellule verrouillée, et son message en donne la raison.

• Ctrl+Z ne rend pas le texte d’origine. Travaille sur une copie de la feuille si tu veux pouvoir revenir en arrière.

Connaître les limites de la macro avant de nettoyer un export

• Elle ne remplace que les trois espaces invisibles de cette page, 160, 8239 et 8203, en plus des caractères de contrôle qu’EPURAGE retire. Un autre caractère invisible, comme l’espace fine, reste dans la cellule.

• Un saut de ligne voulu disparaît aussi, et une adresse écrite sur deux lignes avec Alt+Entrée se retrouve sur une seule.

• Une mise en forme posée sur une partie du texte ne survit pas à la réécriture, et un texte dont seul le premier mot est en gras devient gras en entier.

• Une formule dont le résultat porte des espaces en trop n’est pas touchée, puisque c’est la formule elle-même qu’il faudrait corriger.

• Elle ne convertit aucun texte en nombre. Les montants et les codes collés en texte le restent, et CNUM se charge ensuite des montants.