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

[INFINITY100]

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

 

[fanfan38]

Bonjour
J'ai corrigé le problème ...

A+ François

 

[Staple1600]

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))

 

[Staple1600]

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

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]

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é

=SUPPRESPACE(SUBSTITUE(FRACTIONNER.TEXTE(TEXTE.APRES(TEXTE.APRES(A3;",";1);{"type";"types"};;1);",");"-";""))

Ci-dessous 4 tests

 

[Staple1600]

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 à

=LAMBDA(Plage;Pattern;FILTRE.XML("<a><b>"&SUBSTITUE(Plage;",";"</b><b>")&"</b></a>";Pattern))

• 1ère extraction

=SEPA(A1;"//b[contains(following::*[1],':')]")

renvoie : (Fin de ressource) de deux voitures type - Renault
• 2ième extraction

=SEPA(A2;"//b[contains(preceding::*[1],'y')]")

renvoie : Immatriculés : RN-16 et RN-14.


Pour affiner la 1ère extraction

STXT(SEPA(A3;"//b[contains(following::*[1],':')]");CHERCHE("typ";SEPA(A3;"//b[contains(following::*[1],':')]"));999)

renvoie : type - Renault

 

[INFINITY100]

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.

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

=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

 

[INFINITY100]

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é

=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

 

[mapomme]

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

Oui ça fait très bien l'affaire

 

[Staple1600]

Bonjour le fil

@INFINITY100
Si tu disposes de ces fonctions
(PS: Quelle est ta version d'Excel : 2016 ou 365 ?)
En B3

=SIERREUR(SUPPRESPACE(TEXTE.AVANT(TEXTE.APRES(A3;{"type";"types"};;1);","));"-")

En C3

=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]

Re

Juste pour le fun et pour occuper mon Gestionnaire

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

=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]

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 ?

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

 

[INFINITY100]

Bonjour le fil

@INFINITY100
Si tu disposes de ces fonctions
(PS: Quelle est ta version d'Excel : 2016 ou 365 ?)
En B3

=SIERREUR(SUPPRESPACE(TEXTE.AVANT(TEXTE.APRES(A3;{"type";"types"};;1);","));"-")

En C3

=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]

Bonjour

Je lance un appel aux PowerQueristes du forum

...

Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],

Pas de tableau1 pas de requête.

 

[Hasco]

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.

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 :

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

 

[Staple1600]

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

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