XL 2016 Extraire une partie d'un texte d'une cellule et le copier vers une autre cellule

INFINITY100

XLDnaute Occasionnel
Bonjour,

Je reviens à vous encore une fois pour trouver une solution à mon soucis, en effet dans mon classeur en cellule A3 j'ai un ensemble de désignation des travaux. Seulement moi ce que je veux faire c'est une extraction de deux expression bien précises vers la cellule B3 et C3 à savoir pour l'exemple Figure1 (type Renault,) et Figure2 (Immatriculés : RN-16 et RN-14.)

je m’explique :

1- Dans un premier temps je voudrai faire un test si le mot "Type" et le mot "Immatriculé" sont présents dans la désignation alors la suite du code s’exécute sinon avertir l'utilisateur qu'un des mots est absent et mettre un tiret "-" par la suite à la place du mot absent.

2- Après le test extraire le mot " Type ... " de la première lettre jusqu'à la virgule "," (Figure1) et le mot " Immatriculé .... " de la première lettre aussi jusqu'au point final "." (Figure2) et mettre le premier dans la cellule B3 et le second dans la cellule C3.

Je joins le fichier pour voir plus claire comment je veux avoir comme résultat et possédant une formule qui fait ce job mais pas vraiment à mon goût ;)

NB :
Sachant que la syntaxe du mot "type" peut être comme suis : Type - TYPE - type ou Types - TYPES - types
et la syntaxe du mot "immatriculé" peut être comme suis : Immatriculé - IMMATRICULÉ - immatriculé ou Immatriculés - IMMATRICULÉS - immatriculés ou Immatriculées - IMMATRICULÉES - immatriculées

Merci à vous tous

Cordialement 🙏
 

Pièces jointes

  • Exemple.xlsm
    10 KB · Affichages: 22

Staple1600

XLDnaute Barbatruc
Re, Bonjour fanfan38

Finalement 365 offre de bien belles choses ;)
Une autre formule qui fait le boulot
En B3
=FRACTIONNER.TEXTE(TEXTE.APRES(A3;"type";1;0;1);",";;1)

NB: C3 doit être vide

EDITION : Si on veut utiliser deux formules
En B3
=TEXTE.AVANT(TEXTE.APRES(A3;"type");",")
En C3
=TEXTE.APRES(TEXTE.APRES(A3;"type");",")

Et en version LAMBDA
En B3
=TEXTE.AVANT(X_Y(A3;"type");",")
En C3
=TEXTE.APRES(X_Y(A3;"type");",")
Avec X_Y qui fait référence à :=LAMBDA(Plage;Sep;TEXTE.APRES(Plage;Sep))
 
Dernière édition:

Staple1600

XLDnaute Barbatruc
Re


Pour le fun et pour occuper ce jeudi consacré aux ascenseurs "christiques"
(moi, je ne connais que celui-ci d'ascenseur : Forms.ScrollBar.1 ;))

Une fonction VBA "imparfaite" à base de RegEx
VB:
Function xtc(rng As Range, Optional part As Long = 0)
Dim str$, objMatches As Object, t
str = rng.text
Set objRegExp = CreateObject("VBScript.RegExp")
objRegExp.pattern = "\btype\s+(.*)"
Set objMatches = objRegExp.Execute(str)
If objMatches.Count <> 0 Then
t = Split(objMatches.Item(0).SubMatches.Item(0), ",")
xtc = t(part)
End If
End Function
Mode d'emploi
En B3
=xtc(A3)
En C3
=xtc(A3;1)

Mais c'est vraiment parce que je trouve le temps long ;)
 

Staple1600

XLDnaute Barbatruc
Re

La journée n'étant pas toujours finie, et mon Excel toujours ouvert ;)
Une seule formule en B2
=FRACTIONNER.TEXTE(TEXTE.APRES(A3;{"type";"types"};-1);",")

Fonctionne si type ou types présent dans la cellule.
Reste le problème de la casse
(Ne fonctionne pas avec TYPE ou TYPES)

EDITION:
Avec cette formule, le problème de la casse est réglé
VB:
=SUPPRESPACE(SUBSTITUE(FRACTIONNER.TEXTE(TEXTE.APRES(TEXTE.APRES(A3;",";1);{"type";"types"};;1);",");"-";""))
Ci-dessous 4 tests
Test_OKpng.png
 
Dernière édition:

Staple1600

XLDnaute Barbatruc
Bonjour le fil

C'est long un pont, et mon Excel a faim ;)
Donc dans la série, "extrayons du string à foison" ;)
Ou comme n'as pas dit Ross dans Friends :" Split my String, dude !"

J'ai remis du LAMBDA dans mon gestionnaire
SEPA fait référence à
VB:
=LAMBDA(Plage;Pattern;FILTRE.XML("<a><b>"&SUBSTITUE(Plage;",";"</b><b>")&"</b></a>";Pattern))
• 1ère extraction
Code:
=SEPA(A1;"//b[contains(following::*[1],':')]")
renvoie : (Fin de ressource) de deux voitures type - Renault
• 2ième extraction
Code:
=SEPA(A2;"//b[contains(preceding::*[1],'y')]")
renvoie : Immatriculés : RN-16 et RN-14.


Pour affiner la 1ère extraction
Code:
STXT(SEPA(A3;"//b[contains(following::*[1],':')]");CHERCHE("typ";SEPA(A3;"//b[contains(following::*[1],':')]"));999)
renvoie : type - Renault
 

INFINITY100

XLDnaute Occasionnel
Merci messieurs Staple1600 et mapomme amplement d'avoir pris la peine de me répondre et j'avoue votre solution en deuxième version arrange bien mes affaires sans oublier tes éffores aussi Staple1600 🙏 et puisque nous y sommes finissons le travail comme il se doit.

En effet y a t'il un moyen d'ajouter aux formule si dessous une condition qui dit que si on trouve pas le mot "typ" ou "immatricul" alors on met directement un tiret "-" dans la cellule.

VB:
=SUPPRESPACE(GAUCHE(STXT(A3;CHERCHE(" ";A3;CHERCHE("type*";A3));333);CHERCHE(",";STXT(A3;CHERCHE(" ";A3;CHERCHE("type*";A3));333))-1))

VB:
=SUPPRESPACE(SUBSTITUE(GAUCHE(STXT(A3;CHERCHE(" ";A3;CHERCHE("immatricul*";A3));333);CHERCHE(".";STXT(A3;CHERCHE(" ";A3;CHERCHE("immatricul*";A3));333))-1);":";""))

Ou bien la formule sierreur fait l'affaire comme indiqué dans le fichier joint en C5 et B6 :)

Que me conseilez-vous ? 🤔

PS: Staple1600 il n'y a pas de fichier modèl à partager car c'est un fichier que j'envisage de créer donc il n'est pas encore né hhh 😊
 

Pièces jointes

  • INFINITY100-extraction texte- v2.xlsx
    10.6 KB · Affichages: 4

INFINITY100

XLDnaute Occasionnel
Re

La journée n'étant pas toujours finie, et mon Excel toujours ouvert ;)
Une seule formule en B2
=FRACTIONNER.TEXTE(TEXTE.APRES(A3;{"type";"types"};-1);",")

Fonctionne si type ou types présent dans la cellule.
Reste le problème de la casse
(Ne fonctionne pas avec TYPE ou TYPES)

EDITION:
Avec cette formule, le problème de la casse est réglé
VB:
=SUPPRESPACE(SUBSTITUE(FRACTIONNER.TEXTE(TEXTE.APRES(TEXTE.APRES(A3;",";1);{"type";"types"};;1);",");"-";""))
Ci-dessous 4 tests
Regarde la pièce jointe 1170444
Impeccable aussi je viens de tester cela ... top Merciiiiiiiiiiii
 

Staple1600

XLDnaute Barbatruc
Bonjour le fil

@INFINITY100
Si tu disposes de ces fonctions
(PS: Quelle est ta version d'Excel : 2016 ou 365 ?)
En B3
VB:
=SIERREUR(SUPPRESPACE(TEXTE.AVANT(TEXTE.APRES(A3;{"type";"types"};;1);","));"-")
En C3
Code:
=SUBSTITUE(SUPPRESPACE(SIERREUR(TEXTE.APRES(A3;":";;1);"-"));".";"")

Ces deux formules renvoient les mêmes résultats que ceux de ton dernier fichier exemple.

Questions:
Est-ce que dans les cellules, il n'y aura toujours qu'une seule fois le caractère : ?
Est-ce qu'il y aura toujours uniquement trois informations séparées par une virgule ?
(comme sur ce schéma)
abc, efg type hij, Immatri* : XX 12 et YY 34
 

Staple1600

XLDnaute Barbatruc
Re

Juste pour le fun et pour occuper mon Gestionnaire ;)
LAMBDAFin.png

Ce qui réduit les formules à
En B3
=EX(A3;2;{"type";"types"})
En C3
=EX(A3;3;":")

Ci-dessous la formule pour les ceusses qui voudraient tester
Code:
=LAMBDA(Plage;Col;Sep;SIERREUR(SUBSTITUE(SUPPRESPACE(TEXTE.APRES(INDEX(FRACTIONNER.TEXTE(Plage;",");1;Col);Sep;;1));".";"");"-"))

Comme ils disaient jadis, avant le temps de Vincent.
"Je peux éteindre mon tableur et reprendre une activité normale"

ou plutôt.. rester dans mon tableur et reprendre mon activité dans un autre fil ;) [Me connaissant, il y a peu de chance que je ferme mon Excel, surtout en week-end] ;)[
 

Staple1600

XLDnaute Barbatruc
Re

J'avais oublié de sortir mon PQ. ;)

Je lance un appel aux PowerQueristes du forum
Je suis sûr qu'il y a plus beau et plus sobre, non ?
PowerQuery:
let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Désignation", type text}}),
    #"Fractionner la colonne par délimiteur" = Table.SplitColumn(#"Type modifié", "Désignation", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Désignation.1", "Désignation.2", "Désignation.3"}),
    #"Type modifié1" = Table.TransformColumnTypes(#"Fractionner la colonne par délimiteur",{{"Désignation.1", type text}, {"Désignation.2", type text}, {"Désignation.3", type text}}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Type modifié1",{"Désignation.1"}),
    #"Fractionner la colonne par délimiteur1" = Table.SplitColumn(#"Colonnes supprimées", "Désignation.3", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Désignation.3.1", "Désignation.3.2"}),
    #"Type modifié2" = Table.TransformColumnTypes(#"Fractionner la colonne par délimiteur1",{{"Désignation.3.1", type text}, {"Désignation.3.2", type text}}),
    #"Colonnes supprimées1" = Table.RemoveColumns(#"Type modifié2",{"Désignation.3.1"}),
    #"Texte en minuscules" = Table.TransformColumns(#"Colonnes supprimées1",{{"Désignation.2", Text.Lower, type text}}),
    #"Valeur remplacée" = Table.ReplaceValue(#"Texte en minuscules","types","$",Replacer.ReplaceText,{"Désignation.2"}),
    #"Valeur remplacée1" = Table.ReplaceValue(#"Valeur remplacée","type","$",Replacer.ReplaceText,{"Désignation.2"}),
    #"Fractionner la colonne par délimiteur2" = Table.SplitColumn(#"Valeur remplacée1", "Désignation.2", Splitter.SplitTextByDelimiter("$", QuoteStyle.Csv), {"Désignation.2.1", "Désignation.2.2"}),
    #"Type modifié3" = Table.TransformColumnTypes(#"Fractionner la colonne par délimiteur2",{{"Désignation.2.1", type text}, {"Désignation.2.2", type text}}),
    #"Colonnes supprimées2" = Table.RemoveColumns(#"Type modifié3",{"Désignation.2.1"}),
    #"Majuscule à chaque mot" = Table.TransformColumns(#"Colonnes supprimées2",{{"Désignation.2.2", Text.Proper, type text}}),
    #"Texte nettoyé" = Table.TransformColumns(#"Majuscule à chaque mot",{{"Désignation.2.2", Text.Clean, type text}, {"Désignation.3.2", Text.Clean, type text}})
in
    #"Texte nettoyé"
• Résultat obtenu avec PowerQuery ci-dessous

Result_PQ.png
 

INFINITY100

XLDnaute Occasionnel
Bonjour le fil

@INFINITY100
Si tu disposes de ces fonctions
(PS: Quelle est ta version d'Excel : 2016 ou 365 ?)
En B3
VB:
=SIERREUR(SUPPRESPACE(TEXTE.AVANT(TEXTE.APRES(A3;{"type";"types"};;1);","));"-")
En C3
Code:
=SUBSTITUE(SUPPRESPACE(SIERREUR(TEXTE.APRES(A3;":";;1);"-"));".";"")

Ces deux formules renvoient les mêmes résultats que ceux de ton dernier fichier exemple.

Questions:
Est-ce que dans les cellules, il n'y aura toujours qu'une seule fois le caractère : ?
Est-ce qu'il y aura toujours uniquement trois informations séparées par une virgule ?
(comme sur ce schéma)
abc, efg type hij, Immatri* : XX 12 et YY 34
bonjour Staple1600 merci pour les efforts et tes formules sont toutes impeccables

et réponse à tes questions

oui les cellules n'auront toujours qu'une seule fois le caractère :

et pour la deuxième question

non il se pourrai qu'il y ai plus de trois informations séparées par une virgule

et la troisième je suis sur excel 2016 :)
 

Hasco

XLDnaute Barbatruc
Repose en paix
Re,

Et si.... mais pas sérieux, cela ne correspondra sans doute pas à ce que le demandeur aura, mais dans les données il n'y a pas suffisamment de cas différents et on peut tout imaginer.
VB:
let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content][[Désignation]],
    Transformations = Table.AddColumn(Source, "Personnalisé", each [
                            Type = "Type " & Text.BetweenDelimiters([Désignation], "type ", ", "),
                            Immatriculation = Text.AfterDelimiter([Désignation], ", ", {0, RelativePosition.FromEnd})
                            ]),
    #"Retour en table" = Table.ExpandRecordColumn(Transformations, "Personnalisé", {"Type", "Immatriculation"}, {"Type", "Immatriculation"})
in
    #"Retour en table"

imaginer une amélioration comme celle-ci par exemple :
VB:
Transformations = Table.AddColumn(Source, "Personnalisé", each 
                            let Txt = Text.Lower([Désignation]) 
                            in [
                                Type = "Type " & Text.Trim(Text.BetweenDelimiters([Désignation], "type", ", ")), 
                                Immatriculation = Text.AfterDelimiter([Désignation], ", ", {0, RelativePosition.FromEnd})
                            ]   ),

cordialement
 

Pièces jointes

  • Staple1600 - Exemple.xlsm
    10 KB · Affichages: 4
Dernière édition:

Staple1600

XLDnaute Barbatruc
Re, Bonjour @Hasco

Echange de bon procédés ;)
Pendant que tu faisais dans ton M, j'étais dans mon VBE
C'est fait, je poste
VB:
Sub Pour_Hasco()
vData = Array("Désignation", _
"Travaux d’expertise pour éventuelle prorogation moteur, (Fin de ressource) de deux voitures type Renault, Immatriculés : RN-16     et     RN-14.", _
"Travaux d’expertise pour éventuelle prorogation moteur, (Fin de ressource) de deux voitures tYPe Apha Romeo, ImmaTRIculés :        RN-16 et RN-14.", _
"Travaux d’expertise pour éventuelle prorogation moteur, (Fin de ressource) de deux voitures types Renault,", _
"Travaux d’expertise pour éventuelle prorogation moteur, (Fin de ressource) de deux voitures    ,      IMMATRICULÉS : DB4GT Zagato .")
[A2].Resize(5).Value = Application.Transpose(vData)
ActiveSheet.ListObjects.Add(xlSrcRange, ActiveSheet.[A2].CurrentRegion, , xlYes).Name = "Tableau1"
End Sub

Du coup, j'ai un Tableau1 tout neuf pour tester ta requête
 

Discussions similaires