Discussion :

[S7ylishNoob]

Bonjour à tous,

Malgré mes recherches sur Google, je ne trouve pas la réponse à mon problème (je dois mettre les mauvais mots clefs....) et sollicite donc votre aide.

Mon but est de reconnaître des séries de phrases dans une cellule et de les retranscrire sur une autre cellule.

Voici un exemple d'une seule cellule:
"Pasteurella multocida H1N7 réf xx
Riemerella anatipestifer Sérotype 9 réf xxxxxxxxx
Riemerella anatipestifer Sérotype 1 réf xxxx"
où les xxxx après "réf" correspondent à des lettres ou des chiffres qui varient en nombre.

De là je veux récupérer (et aligner si plusieurs) le chiffre après sérotype de Riemerella anatipestifer seulement. Ici, je veux donc avoir "9; 1; ".

J'ai trouvé une formule compliquée qui rend difficile l'ajout et le contrôle de nouveaux Sérotypes:
=SI(NB.SI(Z76;"*Riemerella anatipestifer Sérotype A*");"A; ";"")&SI(NB.SI(Z76;"*Riemerella anatipestifer Sérotype 1 *");"1; ";"")&SI(NB.SI(Z76;"*Riemerella anatipestifer Sérotype 5*");"5; ";"")&SI(NB.SI(Z76;"*Riemerella anatipestifer Sérotype 6*");"6; ";"")&SI(NB.SI(Z76;"*Riemerella anatipestifer Sérotype 9*");"9; ";"") et ca continue....

Je voudrais donc travailler à partir d'un tableau (sur une autre feuille) qui compilerait valeur à rechercher et la valeur à écrire et je ne trouve pas la formule qu'il faut pour comparer la cellule avec la première colonne du tableau et renvoyer tous les chiffres/ lettres qu'il faut...

Exemple du tableau en question:
Nom ||Renvoie
Riemerella anatipestifer Sérotype A ||A;
Riemerella anatipestifer Sérotype 1 ||1;
Riemerella anatipestifer Sérotype 9 ||9;
... ||...


D'autres exemples :
"Riemerella anatipestifer Sérotype A réf xxxxxxxx
Pasteurella multocida H1N(-) réf xxx"
Je veux avoir: "A; "

"Escherichia coli O78K80 réf xxxxxxxxxxxx
Pasteurella multocida H3N7,8,9 réf xxxxxxxxxxx
Riemerella anatipestifer Sérotype 1 réf xxx
Riemerella anatipestifer Sérotype A réf xxxxxxxxxxxxx"
Je veux avoir: "1; A; "

J'espère être assez clair.

Merci d'avance pour votre aide!

[NVCfrm]

Bonjour à tous,

Malgré mes recherches sur Google, je ne trouve pas la réponse à mon problème (je dois mettre les mauvais mots clefs....) et sollicite donc votre aide.

Mon but est de reconnaître des séries de phrases dans une cellule et de les retranscrire sur une autre cellule.

Voici un exemple d'une seule cellule:
"Pasteurella multocida H1N7 réf xx
Riemerella anatipestifer Sérotype 9 réf xxxxxxxxx
Riemerella anatipestifer Sérotype 1 réf xxxx"
où les xxxx après "réf" correspondent à des lettres ou des chiffres qui varient en nombre.

De là je veux récupérer (et aligner si plusieurs) le chiffre après sérotype de Riemerella anatipestifer seulement. Ici, je veux donc avoir "9; 1; ".

J'ai trouvé une formule compliquée qui rend difficile l'ajout et le contrôle de nouveaux Sérotypes:
=SI(NB.SI(Z76;"*Riemerella anatipestifer Sérotype A*");"A; ";"")&SI(NB.SI(Z76;"*Riemerella anatipestifer Sérotype 1 *");"1; ";"")&SI(NB.SI(Z76;"*Riemerella anatipestifer Sérotype 5*");"5; ";"")&SI(NB.SI(Z76;"*Riemerella anatipestifer Sérotype 6*");"6; ";"")&SI(NB.SI(Z76;"*Riemerella anatipestifer Sérotype 9*");"9; ";"") et ca continue....

Je voudrais donc travailler à partir d'un tableau (sur une autre feuille) qui compilerait valeur à rechercher et la valeur à écrire et je ne trouve pas la formule qu'il faut pour comparer la cellule avec la première colonne du tableau et renvoyer tous les chiffres/ lettres qu'il faut...

Exemple du tableau en question:
Nom ||Renvoie
Riemerella anatipestifer Sérotype A ||A;
Riemerella anatipestifer Sérotype 1 ||1;
Riemerella anatipestifer Sérotype 9 ||9;
... ||...


D'autres exemples :
"Riemerella anatipestifer Sérotype A réf xxxxxxxx
Pasteurella multocida H1N(-) réf xxx"
Je veux avoir: "A; "

"Escherichia coli O78K80 réf xxxxxxxxxxxx
Pasteurella multocida H3N7,8,9 réf xxxxxxxxxxx
Riemerella anatipestifer Sérotype 1 réf xxx
Riemerella anatipestifer Sérotype A réf xxxxxxxxxxxxx"
Je veux avoir: "1; A; "

J'espère être assez clair.

Merci d'avance pour votre aide!

salut,
Clair ? Du tout !
C'est une profusion d'explications qui finit par m'embrouiller.
Peux tu expliquer simplement le problème ?

[papouclo]

Bonjour,
L’image ci-dessous montre ce qui peut s’obtenir par formule en supposant que le nombre d’occurrences du texte cherché dans les cellules n’est pas supérieur à 3.

Les cellules contenant les textes dans lesquels s’effectue la recherche pour extraction sont en colonne A de A1 à A4 (A1, A2 et A3 sont les exemples donnés par S7ylishNoob ; A4 est un exemple créé pour avoir 3 occurrences du texte cherché).
La cellule C1 contient le texte recherché (le dernier caractère est un espace pour avoir exactement la chaine située avant le caractère –chiffre ou lettre- à extraire).
La formule en E1 est :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
=CHOISIR((NBCAR(A1)-NBCAR(SUBSTITUE(A1;$C$1;"")))/NBCAR($C$1);
STXT(A1;CHERCHE($C$1;A1)+NBCAR($C$1);1)&"; ";
STXT(A1;CHERCHE($C$1;A1)+NBCAR($C$1);1)&"; " & STXT(A1;CHERCHE($C$1;A1;CHERCHE($C$1;A1)+1)+NBCAR($C$1);1)&"; ";
STXT(A1;CHERCHE($C$1;A1)+NBCAR($C$1);1)&"; " & STXT(A1;CHERCHE($C$1;A1;CHERCHE($C$1;A1)+1)+NBCAR($C$1);1)&"; " & STXT(A1;CHERCHE($C$1;A1;CHERCHE($C$1;A1;CHERCHE($C$1;A1)+1)+1)+NBCAR($C$1);1)&"; ")

à recopier vers le bas en E2 :E4.
Si l’on est sûr que le nombre d’occurrences ne peut dépasser 2, la formule se simplifiera bien sûr.
SI le nombre d’occurrences du texte cherché dans une cellule peut dépasser 3, il vaut mieux abandonner l’idée de formule et chercher une fonction personnalisée.
Cordialement
Claude

[S7ylishNoob]

Bonjour Claude,

Merci pour le temps passé sur mon problème, que tu as bien cerné. Je n'avais pas pensé à cette formule, cependant, je peux avoir jusqu'à 6 occurrences au sein d'une même cellule. De plus, le nombre de lettres/chiffres à extraire varient entre 1 et 6... Par exemple pour "Escherichia coli O78K80" et "Escherichia coli O1" où je veux extraire O78K80 et O1 respectivement.
J'ai déjà une formule personnalisée, mais est longue compliqué: =SI(NB.SI(Z76;"*Riemerella anatipestifer Sérotype A*");"A; ";"")&SI(NB.SI(Z76;"*Riemerella anatipestifer Sérotype 1 *");"1; ";"")&SI(NB.SI(Z76;"*Riemerella anatipestifer Sérotype 5*");"5; ";"")...
D'où mon idée de faire un tableau avec une formule qui compare la cellule avec toutes les lignes du tableau. Maintenant, il se peut que cela ne soit pas possible et qu'une formule plus simple existe.

@NVCfrm: En essayant de prendre un exemple plus facile avec des pays et des villes. J'ai des cellules qui listent une centaine de villes dans une dizaine de pays différents. De là, je veux extraire toutes les villes de France avec la possibilité de les renommer (si possible).

A - Tableau 1	B - Tableau 1	||	E - Tableau 2	F - Tableau 2
Liste	Ce que je veux avoir	||	Mots à rechercher en A1	Mots cléfs à mettre en B1
FRANCE Paris FRANCE Lyon FRANCE Marseille	Par; Ly; Mars;	||	FRANCE Paris	Par;
FRANCE Paris ESPAGNE Madrid FRANCE Lyon ITALIE Rome	Par; Ly;	||	FRANCE Lyon	Ly;
FRANCE Lyon ALLEMAGNE Berlin ANGLETERRE Londres	Ly;	||	FRANCE Marseille	Mars;

Clément

[NVCfrm]

Salut.
C'est assez clair.
D'une clarté telle que sa solution apparaît dans sa description !
Ça demande de travailler sur la conception comme tu as dû t'en douter.

Pour simplifier le calcul, il te faut créer de nouveaux paramètres constants en mémoire avec une formule ou sur la feuille.

La fonction SI+INDEX est redoutablement efficace.
Tu as raison, elle peut-être très simplifiée.

[S7ylishNoob]

Bonjour NVCfrm,

Peux tu développer ton idée sur le SI+INDEX? J'avoue être assez nul avec la fonction INDEX. :p

Merci!
Clément

[papouclo]

Bonjour,
Dans ta réponse à mon post, la formule personnalisée dont tu parles n'a rien à voir avec ce que j'ai appelé une fonction personnalisée.
Il s'agit d'un programme en VBA qui renvoie une valeur dans la cellule où on l'utilise exactement comme les fonctions basiques d'Excel.
Voici une fonction appelée extract écrite pour ton problème (à écrire dans un module de l'editeur Visual Basic)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Function extract(celcible As Range, celref As Range) As String
Dim chref As String, cible As String
Dim nboccurences As Long, debut As Long, fin As Long, longueur As Long
 
    chref = Trim(celref.Value) & " "
    cible = Trim(celcible.Value) & " "
    nboccurences = (Len(cible) - Len(Replace(cible, chref, ""))) / Len(chref)
    cible = Replace(cible, c, href, "|")
 
    debut = 1
    For i = 1 To nboccurences
        debut = InStr(debut, cible, "|", vbTextCompare) + 1
        fin = InStr(debut, cible, " ", vbTextCompare)
        longueur = fin - debut
        extract = extract & Mid(cible, debut, longueur) & " ; "
    Next i
End Function

On peut ensuite utiliser cette fonction extract pour écrire des formules permettant d'obtenir ce que tu cherches (voir l'image ci-dessous)

La formule écrite dans B2 est :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=extract($A2;B$1)

Cette formule peut se recopier en s'incrémentant sur toute la plage B2:C5.
Cordialement
Claude