Retrouver un mot d'une cellule dans une colonne

Bonjour, j'ai deja regarde differents forum, mais je ne suis jamais tombe sur la question suivante:

Colonne A: Liste de Noms

Colonne B: Liste de communication bancaire libre, format texte, avec des mots, chiffres, dates, et peut etre un des noms de la colonne A. Ce nom peut etre soit une fois dans une cellule, soit plusieurs fois dans plusieurs celleules de la colonne B.

Je voudrais avoir dans la colonne C1 une formule qui va chercher le premier non en A1 et qui verifie si ce nom est quelque part dans une des cellule de la colonne B, avec le resultat 1 ou 0, trouve ou pas, et eventuellement me donner aussi dans quelle cellule de la colonne B ce nom se trouve.

J'ai trouve comment faire avec une formule si un des parametres est "nom" (format texte comme "Hollande", mais pas avec une cellule de reference come "A1".

Je ne sais pas si des formules peuvent etre utilisees ici, ou un code VBA (je ne connais pas VBA...)

Voila, un grand merci d'avance a ceux qui prendront un peu de temps a chercher...

Bonne journee !

bonjour,

une solution avec une fonction personnalisée

appeler la fonction

=findstr(mot à chercher, range de cellules où chercher)

Function findstr(mot As String, r As Range) As String
For Each c In r
If InStr(c.Value, mot) <> 0 Then findstr = c.Address: Exit Function
Next
findstr = "mot non trouvé"
End Function

Bonjour,

Tu peux tester en C1

=SI(NB.SI($B:$B;"*" & A1 & "*")>0;ADRESSE(LIGNE(INDEX($B:$B;EQUIV("*" & A1 & "*";$B:$B;0)));2);"Non trouvé")

A+

Merci frangy et h2so4. Puis je abuser? frangy, comme j'ai excel en anglais, pourrais tu m edonner la formule avec les fonctions en anglais? h2s04, ca marche super, mais la cellule resultat n'est pas la premiere en partant du haut... Est il possible soit d'avoir la premiere cellule ou le trouve le nom, ou d'avoir toutes les differentes cellules ou se trouve ce nom? ou les deux ?

En tout cas, deja merci pour cela...

Bonjour,

Je ne parviens pas à reproduire l'erreur que tu mentionnes. La fonction renvoie la première cellule du rangequi contient le mot. Sinon merci de m'envoyer ton fichier avec le cas qui ne fonctionne pas.

ci-dessous la fonction adaptée pour donner la liste de toutes les cellules (du range donné en paramètre) qui contienne le mot recherché.

Function findstr(mot As String, r As Range) As String

For Each c In r
If InStr(c.Value, mot) <> 0 Then st = st & sep & c.Address: If sep = "" Then sep = " - "
Next
If st <> "" Then
 findstr = st
Else
 findstr = "mot non trouvé"
End If
End Function

je vais tout de suite demander ca en plus alors, si c'est possible:

Quand un nom est trouve par la formule findstr (), voila le resultat "$K$620". Parfait.

Comme je disais avant, si je peux avoir l'adresse de la premiere cellule de la liste, ca serait impec.

Et si aussi possible d'avoir comme resultat, si K620 est la premiere cellule de la liste:

$K$620; 9/30/2013; 80

ou $K$620 est l'adresse de la cellule, 9/30/2013 la date qui est dans une autre colonne N "date valeur", mais aussi rangee 620; 80 une autre colonne O "Montant" aussi rangee 620?

Ca serait vraiment top, car c'est la formule dont j'ai besoin

Bonjour,

voici la fonction adaptée pour donner le complément d'info souhaité.

par contre, je te confime que la fonction renvoie l'adresse de toutes les cellules (du range donné en paramètre) qui contiennent le mot recherché. le mot doit être orthographié et écrit de la même façon (y compris majuscules, minuscules), sinon il n'est pas sélectionné.

Function findstr(mot As String, r As Range) As String
For Each c In r
If InStr(c.Value, mot) <> 0 Then st = st & sep & c.Address & "; " & Cells(c.Row, "N") & "; " & Cells(c.Row, "O"): If sep = "" Then sep = " - "
Next
If st <> "" Then
 findstr = st
Else
 findstr = "mot non trouvé"
End If
End Function

Genial, merci beaucoup! voila le resultat: $K$245; 3/4/2013; 40 - $K$603; 10/17/2012; 75 - $K$818; 3/20/2012; 75

Tres bonne journee, j'espere que ca servira a d'autres.

A bientot sur ce forum, tres bonne adresse

Rebonjour, en applicant la formule, je dois encore faire une demande pour qu'elle fonctionne a 100%.

=> Comme des personnes ont bien sur le meme nom mais sont de famille differente, je dois chercher dans ma colonne deux parametres: le nom (colonne I) et le prenom (colonne J)

La fonction renvoie "mot trouve" si elle trouve le mot aussi faisant partie d'un mot plus long:

Ex; MAES est trouve dans MAESTRO, mais n'a rien a voir.

=> Peut on limiter a la recherche "exacte" du "mot"?

La fonction m'a renvote "mot non trouve" si le nom etait en Majuscule, mais en minuscule dans le collonne de recherche

=> Peut on faire en sorte que la fonction recherche le mot quelque soit l'ecriture?

En resume: si on peut ajouter un critere (prenom) au critere "nom", chercher le mot exact uniquement et quelque soit les caracteres untilises (Maj ou Min), ca serait parfait.

Merci et bonne journee !!

Bonjour,

une solution à tester

appeler la fonction findstring(mot1,mot2,range)

mot1 mot à trouver dans range

mot2 mot à trouver en colonne J si mot1 trouvé

range ensemble des cellules où chercher mot1

Function findstr(mot1 As String, mot2 As String, r As Range) As String
For Each c In r
mottrouvé = sm(UCase(mot1), UCase(c.Value))
If motrouvé Then mottrouvé = sm(UCase(mot2), UCase(Cells(c.Row, "J")))
If mottrouvé Then st = st & sep & c.Address & "; " & Cells(c.Row, "N") & "; " & Cells(c.Row, "O"): If sep = "" Then sep = " - "
Next
If st <> "" Then
 findstr = st
Else
 findstr = "mot non trouvé"
End If
End Function

Private Function sm(mot, source) As Boolean
q = InStr(source, mot)
mottrouvé = False
If q <> 0 Then
  mottrouvé = True
  If q + Len(mot) < Len(source) Then
   If InStr(" ,.;:-", Mid(source, q + Len(mot), 1)) = 0 Then mottrouvé = False
  End If
  If q > 1 Then
   If InStr(" ,.;:-", Mid(source, q - 1, 1)) = 0 Then mottrouvé = False
  End If
End If
sm = mottrouvé
End Function

Merci beaucoup! Ca marche Impec !!

Rebonjour, je me suis apercu que la formule donnait un resultat un peu bizarre quand on avait le cas suivant:

NOM1 PRENOM1

NOM1 PRENOM2

Exemple ci dessous:

$I$10; 40,00; EUR - $I$12; 80,00; EUR

Le resultat est le meme pour les deux lignes:

NOM1 PRENOM1 $I$10; 40,00; EUR - $I$12; 80,00; EUR

NOM1 PRENOM2 $I$10; 40,00; EUR - $I$12; 80,00; EUR

La combinaison NOM1 PRENOM1 est unique dans mes colonnes, donc le resultat devrait etre: $I$10; 40,00; EUR

La valeur a renvoyer pour la ligne NOM1 PRENOM1 est dans la cellule I10

La valeur a renvoyer pour la ligne NOM1 PRENOM2 est dans la cellule I12

Merci.

Bonjour,

une faute de frappe dans le code précédent,

essaie ceci

Function findstr(mot1 As String, mot2 As String, r As Range) As String
For Each c In r
mottrouvé = sm(UCase(mot1), UCase(c.Value))
If mottrouvé Then mottrouvé = sm(UCase(mot2), UCase(Cells(c.Row, "J")))
If mottrouvé Then st = st & sep & c.Address & "; " & Cells(c.Row, "N") & "; " & Cells(c.Row, "O"): If sep = "" Then sep = " - "
Next
If st <> "" Then
 findstr = st
Else
 findstr = "mot non trouvé"
End If
End Function

Private Function sm(mot, source) As Boolean
q = InStr(source, mot)
mottrouvé = False
If q <> 0 Then
  mottrouvé = True
  If q + Len(mot) < Len(source) Then
   If InStr(" ,.;:-", Mid(source, q + Len(mot), 1)) = 0 Then mottrouvé = False
  End If
  If q > 1 Then
   If InStr(" ,.;:-", Mid(source, q - 1, 1)) = 0 Then mottrouvé = False
  End If
End If
sm = mottrouvé
End Function

Desole, maintenant la formule donne "mot non trouvé" pour ces cellules. merci !!

Bonsoir,

en testant davantage, j'ai encore trouvé une erreur. elle n'explique cependant pas le cas que tu mentionnes.

peux-tu me communiquer un cas qui ne fonctionne pas ?

les données et l'appel de la fonction.

merci

Function findstr(mot1 As String, mot2 As String, r As Range) As String
For Each c In r
mottrouvé = sm(UCase(mot1), UCase(c.Value))
If mottrouvé Then mottrouvé = sm(UCase(mot2), UCase(Cells(c.Row, "J")))
If mottrouvé Then st = st & sep & c.Address & "; " & Cells(c.Row, "N") & "; " & Cells(c.Row, "O"): If sep = "" Then sep = " - "
Next
If st <> "" Then
 findstr = st
Else
 findstr = "mot non trouvé"
End If
End Function

Private Function sm(mot, source) As Boolean
q = InStr(source, mot)
mottrouvé = False
If q <> 0 Then
  mottrouvé = True
  If q + Len(mot) <= Len(source) Then
   If InStr(" ,.;:-", Mid(source, q + Len(mot), 1)) = 0 Then mottrouvé = False
  End If
  If q > 1 Then
   If InStr(" ,.;:-", Mid(source, q - 1, 1)) = 0 Then mottrouvé = False
  End If
End If
sm = mottrouvé
End Function

La formule ne marche plus, voila un example

Cellule P414 (nom): Brasxxxxxxx

Cellule Q414 (prenom): Eric

Dans une des cellule de la colonne I, en I10 j'ai le texte:

"Cotisation Eric Brasxxxxxxx"

Dans ma cellule de calcul:

Formule: =findstr(P414,Q414,I:I)

Resultat: Mot non trouvé

avec ta formule precedente:

Function findstr(mot1 As String, mot2 As String, r As Range) As String

For Each c In r

mottrouvé = sm(UCase(mot1), UCase(c.Value))

If motrouvé Then mottrouvé = sm(UCase(mot2), UCase(Cells(c.Row, "J")))

If mottrouvé Then st = st & sep & c.Address & "; " & Cells(c.Row, "N") & "; " & Cells(c.Row, "O"): If sep = "" Then sep = " - "

Next

If st <> "" Then

findstr = st

Else

findstr = "mot non trouvé"

End If

End Function

Private Function sm(mot, source) As Boolean

q = InStr(source, mot)

mottrouvé = False

If q <> 0 Then

mottrouvé = True

If q + Len(mot) < Len(source) Then

If InStr(" ,.;:-", Mid(source, q + Len(mot), 1)) = 0 Then mottrouvé = False

End If

If q > 1 Then

If InStr(" ,.;:-", Mid(source, q - 1, 1)) = 0 Then mottrouvé = False

End If

End If

sm = mottrouvé

End Function

Pour les memes cellules que precedemment, le resultat etait:

$I$10; 40 EUR; - $I$12; 80 EUR;

Car Brasxxxxx etait dans la cellule I10 et I12. Mais Eric n'est que dans la ligne 10. En ligne 12, c'est le meme nom mais un autre prenom.

Merci

Bonjour,

chez moi cette macro fonctionne parfaitement. à condition d'avoir "eric" en j10 est-ce bien le cas ?

Bonjour, j'ai peut etre fait une erreur, car j'ai bouge des colonnes...

Pourriez vous me dire quelle sont les colonnes J,N,O qui sont utilisees:

J = prenom

N =

O =

Sachant que les criteres sont donc les colonnes avec le prenom, le nom et le texte

Merci !!

Bonjour,

voici une version plus générique de la fonction. elle prend 3 paramètres supplémentaires

findstring(mot1;mot2;plage;identifiantcolonne1;identifiantcolonne2;identifiantcolonne3)

où

mot1 est le mot à rechercher dans plage

mot2 est le mot à rechercher dans la colonne identifiée par identifiantcolonne1, sur la même ligne que celle où mot1 a été trouvé

plage est la plage de cellule où chercher mot1

identifiantcolonne1 est l'identifiant de la colonne où chercher mot2

identifiantcolonne2 est l'identifiant d'une colonne dont il faut afficher le contenu si mot1 et mot2 sont trouvés

identifiantcolonne3 est l'identifiant d'une colonne dont il faut afficher le contenu si mot1 et mot2 sont trouvés

exemple d'appel

=findstr("Brassxxx","Eric";I1:i20,"J";"N";"O")

Function findstr(mot1 As String, mot2 As String, r As Range, c1 As String, c2 As String, C3 As String) As String
Application.Volatile
For Each c In r
mottrouvé = sm(UCase(mot1), UCase(c.Value))
If mottrouvé Then mottrouvé = sm(UCase(mot2), UCase(Cells(c.Row, c1)))
If mottrouvé Then st = st & sep & c.Address & "; " & Cells(c.Row, c2) & "; " & Cells(c.Row, C3): If sep = "" Then sep = " - "
Next
If st <> "" Then
 findstr = st
Else
 findstr = "mot non trouvé"
End If
End Function

Private Function sm(mot, source) As Boolean
q = InStr(source, mot)
mottrouvé = False
If q <> 0 Then
  mottrouvé = True
  If q + Len(mot) <= Len(source) Then
   If InStr(" ,.;:-", Mid(source, q + Len(mot), 1)) = 0 Then mottrouvé = False
  End If
  If q > 1 Then
   If InStr(" ,.;:-", Mid(source, q - 1, 1)) = 0 Then mottrouvé = False
  End If
End If
sm = mottrouvé
End Function

Bonjour, merci pour cela, mais je pense que je n'ai pas bien explique quelque chose, car le resultat de la formule done "mot non trouve"

Mais de pouvoir chosisir les colonnes est tres pratique.

Je cherche a trouver dans "range" (colonne I) mot1 ET mot2.

Si je les trouve, alors je veux deux valeurs qui sont specifiees dans les colonnes que je choisis maintenant.

Ex:

Dans une des cellules colonne I,

I13 = "Paiement cotisation Eric Brasxxx 2014, merci a bientot"

En P417: Brassxxx

En Q417: Eric

K13 = 50

L13 = EUR

Formule: =findstr(P417,Q417,I:I,"Q","K","L")

devrait donner un autre resultat que"mot non trouve"

Je trouve Brassxxx ET Eric dans la colonne "I" (I13) donc le resultat contient 50, EUR

Si l'exemple n'est pas assez clair, dites le moi, et MERCI encore.