XL 2019 Recherche mots dans cellule et résultat dans cellule avec séparateur

[Marjorie73]

Bonjour,

Je me permets de revenir vers la communauté pour une demande de formule.

Dans ma feuille « product », dans la colonne F, j’ai une description de produit.
Dans ma feuille « Dico », j’ai une liste de mots clés.


J’essaie que dans la colonne T de la feuille « product », il soit indiqué les mots clés (si ils existent) trouvé dans la cellule en colonne F.
Chaque mot clé étant séparé (sans espace) par le caractère |

J’ai essayé une multitude de formules mais sans succès.

Est=ce que quelqu’un aurait une formule qui me permettrait d’arriver au résultat que je souhaite.

Je vous remercie.

 

[Cousinhub]

Hello,
... on peut aussi facilement prendre en compte plusieurs types de séparateurs ( espace, virgule, point-virgule, point) en remplaçant dans sa requête PowerQuery T_Final le Text.Split par un Splitter.SplitTextByAnyDelimiter
Exemple :

Intersect = Table.AddColumn(TypeText, "Mots_Cles",
                each List.Intersect({Splitter.SplitTextByAnyDelimiter({" ",",",";","."})([nombre]),
                T_Liste[Liste]})),

Ami calmant, J.P

Re-,
Je vais même adopter ta modification, car il y avait certains cas (comme le mentionne Job75 dans ce fil), où le séparateur n'est pas forcément un espace.
Voici donc le fichier modifié (incluant le code de mise à jour)
Bonne journée

 

[mapomme]

Re,

Une version v3 plus simple et qui accepte les catégories comprenant des séparateurs y compris l'espace. Exemple de catégories :

Cat15

M

L

Categ 9

#-00 A1+

Avec Espace

La base du code est une fonction ListeCat à trois paramètres :
ListeCat( Mot , rangeDico , sansDoublon)

1. Mot est le mot au sein duquel on recherche les catégories
2. rangeDico est la plage où se trouvent les codes. C'est un range. Cette plage peut contenir des cellules vides ou avec seulement des espaces. De telles cellules seront ignorées.
3. sansDoublon est un paramètre optionnel. Si cet argument est absent alors la liste résultat peut contenir des doublons. Si cet argument est présent alors la liste résultat ne comprendra pas les doublons.

Listecat renvoie une chaine de caractères avec les catégories figurant dans mot. Les catégories sont séparées par "|". S'il n'y a aucune catégorie alors on retourne la chaine vide.

Cette fonction peut s'utiliser sur une feuille de calcul ou bien dans un code VBA.

Dans le classeur :

• on trouve un bouton pour remplir en une seule fois la colonne T avec possibilité de choisir avec ou sans doublon
• une formule en colonne U pour une liste avec doublon
• une formule en colonne V pour une liste sans doublon

Le code de la fonction ListeCat dans module1 :

Function ListeCat(ByVal Mot, rangeDico As Range, Optional sansDoublon)
Const separ = " !""#$€%&'()*+,-./:;<=>?@[\]{|}¤"
Dim cat, lencat&, i&, x, r
   ListeCat = Empty: Mot = " " & Mot & " "
   For Each cat In rangeDico.Value
      If Len(Trim(cat)) > 0 Then
         lencat = Len(Trim(cat))
         For i = 2 To Len(Mot) - 1
            x = Mid(Mot, i, lencat)
            If x = cat Then
               If InStr(separ, Mid(Mot, i - 1, 1)) * InStr(separ, Mid(Mot, i + lencat, 1)) <> 0 Then
                  r = r & "|" & cat
                  If Not IsMissing(sansDoublon) Then Exit For
                  i = i + lencat
               End If
            End If
         Next i
      End If
   Next cat
   ListeCat = Mid(r, 2)
End Function

 

[job75]

Bonsoir,

J'ai hésité à revenir compte tenu de l'excellent travail de mapomme.

Mais finalement cette solution est quand même différente :

Public d As Object 'mémorise la variable

Function Cles$(txt$, Liste As Range, sep$, Optional exclu$, Optional unique As Boolean)
Dim c As Range, L%, i%, e, test As Boolean
If d Is Nothing Then 'évite le recalcul à chaque appel de la fonction
    Set d = CreateObject("Scripting.Dictionary")
    For Each c In Liste
        d(CStr(c)) = ""
    Next c
End If
L = Len(exclu)
If L Then For i = 1 To L: txt = Replace(txt, Mid(exclu, i, 1), ""): Next i
For Each e In Split(txt)
    If d.exists(e) Then
        If unique Then test = InStr(Cles & sep, sep & e & sep) = 0 Else test = True
        If test Then Cles = Cles & sep & e
   End If
Next e
Cles = Mid(Cles, 2)
End Function

La macro dans le code de la feuille "Dico" :

Private Sub Worksheet_Change(ByVal Target As Range)
Set d = Nothing 'RAZ
Sheets("product").Calculate
End Sub

A+

 

[jurassic pork]

Hello,
Excellent travail Job75 mais il y a un cas de figure où le code risque de ne pas fonctionner : le cas où il n'y a pas d'espace entre un séparateur autre que l'espace et le mot suivant (ce qui constitue une faute de français sauf pour le ; voir ci-dessous ) exemple Cat1,Cat2 Cat1;Cat2
Il serait alors peut-être préférable de remplacer les séparateurs à exclure par des espaces plutôt que par rien :

If L Then For i = 1 To L: txt = Replace(txt, Mid(exclu, i, 1), " "): Next i

Je rappelle certaines règles de français :

Les signes de ponctuation simples sont : la virgule , le point . les points de suspension ...
Règle en français : Pas d'espace avant, une espace après
Les signes de ponctuation doubles sont : le point-virgule ; les deux points : le point d'exclamation ! le point d'interrogation ? les tirets - ... - (à ne pas confondre avec le tiret des mots composés alors sans espace)
Règle en français : Une espace avant, une espace après sauf pour le point-virgule où l'espace est facultative.
et pourquoi une espace : On peut utiliser le mot espace au féminin ou au masculin quand il est employé au sens de « blanc entre deux mots » et qu’il n’est pas suivi d’un adjectif :
Exemples : Il faut mettre une espace devant le deux-points. Ajoutez un espace entre ces deux mots.
Par contre, quand espace est suivi d’un adjectif, comme dans les expressions espace fine ou espace insécable, on l’emploie toujours au féminin.

Ami calmant, J.P

 

[job75]

Bonjour jurassic pork, le forum,

mais il y a un cas de figure où le code risque de ne pas fonctionner : le cas où il n'y a pas d'espace entre un séparateur autre que l'espace et le mot suivant

Bien sûr et il est inutile de s'en préoccuper, les possibilités d'erreur de frappe sont multiples.

A+

 

[job75]

Pour tester j'ai recopié en feuille "product" la plage 2:9 sur 32 000 lignes.

Les formules se recalculent chez moi en 1,3 seconde.