Il m’est régulièrement arrivé d’importer des données depuis un autre système et récemment depuis SAP mais les montants affichés dans Excel n’étaient pas au bon format. Du coup il m’était impossible de modéliser à partie des ces valeurs.
Et un bon nombre de collègues consultants ont rencontré ce problème.
Ce que tout le monde fait naturellement est tout d’abord changer le format de cellule (CTRL+MAJ+1) en sélectionnant Nombre par exemple (ou monétaire selon le besoin).
On remarque d’ailleurs que le format initial est positionné à Standard. Remarquons également que lorsque le séparateur de milliers est sélectionné avec un nombre de décimales à 2 également, l’exemple reste inchangé. Sur ce snapshot, on voit que la valeur 1903 est affichée (zone exemple) alors qu’on souhaiterait plutôt 1 903,00.
La deuxième solution, qui fonctionne dans certains cas est de sélectionner complètement la colonne et de cliquer sur le bouton
et ensuite de sélectionner:
Mais cela ne fonctionne pas toujours, alors comment faire ?
Je vous propose deux solutions, une astuce EXCEL et une autre VBA pour ceux qui souhaitent importer des données à travers un petit programme VBA (comme l’outil d’extraction SAP que j’ai mis à disposition dans la
page téléchargement).
Directement dans EXCEL
L’astuce consiste à sélectionner l’ensemble de la colonne concernée (uniquement les valeurs, c’est à dire, on ne sélectionne pas l’entête).
- On fait copier (CTRL+C ou bouton droit de la souris puis Copier).
- Se positionner sur une autre colonne vierge et non adjacente à une colonne ayant des valeurs. On pourra par exemple, se positionner sur une nouvelle feuille.
- Faire ensuite un collage spécial, en sélectionnant Valeur et Addition:
4. Ensuite on sélectionne toutes les nouvelles valeurs (qui sont au format nombre), on fait Copier, puis Coller dans la zone initiale (là où les valeurs étaient au mauvais format).
A présent, vous avez des valeurs au format nombre. Il suffit de modifier le format cellule (CTRL+MAJ+1) en mettant ce que vous souhaitez (Monétaire, séparateur de milliers, …)
Par un code VBA
Il peut arriver que vous ayez besoin de faire la même manipulation à travers un code VBA. J’ai essayé avec l’enregistreur de macros mais le résultat attendu n’est pas atteint.
Voici donc quelques fonctions qui permettent de mettre au format souhaité:
Si vous avez des « . » à la place des virgules (importation de données anglo-saxones):
Valeur_Format_Francais = Replace(Valeur_Origine, « . », « »)
Si vous avez des espaces (séparateurs de milliers dans la valeur d’origine):
Valeur_New = Replace(Valeur_Origine, » « , « »)
Pour avoir une valeur au format Entier Long (valeurs pouvant être inférieures à -32 768 et supérieures à 32 767): Valeur_Entier_Long = CLng(Valeur_Origine)
Pour avoir une date au format Double, c’est à dire avec des décimales:
Valeur_Double = CDbl(Valeur_Origine)
Enfin, en bonus pour la gestion des dates, si vous souhaitez avoir un format Date:
Valeur_Date = CDate(DateJour)
avec DateJour au format « JJ/MM/AAAA »
Cela signifie que si par exemple vous avez le format suivant en entrée « AAAAMMJJ », il faudra initialiser votre variable DateJour de cette façon:
DateJour = Right(Valeur_Origine, 2) & « / » & Mid(Valeur_Origine, 5, 2) & « / » & Left(Valeur_Origine, 4)
J’espère que cet article pourra aider certains car pour ma part, il m’a fallu chercher avant de trouver les solutions à ce problème. Et si vous avez d’autres solutions, je suis preneur !!!
et ensuite de sélectionner:
Wow merci, le "Faire ensuite un collage spécial, en sélectionnant Valeur et Addition:" il fallait le trouver !!
Pingback: Des caractères spéciaux qui peuvent polluer – Cooking-Excel