Challenge : formule pour extraire des données d'une référence
Bonjour à tous,
Je suis en train de préparer une version pour Excel du générateur de formules pour Google Sheets (qui permet de générer une formule pour l'extraction de données automatiquement).
Il sera pleinement utilisable pour les utilisateurs du pack de fonctions du site, mais seulement partiellement utilisable pour les autres utilisateurs (puisque les formules les plus avancées contiennent des fonctions du pack avec des expressions régulières).
Pour illustrer l'intérêt d'utiliser de ces fonctions (et mieux comprendre pourquoi l'outil utilise ce type de fonctions), je pense montrer un exemple volontairement complexe d'extraction avec et sans fonctions du pack.
Pour le challenge, je vous propose donc d'écrire la formule la plus courte possible pour réaliser cette extraction complexe avec uniquement les fonctions d'Excel (à gauche les données, à droite le résultat que doit renvoyer la formule pour chaque donnée) :
| Données | Résultat attendu |
|---|---|
| C3F.Z77.FR.89.0 | FR |
| UZ23 45 USA 87 K90 | USA |
| O87 98.1.ES.TG5 87 | ES |
| R3.U7621.7 FRANCE 70.6 | FRANCE |
| FZ.PVQ.DE.6.89 | DE |
Les règles du jeu :
- une seule formule (avec autant de fonctions que nécessaire, sans utiliser d'autres cellules pour les calculs, non matricielle)
- uniquement avec des fonctions "classiques" d'Excel (la formule doit pouvoir être utilisable au minimum avec Excel 2013/2016)
- sans VBA
- pas de résultat en dur (on doit pouvoir changer les lettres des pays et obtenir le bon résultat, y compris pour des pays non listés dans cet exemple)
Une piste pour l'extraction :
- si le point et l'espace sont considérés comme des séparateurs, le pays est la 3e valeur depuis la droite de chaque chaîne
Bon courage
Bonjour,
si pas de point, ni de blanc dans le nom du pays, pour le moment j'en suis à 509 caractères en FR 504 en UK.
edit : 506 en FR
=GAUCHE(STXT(SUBSTITUE(SUBSTITUE(A1;" ";".");".";"µ";NBCAR(A1)-NBCAR(SUBSTITUE(SUBSTITUE(A1;" ";".");".";""))-2);TROUVE("µ";SUBSTITUE(SUBSTITUE(A1;" ";".");".";"µ";NBCAR(SUBSTITUE(A1;" ";"."))-NBCAR(SUBSTITUE(SUBSTITUE(A1;" ";".");".";""))-2))+1;99);TROUVE(".";STXT(SUBSTITUE(SUBSTITUE(A1;" ";".");".";"µ";NBCAR(SUBSTITUE(A1;" ";"."))-NBCAR(SUBSTITUE(SUBSTITUE(A1;" ";".");".";""))-2);TROUVE("µ";SUBSTITUE(SUBSTITUE(A1;" ";".");".";"µ";NBCAR(A1)-NBCAR(SUBSTITUE(SUBSTITUE(A1;" ";".");".";""))-2))+1;99))-1)avec office 365, 151 en FR et 145 en UK.
=LET(x,SUBSTITUTE(A1," ","."),l,LEN(x)-LEN(SUBSTITUTE(x,".","")),m,SUBSTITUTE(x,".","µ",l-2),d,MID(m,SEARCH("µ",m)+1,99),LEFT(d,SEARCH(".",d)-1))edit : 506 en FR
Bien joué
avec office 365, 151 en FR et 145 en UK
Bel exemple de l'utilisation de la fonction LET (même si je ne vais pas pouvoir la retenir dans ce cas)
L'idée ici est de montrer pourquoi l'outil ne propose pas une formule qui soit directement utilisable avec toutes les versions (à peu près) récentes d'Excel.
Bonjour à tous
Un test en matricielle , mais 536 caractères
=DROITE(STXT(SUBSTITUE(A2;".";" ");1;CHERCHE("|";SUBSTITUE(SUBSTITUE(A2;".";" ");" ";"|";NBCAR(SUBSTITUE(A2;".";" "))-NBCAR(SUBSTITUE(SUBSTITUE(A2;".";" ");" ";""))-1)));EQUIV(" ";STXT(STXT(SUBSTITUE(A2;".";" ");1;CHERCHE("|";SUBSTITUE(SUBSTITUE(A2;".";" ");" ";"|";NBCAR(SUBSTITUE(A2;".";" "))-NBCAR(SUBSTITUE(SUBSTITUE(A2;".";" ");" ";""))-1)));NBCAR(STXT(SUBSTITUE(A2;".";" ");1;CHERCHE("|";SUBSTITUE(SUBSTITUE(A2;".";" ");" ";"|";NBCAR(SUBSTITUE(A2;".";" "))-NBCAR(SUBSTITUE(SUBSTITUE(A2;".";" ");" ";""))-1))))-LIGNE($1:$255);1);0))Bonjour à tous
Une tentative biaisée
=STXT(A2;CHERCHE("µ";SUBSTITUE(SUBSTITUE(A2;" ";".");".";"µ";SOMMEPROD((STXT(SUBSTITUE(A2;" ";".");LIGNE(INDIRECT(1&":"&NBCAR(A2)));1)=".")*1)-2))+1;CHERCHE("µ";SUBSTITUE(SUBSTITUE(A2;" ";".");".";"µ";SOMMEPROD((STXT(SUBSTITUE(A2;" ";".");LIGNE(INDIRECT(1&":"&NBCAR(A2)));1)=".")*1)-1))-1-CHERCHE("µ";SUBSTITUE(SUBSTITUE(A2;" ";".");".";"µ";SOMMEPROD((STXT(SUBSTITUE(A2;" ";".");LIGNE(INDIRECT(1&":"&NBCAR(A2)));1)=".")*1)-2)))re-bonjour,
@78chris, je ne vois pas le biais
en combinant nos formules : 352 caractères
=STXT(A1;CHERCHE("µ";SUBSTITUE(SUBSTITUE(A1;" ";".");".";"µ";NBCAR(A1)-NBCAR(SUBSTITUE(SUBSTITUE(A1;" ";".");".";""))-2))+1;CHERCHE("µ";SUBSTITUE(SUBSTITUE(A1;" ";".");".";"µ";NBCAR(A1)-NBCAR(SUBSTITUE(SUBSTITUE(A1;" ";".");".";""))-1))-1-CHERCHE("µ";SUBSTITUE(SUBSTITUE(A1;" ";".");".";"µ";NBCAR(A1)-NBCAR(SUBSTITUE(SUBSTITUE(A1;" ";".");".";""))-2)))EDIT ADMIN : insère tes formules à l'aide de stp
Bravo à tous les 3 pour vos solutions
Je vais retenir la dernière formule de h2so4 qui me paraît déjà étonnamment courte pour une formule avec uniquement des fonctions de base d'Excel. Ca aidera probablement les quelques utilisateurs de cet outil à mieux comprendre pourquoi l'outil ne peut pas leur proposer de telles formules automatiquement.
Dans ce cas, la formule suivante (qui nécessite d'avoir le pack de fonctions) sera proposée par l'outil :
=REGEX_EXTRACT(A1;"([^ .]*)(?:[ .][^ .]*){2}$")Si vous avez envie de tester l'outil, je viens de le mettre en ligne : https://www.excel-pratique.com/fr/utilitaires/formule-extraction
EDIT ADMIN : insère tes formules à l'aide de </> stp
Ouch, n'était-ce pas le cas ?
Ouch, n'était-ce pas le cas ?
Tu avais mis tes 3 formules dans (au lieu de ).
N'hésite pas à insérer tes formules comme si c'était du code VBA pour profiter de la coloration syntaxique et du bouton "Copier"
Pour avoir :
=STXT(A1;CHERCHE("µ";SUBSTITUE(SUBSTITUE(A1;" ";".");".";"µ";NBCAR(A1)-NBCAR(SUBSTITUE(SUBSTITUE(A1;" ";".");".";""))-2))+1;CHERCHE("µ";SUBSTITUE(SUBSTITUE(A1;" ";".");".";"µ";NBCAR(A1)-NBCAR(SUBSTITUE(SUBSTITUE(A1;" ";".");".";""))-1))-1-CHERCHE("µ";SUBSTITUE(SUBSTITUE(A1;" ";".");".";"µ";NBCAR(A1)-NBCAR(SUBSTITUE(SUBSTITUE(A1;" ";".");".";""))-2)))Au lieu de :
=STXT(A1;CHERCHE("µ";SUBSTITUE(SUBSTITUE(A1;" ";".");".";"µ";NBCAR(A1)-NBCAR(SUBSTITUE(SUBSTITUE(A1;" ";".");".";""))-2))+1;CHERCHE("µ";SUBSTITUE(SUBSTITUE(A1;" ";".");".";"µ";NBCAR(A1)-NBCAR(SUBSTITUE(SUBSTITUE(A1;" ";".");".";""))-1))-1-CHERCHE("µ";SUBSTITUE(SUBSTITUE(A1;" ";".");".";"µ";NBCAR(A1)-NBCAR(SUBSTITUE(SUBSTITUE(A1;" ";".");".";""))-2)))