Excel 2003 - Fonctions de transformations Fermé

Question

Bonjour,

Je prends des données venant d Internet et j aimerais pouvoir faire des calculs sous Excel 2003 pour se faire j aimerais savoir s il existe des fonctions pour arriver à un résultat voulu en partant d un cas précis.

1er cas
A1 = "33 livres d histoire", en B1, j aimerais le résultat "33"

2ème cas
A1 = "8 795 769", en B1, j aimerais le résultat "8795769"

3ème cas
A1 = "1 150 506 millimètres", en B1, j aimerais le résultat "1150506"

4ème cas
A1 = "77.8318 cm" en B1, j aimerais le résultat "77,8318"

5ème cas
A1 = "0.5" en B1, j aimerais le résultat "0,5"

MERCI

Utilisateur anonyme · Answer

=GAUCHE(A1;2)

Mike-31 · Answer

Salut,

Pour ces valeurs il va falloir procéder par étapes avec la fonction convertir
mettre la cellule contenant "33 livres d histoire" et Données/Convertir cocher largeur fixe/Suivant tu remarqueras ton texte séparé par des flèches tournées vers le haut supprimes celles qui sont en trop en un clic gauche et glissé hors de la zone et Terminer.

recommencer avec les valeurs qui différentes comme 1 150 506 millimètres

Pour supprimer des intervalles de ces valeurs 
8795769
1150506
mettre les cellules en surbrillance et Format/Cellule/onglet Nombre/sélectionner Standard

si tes valeurs 77.8318 et 0.5 sont en A4 et A5 par exemple en C4 saisis cette formule 
=SUBSTITUE(A4;".";",")*1
et incrémentes là vers C5 elle va changer le point par une virgule et mettre la valeur en numérique pour obtenir 77,8318 et 0,5

A+
Mike-31

Un problème sans solution est un problème mal posé  (Einstein)

Le Pingou · Answer

Bonjour,
Les voici dans l'ordre :
cas 1 : =GAUCHE(A1;2) cas 2 : =SUBSTITUE(A1;" ";"")   cas 3 : =SUBSTITUE(GAUCHE(A1;9);" ";"")  
cas 4 : =SUBSTITUE(GAUCHE(A1;7);".";",")  cas 5 : =SUBSTITUE(A1;".";",")
En consultant un minimum l'aide d'Excel on tombe presque dessus !

eriiic · Answer

Bonsoir tout le monde,

la compil' :
=SUBSTITUE(SI(MAX(CODE(STXT(A3;LIGNE(INDIRECT("$1:$"&NBCAR(A3)));1)))>57;GAUCHE(A3;EQUIV(VRAI;CODE(STXT(A3;LIGNE($1:$20);1))>57;0)-1);A3);".";",")*1

formule matricielle à valider par shift+ctrl+entrée
(doit s'entourer de { } si bien fait)

eric

gbinforme · Answer

Bonsoir tout le monde, 

Belle formule Éric, il ne te manque que l'espace insécable à gérer et qui est assez souvent utilisé : dernier défi ? :-)

Raymond PENTIER · Answer

Bravo à tous,
et spécialement à Eric, qui avait un peu disparu, on dirait ?

eriiic · Answer

Bonjour et merci :-)

Ah ! je l'avais oublié cet espace insécable.
Mais vu que j'arrive aux 7 fonctions max je suis un peu embété... ;-)
Bah, on dira que c'est que pour excel 2007 et que ça devrait être 
=SUBSTITUE(SUBSTITUE(SI(MAX(CODE(STXT(A3;LIGNE(INDIRECT("$1:$"&NBCAR(A3)));1)))>57;GAUCHE(A3;EQUIV(VRAI;CODE(STXT(A3;LIGNE($1:$20);1))>57;0)-1);A3);".";",");car(160);"")*1
C'est vrai que le temps me manque un peu en ce moment mais tout va bien :-)

Bonne journée à tous
eric

eagle-of-gold · Answer

Bonjour,

D'abord, je tenais à tous vous remercier pour votre aide. Je vais répondre à chacun d'entre vous séparément pour faciliter la lecture et ensuite, si j'ai d'autres questions, je les poserai.

Bonjour Mike-31,

Comme c'est des données Internet mise à jour assez souvent je veux éviter de passer par des fonctions de conversions ou des macros si c'est possible car il faut répéter ces opérations à chaque fois.

Pour être plus clair, je veux juste copier/coller les données d'Internet pour les mettre à jour et puis que tout se fasse automatiquement.

Bonjour Le Pingou,

Je te remercie pour les fonctions pour chaque cas, mais je pense que j’aurai pour certains cas des soucis. Pour éviter de répéter plusieurs fois la même chose, je mettrai les soucis possibles dans le message final.

Bonjour eriiic,

Ta première formule est compliquée et donc je ne saurai pas la transformer pour l'utiliser dans d'autres classeurs. Je vais quand même essayer de l'utiliser pour la comprendre mais j'ai des doutes (rire).

Pour ta deuxième formule si j'ai bien compris, il faut Excel 2007 que je n'ai pas.

Bonjour,

En partant des formules de Le Pingou qui sont plus simples à comprendre.

1er cas
=GAUCHE(A1;2)
Le souci est que si je tire la formule et qu’il y a des données avec 1, 2 et 3 chiffres, j’aurai sûrement un problème.
A1 = "3 livres d histoire"
A2 = "12 BD"
A3 = "133 revues"
Y a-t-il une fonction qui garde uniquement les nombres (3, 12 et 133) sans devoir préciser le nombre de caractères à garder?

3ème cas
=SUBSTITUE(GAUCHE(A1;9)
Le souci est si je tire la formule et que la partie à garder n’est pas constitué de 9 caractères, j’aurai sûrement un problème.
A1 = "1 150 506 millimètres"
A2 = "50 506 mètres"
A3= "100 506 centimètres"
Y a-t-il une fonction qui garde uniquement les nombres (1 150 506, 50 506 et 100 506) sans devoir préciser le nombre de caractères à garder puis d’enlever les espaces avec la fonction SUBSTITUE?

4ème cas
=SUBSTITUE(GAUCHE(A1;7);".";",")
J’aurai sûrement le même souci que pour le 3ème cas si je tire la formule.


Je ne sais pas si des fonctions existent pour qu’en tirant la formule qu’il n’y ait pas de souci à chaque application, mais bon, le but est de gagner le plus de temps possible.

Si ce n’est pas possible, je ferai une formule différente dans les cas où j’aurai plusieurs données différentes à gérer.

MERCI

PS: eriiic pour ta formule complexe, je verrai cela plus amplement ce week-end, j’aimerais quand même arriver à la comprendre même si je ne sais pas la transformer pour l’utiliser dans d’autres classeurs Excel 2003.

eriiic · Answer

Re,

Pour ce qui est de ma formule tu n'as normalement pas à la changer (quoique CCM rajoute parfois des caractères parasites....)
Tu la copies en B3, tu la valides par chift+ctrl+entrée, à recopier vers le bas si besoin.
En A3 tu mets une des chaines attendues et tu regardes si c'est bon.
Le cas non traité, comme l'a fait remarqué gb, est si tu as des espaces insécables.

eric

Le Pingou · Answer

Bonsoir eriiic,
Je me suis régalé pour comprendre le fonctionnement de votre superbe compil.
Une fois la formule comprise j'ai essayé pour tenir compte des espaces insécables en tenant compte de la limite d'imbrication version 2003 et j'ai simplement créé une cellule intermédiaire [B2] pour la substitution des espaces et votre formule prend cette dernière comme référence (au passage j'ai supprimé le *1 en fin de formule car j'obtenais #VALEUR! pour le nombre avec ',' . 
Donc : 
en A2 = la chaine à convertir
en B2 =SUBSTITUE(A2;" ";"")
Et en C2 =SUBSTITUE(SI(MAX(CODE(STXT(B2;LIGNE(INDIRECT("$1:$"&NBCAR(B2)));1)))>57;GAUCHE(B2;EQUIV(VRAI;CODE(STXT(B2;LIGNE($1:$20);1))>57;0)-1);B2);".";",")
A valider par Ctrl+Shift+Enter


Salutations.
Jean-Pierre

Mike-31 · Answer

Bonsoir tout le monde,

En ce qui me concerne, je me suis pris la tête pour décortiquer l’encyclopédie d’Eriiic, dommage qu’elle ne puisse pas être utilisée sur une version antérieure à 2007.
Comme un trophée, elle est à conserver à vue.
Heureusement la solution de Le Pingou est une excellente alternative, à compléter avec *1 en fin de formule pour mettre la valeur en numérique.

Je me demande ou Eriiic puise son imagination, ce doit être un extraterrestre

Merci pour cette leçon

A+
Mike-31

Un problème sans solution est un problème mal posé  (Einstein)

Le Pingou · Answer

Bonsoir Mike-31,
Attention avec l'utilisation de *1 en fin de formule pour mettre la valeur en numérique car si vous avez un nombre décimal et que vous avez une ',' et que votre système travail avec le '.' vous aurez un joli #VALEURS qui va apparaitre.

JvDo · Answer

Bonsoir à tous,

si vous avez installé Morefunc de Laurent Longre, vous pouvez utiliser les fonctions REGEX. sur expressions régulières :
=SUBSTITUE(REGEX.SUBSTITUE(A13;"[^0-9.]";"");".";",")
ceux qui maîtrisent les expressions régulières pourront faire plus court.

cordialement

Le Pingou · Answer

Bonjour JvDo,
Très intéressante cette suggestion, mais il est impossible de télécharger sur http://xcell05.free.fr/ ... !
C'est dommage !

eriiic · Answer

Bonjour jean-pierre,

vous avez un nombre décimal et que vous avez une ',' et que votre système travail avec le '.' vous aurez un joli #VALEURS qui va apparaitre. 
Il est préférable d'adapter la formule selon le paramétrage du poste.
- si le séparateur décimal est la , on remplace . par , :
=SUBSTITUE(SI(MAX(CODE(STXT(A3;LIGNE(INDIRECT("$1:$"&NBCAR(A3)));1)))>57;GAUCHE(A3;EQUIV(VRAI;CODE(STXT(A3;LIGNE($1:$20);1))>57;0)-1);A3);".";",")*1  
- si le séparateur décimal est le . on remplace , par . :
=SUBSTITUE(SI(MAX(CODE(STXT(A3;LIGNE(INDIRECT("$1:$"&NBCAR(A3)));1)))>57;GAUCHE(A3;EQUIV(VRAI;CODE(STXT(A3;LIGNE($1:$20);1))>57;0)-1);A3);",";".")*1 

eric

Le Pingou · Answer

Bonjour Eric,
Merci. 
Oui effectivement mais j'ai réagi seulement après ma réponse à Mike-31, je pense que j'avais raté une courbe ...!
Bon week-end.

Mike-31 · Answer

Salut tout le monde,

Waouh, qu'elle enrichissante discussion depuis mon décrochage hier soir, je crois que tout à été dit sur les posts précédents.

Bon wek-end à tous

A+
Mike-31

Un problème sans solution est un problème mal posé  (Einstein)

eriiic · Answer

Re tout le monde,

une ruse de sioux toute bête ;-) pour simplifier la formule et du coup pouvoir tenir compte des espaces insécables.

avec , en séparateur décimal :
=SUBSTITUE(SUBSTITUE(GAUCHE(A3;EQUIV(VRAI;CODE(STXT(A3&"a";LIGNE($1:$20);1))>57;0)-1);CAR(160);"");".";",")*1

avec . en séparateur décimal :
=SUBSTITUE(SUBSTITUE(GAUCHE(A3;EQUIV(VRAI;CODE(STXT(A3&"a";LIGNE($1:$20);1))>57;0)-1);CAR(160);"");",";".")*1

Toujours en matricielle à valider par shit+ctrl+entrée

eric

Mike-31 · Answer

Re tous,

En suivant les formules d'Eriiic, il est encore possible d'intégrer un autre SUBSTITUE et de n'avoir qu'une seule formule en matricielle. A tester dans tous les sens bien sur, mais pour moi ça roule

=SUBSTITUE(SUBSTITUE(SUBSTITUE(GAUCHE(A3;EQUIV(VRAI;CODE(STXT(A3&"a";LIGNE($1:$20);1))>57;0)-1);CAR(160);"");",";".");".";",")*1

En attente de vos remarques ou rectification

A+
Mike-31

Un problème sans solution est un problème mal posé  (Einstein)

Le Pingou · Answer

Bonjour Eric,
Merci mais chez moi sa ne marche pas avec les espaces insécables j’obtiens  #VALEURS. En cherchant j’ai pris le code ‘32’ pour CAR() au lieu de 160 et là c’est  tout bon … c’est ou la différence entre nous deux !
Petite remarque au passage pour Mike-31, la substitution des espaces est déjà prise en compte dans le la formule d’Eric ( …… CAR(160);""…..) , le troisième SUBSTITUE est pour les oiseaux !
Au passage j’ai essayé la proposition de JvDo (https://forums.commentcamarche.net/forum/affich-14112222-excel-2003-fonctions-de-transformations#13)  c’est génial, merci.
A une prochaine.

eriiic · Answer

Re,

l'espace insécable (160, mais il me semblait avoir trouvé 130 aussi récemment) est différent l'espace 'normal' (32), visuellement c'est un espace mais il interdit le retour à la ligne à cet endroit (comme si les 2 mots n'en formait qu'un seul).
Aucune raison d'avoir #valeur avec l'un et pas avec l'autre, substitue() ne remplace que s'il trouve le caractère.

Mike, ton substitue() en plus ne sert à rien en effet, tu remplaces , par . et dans la foulée . par ,

eric

Mike-31 · Answer

Re Eriiic,

Comme un grand, lorsque j'ai vu tes deux formules concernant le traitement des séparateurs de décimales post 18, j’ai ajouté un substitue pour que le séparateur décimal point soit pris en compte comme tu l’as si bien démontré dans ta deuxième formule, plusieurs saisies satisfaisante j’ai balancé la formule.
Erreur de ma part j’aurai du changer mes paramètres régionaux ou décomposer la formule, la solution était trop facile
et ma conclusion « En attente de vos remarques ou rectification » n’était pas innocente 


A+
Mike-31

Un problème sans solution est un problème mal posé  (Einstein)

gbinforme · Answer

bonjour tout le monde,

Je vois que vous avez bien cogité pendant que j'étais absent mais Éric il me semble qu'il faut une ruse de sioux Mr+ pour pouvoir tenir compte des espaces insécables comme le fait remarquer Le Pingou.
=SUBSTITUE(GAUCHE(SUBSTITUE(A3&"a";CAR(160);"");EQUIV(VRAI;CODE(STXT(SUBSTITUE(A3;CAR(160);"")&"a";LIGNE($1:$20);1))>57;0)-1);".";",")*1
Toujours en matricielle à valider par shit+ctrl+entrée 

Sinon avec 8 795 769 en insécables, on n'obtiens que 8 car 160 est supérieur à 57 si on ne l'enlève pas avant la recherche du non numérique.

eriiic · Answer

Bonjour tout le monde,

Bien vu, c'est vrai que ce substitue() là il faut le mettre à l'intérieur.
C'est vrai aussi que je l'ai ajouté sans tester, mea culpa ;-)
eric

Mike-31 · Answer

Salut tous,

J’adore ce Forum surtout lorsque les échanges sont courtois et constructifs, et que chacun essaie de se surpasser pour apporter la meilleure solution pour l’enrichissement tous.
Ce n’est malheureusement pas le cas de tous les intervenants pourtant de valeur ce qui terni cette image

Bravo et essayons de continuer longtemps dans cet esprit

Cordialement

A+
Mike-31

Un problème sans solution est un problème mal posé  (Einstein)

Excel 2003 - Fonctions de transformations

25 réponses

Discussions similaires