Monsieur Excel
Pour tout savoir faire sur Excel !

10 septembre 2012

Récupération de nombres US

On récupère parfois des listes de nombres écrits à l’américaine, c’est-à-dire avec le point comme séparateur des milliers et la virgule comme marqueur de décimale. Du coup, ces nombres sont compris par Excel comme étant du texte, ce qui fait qu'il s'affichent à gauche de la cellule.

Le problème est alors de récupérer les valeurs de façon qu'elles soient utilisables par Excel :
En fait, ce but peut être atteint à l’aide d’une formule unique Voici la formule que nous avons entrée en B2 et recopiée jusqu’en B4 : =1*substitue(substitue(A2;",";"");".";",")

Cette formule élimine les virgules puis remplace le point éventuel par une virgule. La multiplication par « 1 » transforme le texte ainsi obtenu en valeur, car il est plus simple de faire ce produit par 1 que d’utiliser la fonction Cnum().

Remarque 1 – Notre formule ne marcherait pas dans le cas où il y aurait des
« $ », comme en A5. Pour résoudre ce problème, nous avons créé la formule suivante, qui peut d’ailleurs remplacer sans problème la formule précédente, même quand il n’y a pas de « $ » :
=1*substitue(substitue(substitue(A5;"$";"");",";"");".";",")

Remarque 2 – Les formats numériques des cellules B2:B5 ne sont pas apparus tout seuls : il revient à vous à les définir…

Remarque 3 – Cet article est une actualisation d’un article publié le 24 avril 2008. Nous suivons aujourd'hui la même logique qu'il y a 4 ans...

4 Commentaire(s):

  • Bonjour,
    merci pour cette application de la fonction substitue.

    Nous pourrions également utiliser la fonctionnalité CONVERTIR (menu ou onglet Données): à la 3° étape de l'assistant, le bouton "Avancé..." fait apparaître les paramètres avancés d'importation du texte avec le choix des séparateurs de décimale et de milliers.

    Je n'ose plus me rappeler depuis quand cette fonctionnalité existe avec Excel.

    Merci pour vos articles et astuces,
    Cordialement

    By Anonymous ld42, sur 10:03 PM  

  • Malheureusement, votre formule est tributaire du séparateur décimal choisi par l'utilisateur sur son poste de travail. S'il a choisi le point, la formule ne marche plus...
    Je propose la formule suivante :

    =0,01*SUBSTITUE(SUBSTITUE(A1;",";"");".";"")

    By Anonymous cduigou, sur 8:40 PM  

  • Mille excuses, ma formule ne marche que s'il y a un . décimal dans l'original...

    By Anonymous cduigou, sur 8:43 PM  

  • J'utilise personnellement la fonction Rechercher 'point' / Remplacer par 'virgule' (ou par 'vide' selon les besoins).

    Et je multiplie ensuite la valeur par 1 pour transformer le texte en nombre.

    Un grand merci pour vos articles!

    By Anonymous Kallyste, sur 5:31 PM  

Enregistrer un commentaire

<< Accueil