Recherches formules pour extraire des termes d'une cellule

Bonjour,

J'utilise occasionnellement Excel dans le cadre de mon travail de recherche.

Ma demande d'aide est la suivante : Je souhaite extraire différents termes contenus dans une cellule d’Excel.

La structure que je rencontre le plus souvent se présente sous cette forme : ADM_ARAGON_700K_1798

Il y a donc ici 4 termes différents, séparés par un « _ »

Je souhaiterais connaître les formules me permettant de créer les colonnes suivantes :

- colonne où ne figurera que le second terme

Précision : il arrive aussi que les cellules soient structurées comme suit : ADM_BISCAYE_ALAVA_250K_1800 ou encore ADM_GRENADE_CORDOUE_JAEN_660K_1782 Idéalement, il aurait été préférable qu'elles soient formulées ainsi : BISCAYE-ALAVA ou GRENADE-CORDOUE-JAEN.

Le plus important pour moi est d'obtenir à la fin BISCAYE_ALAVA ou GRENADE_CORDOUE_JAEN.

Je ne suis pas expert; peut-être qu'une formule où le premier terme ainsi que les deux derniers sont "éliminés" ....

Puis :

- colonne où figureront le second et le 3eme terme, avec le « _ » en séparateur (la présence du séparateur n’est pas un souci) ; rappel : le 3eme terme pouvant comporter un ou plusieurs "_" ; il est donc important "d'éliminer" le dernier terme (date)

et enfin :

- colonne où apparaîtra uniquement le 3eme terme, avec sa transformation « en millier » puisque 700K signifie 700000 et c’est ce chiffre que je souhaite obtenir dans cette colonne.

Bonsoir,

Ici : Générer une formule d'extraction (excel-pratique.com) il y a une possibilité de générer les formules d'extraction. Mais perso je n'arrive pas à trouver la bonne formule. Il faudrait essayer, j'ai peut-être oublié quelque chose. J'ai pourtant placé l'addin. Voici ce que me propose le chercheur de formule pour ARAGON, BISCAYE_ALAVA, GRENADE_CORDOUE_JAEN:

=REGEXREPLACE(A1;"^.{4}(.*).{10}$";"$1")

Bonsoir Thierry2000 et

Une petite présentation ICI serait la bienvenue

Si vous ne l'avez pas encore fait, je vous invite à lire la charte du forum [A LIRE AVANT DE POSTER]
qui vous aidera dans vos demandes et réponses sur ce forum et notamment

• Joignez (si possible) un fichier pour augmenter vos chances d'obtenir de l'aide en cliquant sur le bouton Fichier de l'éditeur. Si votre fichier est trop lourd ou contient des données personnelles, créez une version allégée de votre fichier avec juste assez d'informations pour permettre de comprendre votre problème. Dans tous les cas, ne postez JAMAIS de fichiers avec des informations personnelles ou confidentielles (cet utilitaire peut vous aider à les retirer).

Avec un exemple de ce que vous avez, et ce que vous voulez, cela nous permettrais peut-être de mieux comprendre !?

Merci de votre participation

Cordialement

Bonsoir fcyspm30, attention pour utiliser la formule donnée,
sauf erreur il faut installer l'add-in XLP ce n'est pas forcément ce que veut Thierry

J'avais rempli très très succintement ma fiche de présentation, c'est vrai.

En ce qui concerne le fait de joindre un fichier excel : je ne peux le faire, simplement parce que l'ordi que j'utilise pour mes travaux de recherche n'est pas le mien, que je n'ai pas accès à internet sur ce poste là et que de plus je n'ai pas excel sur mon ordi personnel.

Je pourrais joindre un fichier tableur mais de type libre-office. Je crains que ça ne soit pas bienvenu ici. Voilà pourquoi je n'ai pas joint de fichier à ma demande.

Effectivement, comme le mentionne BrunoM45 le modérateur, je ne souhaite pas installer un add-in sur l'ordinateur. Les éléments ci-dessus viennent conforter ceci.

Merci.

Thierry

Re,

Je ne sais pas si l'extension type ".ods" passe sur le forum, mais sinon pourquoi pas au pire

Ce serait au moins pour comprendre

A+

Bonjour à tous,

un essai de fonction personnalisée avec les éléments fournis.
Je suis parti du principe que "k_" était toujours présent.
Explications dans le fichier.
Remarque : pour modifier/supprimer une formule matricielle multi-cellule il faut sélectionner toute la plage de la matrice.
Remarque 2 : la fonction personnalisée doit être présente dans un module standard du fichier à traiter.
Si c'est trop chinois pour toi ou si tu ne peux pas modifier et mettre en xlsm le fichier original, tu peux faire des copié-collés des valeurs à traiter dans ce fichier et ramener le résultat dans le tien.

eric

bonjour,

2021-365 =STXT(A2;CHERCHE("_";A2)+1;AGREGAT(15;6;1/(1/CHERCHE(SEQUENCE(;10;0);A2));1)-(CHERCHE("_";A2)+2))

<=2019 =STXT(A2;CHERCHE("_";A2)+1;AGREGAT(15;6;1/(1/CHERCHE(COLONNE($A$1:$J$1)-1;A2));1)-(CHERCHE("_";A2)+2))

EDIT : correction en rouge dans la formule <=2019, doit être "J" dans le fichier ici-dessous

Bonjour,

Tout d’abord, merci à BrunoM45, Eriiic et BsAlv pour leurs messages.

Le fait de ne pas avoir accès à Excel comme je le voudrais - croyez bien que cela me gêne beaucoup – et afin de bien expliquer ce que je souhaite obtenir, je vous adresse comme l’a proposé BrunoM45, un fichier .ods. J’espère qu’il sera pris en compte par le site.

Sur ce fichier, j’ai nommé les colonnes et j’ai mis les valeurs à obtenir (colonnes concernées par les formules : B,C et F)

Pour la formule recherchée en colonne C, j'ai appliqué la formule fournie par BsAlv, en colonne D et le résultat me convient parfaitement.

Vous constaterez que j’ai laissé la formule dans les cellules et je n’ai donc pas mis « en valeurs ».

Cette formule conviendra donc sur la version excel que j’utilise (je suis presque sûr qu’il s’agit de la version 2019 ; j’espère ne pas me tromper ). Merci beaucoup à BsAlv.

Commentaire : Je constate que la formule fournie est très complexe (pour moi en tout cas) et jamais je n’aurais pu la trouver sans votre aide !!!

Je remercie à l’avance les personnes qui pourront me fournir les formules à utiliser pour arriver au résultat mentionné dans les colonnes B et F.

Enfin, la colonne E correspondant à l’échelle en Kilo est une colonne « intermédiaire », peut-être un passage obligé pour être utilisé pour une formule ???.

Elle contient donc ce que j’appelais au départ le troisième terme du nom du fichier figurant en colonne A.

Bonne journée,

Thierry

Bonjour Thierry,

Nous avons un gros souci je pense
si vous ne voulez pas de la fonction personnalisée d'eriiic et pas de la formule de BsAlv...

Et bien je ne sais pas comment vous allez faire !?

Ou alors passer par x colonnes ?

A+

bonjour,

Commentaire : Je constate que la formule fournie est très complexe (pour moi en tout cas) et jamais je n’aurais pu la trouver sans votre aide !!!

[s=co-b2a2c7][/s]La formule corrigée dans ma réaction précedente contenait

"COLONNE($A$1:$J$1)-1" >>>> donc "le numéro des 10 premières colonnes -1" >>>> le série 0 à 9 ou 0,1,2,3,4,....,8,9

ma formule dans votre colonne J contient COLONNE($A$1:$H$1)-1 >>> les 8 premières colonne numéros -1 >>>> le série 0 à 7 ou 0,1,2,3,4,5,6,7 et ne pas 8 et 9.

Cela veut dire que si le premier chiffre dans ce texte est un 8 ou 9, il sera ignoré.

ce "COLONNE($A$1:$H$1)-1" est important et ne peut pas être modifié, j'éspère que vous comprenez l'explication.

La formule pour excel 2021-365 avec SEQUENCE(;10;0) est pour cette raison plus facile.

Bonjour,

BrunoM45 écrit : ""Nous avons un gros souci je pense ; si vous ne voulez pas de la fonction personnalisée d'eriiic et pas de la formule de BsAlv...

Et bien je ne sais pas comment vous allez faire !? ; Ou alors passer par x colonnes ?"

Je ne comprends pas ce que vous voulez dire ; j'ai écrit dans mon dernier message que la formule fournie par BsAlv, applicable pour aboutir au résultat de la colonne C me convient. Concrètement, cette formule est appliquée dans la colonne D du tableau nommé « Résultats souhaités pour la recherche de formules » que je mets en PJ. Je n’ai jamais dit que je ne voulais pas de ces fonctions et formules.

Je disais que je ne suis pas du tout expert en formules sur excel et que jamais je n’aurais pu trouver moi-même une telle formule, aussi complexe. Bien sûr, pour les cas identiques que je vais rencontrer à l’avenir , il me suffit de faire un copier-coller de la formule fournie.

En ce qui concerne les colonnes relatives à l’échelle (E et F) du tableau en PJ, les formules fournies par BsAlv me satisfont. C’est parfait. J’ai bien compris que pour la transformation en milliers, je dois faire attention à sélectionner la cellule qui sert de référence (où se trouve le chiffre donné en K). Merci beaucoup pour ces deux formules.

Par contre, pour la colonne intitulée « identification » la formule fournie par BsAlv ne correspond pas au résultat souhaité. Je m’explique :

Base de départ : ADM_ARAGON_700K_1798 (nom de fichier)

Je souhaite obtenir ARAGON_700K Or la formule de BsAlv

=SUPPRESPACE(GAUCHE(SUBSTITUE($A2;"_";REPT(" ";100);NBCAR($A2)-NBCAR(SUBSTITUE($A2;"_";"")));100))

aboutit à ADM_ARAGON_700K

Rappel : je travaille aujourd’hui sur un fichier ods. peut-être est-ce la cause de l'échec de l'opération.

La nouvelle formule fournie par BsAlv pour la colonne « titre document » n’aboutit pas au résultat souhaité mais sa formule fournie précédemment me convient, comme je l’ai déjà dit. Donc la seule colonne en attente de solution est celle appelée "Identification".

J’ai omis d’évoquer, dans mon message précédent, les fonctions fournies par Eriiic et je m’en excuse. Je m’en suis aperçu après l’envoi du message et je n’ai pas voulu surcharger ma demande et j'ai préféré laisser d’abord les lecteurs prendre connaissance du message. Mais je comptais bien revenir sur les fonctions fournies par Eriiic. J'y viens :

J’ai fait un copier-coller de la fonction =DECOUP(A2;2) afin de remplir la colonne « titre document »

cette fonction ne fonctionne pas chez moi mais j’insiste sur le fait qu’en l’absence de l’ordi sur lequel j’ai mes tableaux excel, je travaille sur des fichiers ods. Ce qui doit créer un souci au niveau de l’application de la formule. J’essaierai les fonctions fournies par Eriiic dès que j’aurai accès à l’ordinateur équipé d’excel. Je me rends compte que tout ceci constitue un grand désagrément pour moi et aussi pour ceux qui comme vous font des efforts pour m'aider.

Je précise qu’en lisant cette fonction « =DECOUP(A2;2) », elle m’a paru « logique » car enfait on découpe le contenu de la cellule de départ en tranches et on doit pouvoir sélectionner une ou plusieurs tranches pour afficher le résultat du choix dans une autre colonne. les tranches étant séparées par un _

Désolé si la façon dont je m’exprime n’est pas très adaptée à votre langage d’experts en excel.

D’ailleurs, à ce sujet, à l’attention de BsAlv : à la lecture de la dernière partie de votre dernier message :

« [s=co-b2a2c7][/s]La formule corrigée dans ma réaction précedente contenait

"COLONNE($A$1:$J$1)-1" >>>> donc "le numéro des 10 premières colonnes -1" >>>> le série 0 à 9 ou 0,1,2,3,4,....,8,9

ma formule dans votre colonne J contient COLONNE($A$1:$H$1)-1 >>> les 8 premières colonne numéros -1 >>>> le série 0 à 7 ou 0,1,2,3,4,5,6,7 et ne pas 8 et 9.

Cela veut dire que si le premier chiffre dans ce texte est un 8 ou 9, il sera ignoré.

ce "COLONNE($A$1:$H$1)-1" est important et ne peut pas être modifié, j'éspère que vous comprenez l'explication.

La formule pour excel 2021-365 avec SEQUENCE(;10;0) est pour cette raison plus facile.

j'avoue ne pas y avoir compris grand chose. Cela vient de moi, pas de vous. Désolé, je suis un utilisateur basique, j’applique en copier-coller les formules que des personnes compétentes comme vous ont pu m’indiquer. Mes capacités en formules excel se limitent à savoir extraire les x derniers chiffres d’une cellule. Dans le cas présent, je ne sais donc qu’extraire la date dans la cellule de référence.

Merci à tous pour votre aide et espérant obtenir une formule pour la colonne "Identification",

Thierry

re,

Re,

Merci à BsAlv ! Je viens de tester le tout assez rapidement parce que je dois m'absenter. Cela fonctionne pour toutes les colonnes souhaitées, au moins sur fichier ods.

J'essayerai en début de semaine sur excel mais cela devrait bien se passer je pense. Bien sûr, je reviendrai ici pour vous confirmer le succès et encore une fois : un grand merci pour vos recherches.

Thierry

Bonjour,

le langage VBA n'est pas compatible avec Libre Office. Il permet d'étendre les possibilités d'excel.
La fonction doit être présente dans le classeur.
Alt+F11 pour ouvrir l'éditeur VBE
Clic-droit sur le projet du classeur à gauche et 'Insertion / Module'
Y coller le code :

Function decoup(ch As String, Optional pos As Variant) As Variant
    Dim p As Long, tmp
    p = InStr(ch, "_")
    ch = Mid(ch, p + 1)
    ch = Replace(ch, "k_", "000|", , , vbTextCompare)
    p = InStr(ch, "|")
    p = InStrRev(ch, "_", p)
    Mid(ch, p) = "|"
    tmp = Split(ch, "|")
    If IsMissing(pos) Then
        decoup = tmp
    Else
        decoup = tmp(pos - 2)
    End If
End Function

enregistrer sous... le classeur avec le type *.xlsm
La nouvelle fonction personnalisée est disponible pour les feuilles.
eric

Bonjour à tous !

Une proposition via Power Query (nativement intégré à votre version Excel) :

Bonjour,

Merci beaucoup à Eriiic et à JFL pour leurs informations. J'attendrai d'avoir accès à excel pour examiner tout cela. Je constate avec grande surprise qu'il existe plusieurs façons de s'y prendre pour arriver au résultat souhaité. Je ne m'imaginais pas tout cela, moi l'utilisateur basique de excel.

A Eriiic : en résumé, il suffit de suivre les indications de votre message du 18/06 à 13h02 pour activer la prise en compte des fonctions VBA (des fonctions dont je ne connaissais pas du tout l'existence). Et ensuite j'applique la formule indiquée dans le fichier en annexe de votre premier message :

=DECOUP(A2;2) si je veux extraire l'élément figurant en second rang de la cellule de base;

j'aurai donc à partir de ADM_ARAGON_700K_1798 le terme ARAGON dans la colonne où j'aurai mis la formule ? c'est bien cela ?

si je prends la formule =DECOUP(A2;3) j'obtiendrai 700K ? c'est ça ? ce qui signifie qu'excel considère le "_" comme un "séparateur de termes"

Vous proposez une seconde façon de procéder qui, d'après ce que j'ai compris, permet d'obtenir dans une colonne les termes situés en tel et tel rang. C'est bien cela ? je peux donc choisir les rangs que je veux, à ma guise. Et si je devais avoir à traiter des cellules de base composées d'un élément supplémentaire, pouvoir l'extraire seul dans une nouvelle colonne, en précisant juste son rang ? Si c'est cela, alors c'est vraiment top.

J'ai bien lu que je ne devais pas me contenter, pour cette façon-là, de faire "entrée" pour appliquer la formule mais maj+ctrl+entrée.

A JFL : Je n'ai pas accès à excel au moment où je vous écris. Je ne peux procéder aux opérations. Je ne connais pas Power Query. Il est donc inclus dans le excel que j'utilise, c'est cela ? je suis quasiment sûr qu'il s'agit de la version 2019, je vérifierai cela en début de semaine. Cette façon de formuler les demandes m'est inconnue et je verrai le moment venu. En tout cas, soyez sûr que je la testerai.

Encore merci à tous d'avoir consacré de votre temps à répondre à ma recherche. Bien sûr, je vous tiendrai informé du résultat de toutes vos propositions.

Cordialement,

Thierry

Bonjour à tous !

thierry2000 a écrit :

Je constate avec grande surprise qu'il existe plusieurs façons de s'y prendre pour arriver au résultat souhaité. Je ne m'imaginais pas tout cela, moi l'utilisateur basique de excel.

Le monolithisme n'est pas de mise avec Excel et la créativité des contributeurs impressionnante donc......

A JFL : Je n'ai pas accès à excel au moment où je vous écris. Je ne peux procéder aux opérations. Je ne connais pas Power Query. Il est donc inclus dans le excel que j'utilise, c'est cela ? je suis quasiment sûr qu'il s'agit de la version 2019, je vérifierai cela en début de semaine. Cette façon de formuler les demandes m'est inconnue et je verrai le moment venu.

Power Query accompagne Excel depuis la version 2010 (complément gratuit à installer pour Excel 2010 et 2013. Nativement intégré à partir de la version 2016).

Sachez qu'il existe une foultitude de tutoriels sur le net traitant de cet outil.

Bonjour,

oui et non. Tu obtiendras 700000, je fais la conversion.

je peux donc choisir les rangs que je veux, à ma guise

non.
Ca sera d'office n termes à partir du 2nd, le 1er étant éliminé. Et autant de termes que de cellules sélectionnées.
Si tu as de nombreuses lignes ça permet de diviser d'autant le nombre de formules.
Les formules matricielles présentent des avantages, et aussi des inconvénient s'il s'agit de modifier la formule et qu'on n'est pas familier.
Disons que ça ne coûtait pas plus cher de l'ajouter, donc si tu n'es pas à l'aise reste avec la formule à 2 paramètres.
Surtout que tu n'extrais que 2 valeurs. Si tu en avais 10 ou plus tu comprends que ça aurait été plus intéressant, autant pour l'écriture que pour le temps de calcul si de nombreuses lignes.
eric

Bonjour,

J'ai demandé au propriétaire de l'ordinateur sur lequel j'utilise Excel de me dire la référence de la version installée.

Contrairement à ce que je pensais, il ne s'agit pas de Excel 2019 mais de Microsoft Office Professionnel Plus 2010. Excel est donc une partie de MOPP 2010.

J'ai demandé à la personne de voir si elle pouvait installer les codes VBA et voir sur un exemple que je lui ai donné, si elle peut créer des colonnes où elle aura extrait différents types d'infos. Apparemment ça donne de bons résultat. Bien sûr j'aurai des corrections à faire en fonction des cellules qui sont plus ou moins bien rédigées. Je verrai tout ça dans un ou deux jours, quand j'aurai accès à Excel.

Concernant Power Query, il semble que je devrai installer un complément. Je verrai ça au même moment.

Un grand merci à tous et je vous tiens au courant, bien sûr.

Thierry

Re,

suite aux réponses fournies par Eriiic : concerne formules en VBA

La personne qui possède l’ordinateur a pris un peu de son temps pour tester un peu plus les formules VBA et je suis content du résultat. J’ai eu de sa part un fichier excel avec, m’a-t-elle dit, les formules non transformées en valeurs - que je n’ai pu ouvrir faute d‘Excel - et aussi une capture d’écran pour qu’en attendant, je puisse constater le résultat.

Je suis reparti vers elle en lui indiquant une autre formule pour la date - une des seules formules que je connaisse, à savoir =DROITE(A2;4) Comme tous les identifiants se terminent la date, il n’y aura donc pas de corrections à faire. Elle a mis cette formule en colonne F.

Je me permets donc de poster ce message sur le forum, accompagné du fichier reçu, en xlsm où les formules VBA sont visibles.

Il apparaît que les cellules en colonne A ( « Identifiant ») ne sont pas toutes sous le même type du fait des noms de feuilles qui possèdent souvent des « _ » entre les mots. Ce qui rend les choses un peu plus compliquées pour avoir une colonne « Titre de feuille homogène et cohérente sur toute la colonne. De toute façon, je sais que rien ne sera jamais parfait, à moins de modifier beaucoup d’identifiants pour avoir cette uniformité.

En ce qui concerne les échelles, certaines sont en M (million). Je pense que là, il est préférable de tout corriger à la main, leur quantité étant limitée. C’est un cas particulier qui ne mérite pas une formule spéciale.

Par contre, si je devais avoir dans le futur toute une série d’identifiant en M, comment dois-je faire pour extraire l’échelle et la transformer en chiffre ?

Je remarque que des lignes A 108 à A 123, j’ai une série portant des numéros de feuille allant de 01 à 16.

Comment devrais-je faire pour les extraire si je voulais créer une colonne spécifique avec ces n°, si j’avais ce genre de choses en grand nombre ? Est-ce possible sans tout chambouler ? Là, j’en demande sans doute un peu beaucoup. Si trop compliquén, pas de soucis, cç aurait été un « plus ».

Parfois des réponses bizarres apparaissent comme en B31 où normalement je devrais obtenir ESPAGNE. Je pense que c’est dû au fait que l’échelle soit en M et non en K. Pas de souci, je corrigerai manuellement en A31.

Le seul vrai souci rencontré c’est le remplissage de la colonne « Identifiant abrégé » à l’aide de la syntaxe 2, matricielle multi cellules =DECOUP(A2) comme vous l’avez indiqué dans un message (tableau avec des bulles en bleu pour indiquer la manip) A lire votre document, cela nous paraît simple à tous les deux mais même en nous aidant mutuellement à distance, nous n’avons pas trouvé la bonne façon de procéder. Vous écrivez : pour les deux éléments E2:F2 …. saisir la formule ….. valider en matriciel avec maj+ctrl+entrée

Mais nous n’y sommes pas arrivés. Nous ne sommes pas des pros, c’est sûr.

Dans le cas présent, il faut arriver à ce que la colonne D prenne en compte les colonnes B et C pour aboutir au résultat « ARAGON_700K » en D2.

Vous serait-il possible de détailler un peu plus les actions à faire pour aboutir au résultat souhaité ?

Merci beaucoup à l’avance pour votre aide, laquelle m’a d’ores et déjà été très très utile.

Thierry