dimanche 1 juillet 2012

Convertir un nombre en texte

Le billet de dimanche dernier portait sur les manipulations d’Excel autour du mode Edition : entrer&sortir d’une cellule.

C’était l’occasion d’utiliser l’instruction SendKeys, qui simule une action utilisateur au clavier. Au-delà de cette opération entrer&sortir, l’un des premiers usages que j’avais fait de cette instruction avait été de bricoler une macro qui me permette de convertir facilement un nombre en texte si ce nombre avait été malencontreusement entré comme valeur numérique.

Par exemple, si j’avais un listing d’adresses dont l’une des colonnes était le code postal, il arrivait que ces codes postaux aient un format numérique - le format par défaut pour des nombres. Bien sûr dans beaucoup de cas on pourrait travailler avec les valeurs numériques de ces codes postaux ; mais ça pourrait poser problème dans certaines situations.

Illustration avec les codes postaux qui commencent par 01 (Ain), 02 (Aisne), 03 (Allier) … 09 (Ariège). Si on laisse Excel les traiter comme des nombres, le premier 0 va être coupé et les codes postaux seront donc transformés en valeurs commençant par 1, 2, 3... 9. Et si on veut - exemple au hasard - en extraire le code du département, ça ne marche pas de prendre les deux chiffres à gauche du code postal !



Le plus sûr est de convertir ces valeurs-là en texte, ce qui se fait par deux manipulations successives :
1. donner aux cellules le format Texte,
2. revalider (ou ressaisir) le contenu de la cellule.
On l’a vu dans le billet précédent : ce second point revient à entrer & sortir de la cellule.

Au passage, je signale l’acrobatie de correction qu’il faut aussi penser à faire dans le cas des codes postaux : corriger spécifiquement ceux auxquels un format numérique n’a laissé que 4 caractères au lieu de 5, pour y replacer un 0 au début.

Tout cela pour dire ceci : la façon dont je faisais auparavant cette conversion consistait à faire faire le changement de format par le code VBA, c’est à dire à effectuer un léger ajout à la macro (celle qu’on a vue dimanche dernier) en la faisant débuter par la ligne suivante :
    Selection.NumberFormat = "@"

L’ennui de cette façon de faire est que l’alternance F2-Entrée est assez chronophage. En fait, c’est précisément l’instruction SendKeys qui prend du temps. Si je travaillais sur une colonne de codes postaux de plusieurs dizaines de milliers de lignes, l’opération pouvait prendre des dizaines de secondes.


Comment aller plus vite ?

On va voir ici deux façons de faire cette conversion plus rapidement.


La première façon est d’utiliser les commandes de conversion qu’on lance par Données / Convertir (ce n’est pas le même habillage jusqu’à Excel 2003 et à partir d’Excel 2007, mais le chemin est le même et les commandes aussi !)

Concrètement, on sélectionne la plage de cellules à convertir (sur une seule colonne, impérativement), on lance Données / Convertir et, à l’étape “3 sur 3”, on choisit l’option Texte. La conversion est à peu près instantanée. (Les étapes 1et 2 ne sont pas déterminantes : à la première on peut choisir indifféremment “Délimité” ou “Largeur fixe”, et à la seconde n’importe quel type de séparateur puisque, de toute manière, on ne travaille qu’avec une seule colonne !)

Sous Excel 2003, ça ressemble à ceci :

Sous Excel 2007, ça ressemble à cela :


Qu’est-ce que ce serait en VBA ? Utiliser l’enregistreur de macro en effectuant ce qui précède va générer plusieurs lignes de code. Seule celle contenant la méthode TextToColum est nécessaire. Qui plus est, elle est affublée d’un grand nombre de paramètres mais deux seulement seront nécessaires ici, tous les autres étant dispensables. C’est ceci :
Selection.TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, 2)

Le paramètre DataType permet de préciser le type de source sur lequel on travaille(c’est le choix entre “délimité” et “largeur fixe” qu’on effectue à l’étape “1 sur 3”). Le paramètre FieldInfo permet de spécifier le format-cible : la valeur 1 dit “c’est le premier item”, et la valeur 2 dit “on veut passer ça en format Texte”.

Bref, on peut faire une macro de trois lignes seulement, qui convertit en texte une sélection de nombres entrés en valeurs numériques. C’est tellement chouette que je ne résiste pas au plaisir d’écrire ici la macro en entier :
Sub Conversion_Rapide_Nombre_En_Texte()  
Selection.TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, 2)
End Sub

Cette façon de faire est extrêmement rapide. Cependant, on peut l’appliquer à une seule colonne à la fois, et sur une unique plage de cellules à la fois. Si on a un grand listing présentant de nombreuses colonnes de nombres ou si on doit faire ce travail de conversion sur plusieurs plages disjointes, on ne peut pas le faire d’un seul coup.


La seconde façon va le permettre. Il n’y a pas de commande Excel pour faire ça : on va donc travailler exclusivement en VBA. Gardons à l’esprit qu’on veut quelque chose qui aille vite, donc pas avec le SendKeys...

À l’utilisation, ce sera la mêrme chose : il faudra sélectionner les cellules à convertir puis lancer la macro.

L’idée de la macro est de faire une boucle qui parcourt une à une chaque cellule (c) de la plage sélectionnée. Que convient-il de faire sur chaque cellule ? Deux choses.

D’abord, passer la cellule au format “Texte”. Ca se fait comme ça :
    c.NumberFormat = "@"
Si la cellule est déjà au format texte, ce n’est pas grave, l’effet sera nul.

Ensuite – et c’est là l’astuce de la macro – entrer dans la cellule la valeur qu’elle comporte mais passée par la fonction Format ! Comme ceci :
c = Format(c.Value)
Que fait cette fonction ? On l’utilise généralement pour travailler sur des dates ou des heure, notamment pour régler la manière dont s’affichent ces données (Elle peut prend pour paramètre une information précisant ). Ici, utilisée sans argument autre que la valeur qu’on lui passe, elle renvoie cette valeur sous la forme d’une chaîne de caractères (String) : il n’y a plus de format numérique.

C’est tout !

En imbriquant cela dans une boucle, et en entourant l’ensemble des commandes qui gèrent le rafraichissement écran, ça donne ça :

Sub ConversionNombreEnTexte()

Application.ScreenUpdating = False

Dim c As Range
For Each c In Selection
c.NumberFormat = "@"
c = Format(c.Value)
Next c

Application.ScreenUpdating = True

End Sub


Une fois à portée de clic, cette macro permet de convertir des nombres en texte, même sur des milliers de cellules, en une fraction de seconde !

1 commentaire: