Générer une formule d'extraction

A partir d'un échantillon de données de quelques cellules, cet outil est capable de générer automatiquement une formule d'extraction que vous pourrez ensuite copier-coller dans votre feuille Excel.

Les formules proposées peuvent contenir les fonctions GAUCHE, DROITE ou STXT mais également des fonctions plus avancées (qui nécessitent d'avoir installé l'add-in gratuit du site) telles que REGEX_REPLACE et REGEX_EXTRACT.

L'exemple ci-dessous vous aidera à mieux comprendre le fonctionnement de ce générateur de formules.


Exemple d'utilisation

Dans cet exemple, l'objectif est de trouver une formule capable d'extraire le pays des références suivantes :

excel tableau formule extraction

Pour tenter de trouver la formule magique qui va extraire ces données, sélectionnez une portion de la plage de cellules contenant les données (3 à 6 cellules sont généralement suffisantes) et copiez-les :

excel tableau copier formule extraction

Collez-les ensuite dans la zone de texte en bas de page et cliquez sur le bouton Continuer :

refs formule extraction

Après validation, le tableau suivant apparaît :

Colonne de cellulesRésultat souhaitéRésultat calculé
C3F.Z77.FR.89.0...
UZ23 45 USA 87 K90...
O87 98.1.ES.TG5 87...
R3.U7621.7 FRANCE 70.6...
FZ.PVQ.DE.6.89...

Entrez ensuite les valeurs attendues dans au moins 2 champs (ici, les pays) :

Colonne de cellulesRésultat souhaitéRésultat calculé
C3F.Z77.FR.89.0FR
UZ23 45 USA 87 K90USA
O87 98.1.ES.TG5 871
R3.U7621.7 FRANCE 70.67
FZ.PVQ.DE.6.89DE

Si l'outil arrive à générer une formule à partir de ces données, l'aperçu des résultats sera affiché dans la colonne de droite et la formule juste au-dessous du tableau.

Si certaines valeurs de la colonne de droite ne correspondent pas au résultat attendu (ce qui est le cas ici), complétez un ou plusieurs champs supplémentaires :

Colonne de cellulesRésultat souhaitéRésultat calculé
C3F.Z77.FR.89.0FR
UZ23 45 USA 87 K90USA
O87 98.1.ES.TG5 87ES
R3.U7621.7 FRANCE 70.6FRANCE
FZ.PVQ.DE.6.89DE

Le résultat est cette fois-ci correct et la nouvelle formule est affichée juste au-dessous :

=REGEX_EXTRACT(A1;"([^ .]*)(?:[ .][^ .]*){2}$")

Pour terminer, copiez la formule dans votre feuille Excel, remplacez A1 par la première cellule contenant les données (ici, la cellule B2) et étirez la formule vers le bas :

excel regex extract fonction extraire formule extraction
Les formules contenant les fonctions REGEX_REPLACE ou REGEX_EXTRACT ne sont utilisables qu'avec l'add-in gratuit du site.

Pour mieux comprendre pourquoi ces fonctions sont si puissantes et pourquoi cet outil ne propose pas plutôt une formule contenant uniquement des fonctions présentes sur toutes les versions d'Excel, voici la plus courte formule capable de réaliser la même extraction avec les fonctions "classiques" d'Excel :

=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)))
Si vous souhaitez faire part de vos remarques, questions ou appréciations à propos de cet outil, un sujet a été ouvert sur le forum.

Plage de cellules

Copiez-collez environ 5 cellules contenant les données (ou entrez une valeur par ligne) :