Comment utiliser la fonction Trim en VBA dans Excel

Sa colonne Fournisseur sort d’un export mal tenu, avec des espaces devant, derrière, en double au milieu, et un espace insécable ramené du web. La colonne Longueur compte les caractères de chaque nom avec NBCAR, et c’est elle qui bouge dès qu’une macro passe.

Aucun de ces défauts ne se voit, ni dans Excel ni dans la grille ci-dessus, parce qu’un espace n’a pas de dessin. Seule la colonne Longueur les trahit. « Aubert Métaux » ne fait que 13 caractères une fois écrit proprement, contre les 15 qu’annonce la ligne 2, et « Ferrand Plastiques » en fait 18 contre 24.

Supprimer les espaces du début et de la fin avec Trim

Trim reçoit une chaîne, en retire les espaces qui la précèdent et ceux qui la suivent, puis renvoie le texte nettoyé. Elle ne modifie pas ce qu’on lui passe, donc son résultat se range dans une variable, ou dans la cellule d’où il vient.

Nettoyer la valeur d’une cellule

Sur une feuille, la même ligne relit la cellule, la nettoie et la réécrit par-dessus, sans colonne d’aide ni collage en valeurs. C’est la forme la plus courte, et celle qu’on répète ensuite sur toute une colonne.

Une réserve accompagne pourtant cette écriture, parce que réécrire une cellule lui fait perdre ce qu’elle avait de particulier. Un code saisi en texte comme 00123 ressort en nombre 123, ses zéros de tête envolés, puisqu’Excel relit la chaîne réécrite et la prend pour un nombre. Une cellule vide, elle, ne risque rien et reste vide.

Couper d’un seul côté avec LTrim et RTrim

VBA propose deux variantes qui ne travaillent que d’un bord, et leur initiale dit lequel. LTrim coupe à gauche, c’est-à-dire au début de la chaîne, RTrim coupe à droite, et Trim fait simplement les deux d’un coup.

Voir les espaces restants dans la fenêtre Exécution

Les crochets collés de part et d’autre du texte ne servent qu’à l’œil, puisqu’un espace en fin de ligne ne se distingue de rien du tout. Debug.Print écrit son résultat dans la fenêtre Exécution de l’éditeur, qu’on ouvre par Ctrl+G, et c’est là qu’on vérifie une chaîne avant de la renvoyer dans la feuille.

Le choix entre les trois se règle vite. Un nom, une référence ou un code destiné à être comparé passe toujours par Trim, alors que LTrim et RTrim servent quand un seul bord doit bouger, par exemple pour garder l’indentation d’un texte présenté sur plusieurs lignes.

Comprendre pourquoi Trim ne touche pas aux espaces du milieu

Trim ne regarde que les extrémités de la chaîne et s’arrête au premier caractère qui n’est pas un espace. Le double espace planté entre Vernon et Papeterie, en ligne 4 du classeur, y survit donc intact, et la cellule reste introuvable pour une RECHERCHEV. C’est toute la différence avec SUPPRESPACE, la fonction de feuille, qui ramène en plus chaque répétition à un seul espace.

Réduire aussi les espaces doublés avec WorksheetFunction.Trim

Les fonctions de feuille restent accessibles depuis VBA par l’objet WorksheetFunction, et c’est par là que passe le nettoyage complet. WorksheetFunction.Trim appelle la fonction de la feuille, donc SUPPRESPACE dans un Excel français, avec le même résultat que la formule.

L’écriture raccourcie Application.Trim(…) donne exactement le même texte, et les deux se valent tant qu’on nettoie une chaîne. La forme longue reste préférable par habitude, car elle signale à la lecture que l’appel sort du langage VBA pour emprunter une fonction de la feuille.

Traiter l’espace insécable que Trim laisse passer

Trim ne connaît qu’un seul caractère, l’espace ordinaire de la barre d’espace, que VBA écrit Chr(32). L’espace insécable, Chr(160), lui ressemble trait pour trait à l’écran mais lui résiste complètement, si bien qu’une cellule collée depuis une page web reste sale après son passage. La parade tient en un Replace posé avant le nettoyage, déjà écrit dans l’article sur les fonctions pour supprimer un espace dans Excel, et la suite de cette section porte sur ce qu’il ne règle pas.

WorksheetFunction.Trim ne fait pas mieux sur ce point, puisque SUPPRESPACE ignore elle aussi le caractère 160, ce qu’aucun des deux appels n’annonce. Dans le classeur d’exemple, « Barral Textile » tombe bien de 16 à 14 caractères sous l’une comme sous l’autre, la longueur exacte d’un nom propre, alors que l’espace du milieu reste un insécable et qu’une comparaison avec le même nom tapé à la main échoue toujours. La longueur cesse alors de trahir le défaut, et c’est le cas le plus déroutant de tous.

D’autres caractères invisibles se comportent de la même façon. La tabulation Chr(9), le saut de ligne Chr(10) et l’espace de largeur nulle ChrW(8203) traversent Trim sans bouger, et une chaîne qui en porte reste longue d’un caractère de trop. La page sur les espaces invisibles d’Excel les passe tous en revue, avec une macro qui les retire d’un seul geste, et la fiche du nettoyage de texte explique comment les repérer à la formule.

Choisir entre Trim et Trim$

Les deux écritures renvoient le même texte, et leur différence porte sur le type de ce qu’elles rendent. Trim$ renvoie directement une chaîne, alors que Trim renvoie un Variant. Cette souplesse lui permet de recevoir la valeur Null et de la laisser passer telle quelle, là où Trim$ s’arrête sur l’erreur 94.

La règle pratique suit l’origine du texte. Un contenu lu dans une base de données ou dans un enregistrement peut valoir Null, et Trim l’absorbe sans arrêter la macro, quand une variable déjà déclarée As String ne risque rien et gagne à passer par Trim$, plus direct. Les cellules d’une feuille, elles, ne rendent jamais Null, donc les deux conviennent.

Nettoyer toute une colonne avec Trim dans une boucle

Trim ne prend qu’une chaîne à la fois, et lui passer une plage entière déclenche l’erreur 13, y compris par WorksheetFunction. Une colonne se traite donc cellule par cellule, dans une boucle For Each, et l’article sur les fonctions pour supprimer un espace dans Excel en donne déjà une, prête à coller. Celle qui suit en change deux détails, parce qu’elle appelle WorksheetFunction.Trim plutôt que Trim, et ne réécrit une cellule que si son texte a bougé.

Collée dans un module du classeur d’exemple, elle se lance sur la sélection A2:A9 et n’affiche rien à l’écran. C’est la colonne Longueur qui montre son travail, puisque deux espaces de trop ne se voient pas dans la cellule. Aubert Métaux passe de 15 caractères à 13, Ferrand Plastiques de 24 à 18, Barral Textile de 16 à 14, alors que Hérault Outillage, déjà propre, garde ses 17.

Trois précautions valent pour toute macro de ce genre. Le test VarType(cellule.Value) = vbString laisse de côté les nombres, les dates et les formules, qui n’ont rien à faire dans un nettoyage de texte, et la comparaison finale évite de réécrire une cellule qui n’a pas bougé, ce qui accélère la boucle sur un gros export. La dernière tient au retour en arrière, car Ctrl+Z ne rend pas les valeurs d’origine après une macro, donc mieux vaut travailler sur une copie de la feuille.

Le piège des zéros de tête, lui, s’applique toujours, puisque la macro réécrit bel et bien chaque cellule. Une colonne de codes ou de numéros de compte se confie plutôt à la macro de la page sur les espaces invisibles, qui réécrit chaque texte en texte et lui garde ses zéros.

Le collage du code dans un module, et la marque qui empêche Excel d’exécuter la moindre macro d’un fichier téléchargé, sont décrits dans l’article sur l’installation d’une macro dans Excel. Le classeur d’exemple porte cette marque dès son téléchargement, et elle le suit d’un dossier à l’autre, puisqu’elle tient au fichier lui-même. Coche donc la case Débloquer dans ses Propriétés avant de coller quoi que ce soit.

Enlever d’autres caractères qu’un espace au bout d’une chaîne

Trim ne sait retirer que des espaces, et aucun argument ne lui dit de couper autre chose, contrairement au TRIM de SQL qui reçoit le caractère à rogner. Pour des dièses, des points ou des tirets collés aux extrémités d’une référence, la coupe s’écrit donc à la main, avec Left, Right, Mid et Len.

Un détail surprend à la première saisie. Excel écrit un arobase devant le nom, =@Couper_caractere(B2;"#"), alors que tu as tapé la formule sans lui. Il le pose sur les fonctions personnalisées dont un argument pourrait recevoir une plage, pour garder le comportement des anciennes versions, et le résultat ne change pas. Laisse-le où il est.

Écrite en Function plutôt qu’en Sub dans un module standard, elle devient une fonction personnalisée qu’une cellule appelle comme n’importe quelle autre. Sur la colonne Référence du classeur d’exemple, elle tire « BC-4021 » de « ##BC-4021## », et rend telle quelle une référence qui n’a jamais porté de dièse.

Le second argument permet de viser n’importe quel caractère sans toucher au code, un point, un tiret ou une apostrophe selon l’export. Quand le caractère à retirer se trouve au milieu plutôt qu’aux extrémités, Replace suffit, puisqu’elle remplace toutes ses occurrences d’un coup.

Tout cela suppose pourtant qu’une macro soit la bonne réponse, ce qui n’est pas toujours le cas. Rechercher et remplacer, le remplissage instantané et Power Query nettoient les mêmes colonnes sans une ligne de code, et ils valent mieux dès que la corvée ne revient qu’une fois.