aide pour trouver une formule

[caroline]

salut à tous
voilà mon problème est de récupérer un prenom dans une cellule
par exemple
jean,25ans celiba 1.70
pierre.26ans marié 1.75
jean-marc 30 celiba 1.82
alain 28 marié 1.65
vincent19celiba1.70
donc moi j'ai envie de récupérer le prénom seulement et j'ai utilise cette formule gauche(cellule1;trouve(" ";cellule1)-1)
avec cette formule je peux récupérer le prénom mais je suis obligée de changer chaque fois si le prénom est suivi d'un espace d'une virgule , d'un point etc.Donc quelqu'un pourrait il m'aider à trouver une formule qui prends en considération tout les cas possible cad un prénom suivi d'un espace , d'un chiffre , d'une virgule etc....
MERCI D AVANCE

 

[Jean-Marie]

Bonjour, Caroline

Voici une formule qui te permettra de trouver les prénoms dans tous les cas, c'est une fonction matricielle donc validée par Ctrl+Shift+Entrer, la voilà

=STXT(B2;1;PETITE.VALEUR(SI(NB.SI(A1:A27;STXT(B2;LIGNE(INDIRECT("a1:a"&NBCAR(B2)));1))=1;65536;LIGNE(INDIRECT("a1:a"&NBCAR(B2))));1)-1)

On va prendre comme exemple : jean-marc 30 celiba 1.82, (il a un nom composé comme le mien)

Le prénom ne peut-être composé de chiffre, ni de virgule de point d'espace, si c'est un prénom composé il devra avoir un trait d'union.

Je vais décomposer au maximum la formule.

Ce qu'il faut trouver en 1er, c'est le caractère qui sépare le prénom du reste, c'est dans cette partie,
NB.SI(A1:A27;STXT(B2;LIGNE(INDIRECT("a1:a"&NBCAR(B2)));1))=1
Je rappelle qu'une fonction matricielle se créer un tableau en mémoire. Le tableau sera de x éléments, pour avoir le nombre d’éléments variables j'utilise INDIRECT("a1:a"&NBCAR(B2)) qui renvoie une plage du nombre de lignes qui correspond au nombre de caractères (dans notre exemple 25, donc 25 lignes, donc le tableau sera composé de 25 données). Maintenant il faut mettre dans ce tableau les éléments, faits par STXT(B2;...;1), ce sera les caractères composés par la phrase.
Maintenant il faut comparer le contenu du tableau par rapport à des caractères valides contenu dans une plage A1:27 ayant de A à Z et le trait d'union (pour écrire le trait d'union il faudra mettre ceci dans la cellule '- ), peu importe l'ordre, et aussi que ce soit écrit en majuscule ou en minuscule.
Le NB.SI(...;...) va retourner si l'élément du tableau fait partie des caractères acceptés,
- pour la partie si oui 65536
- pour la partie non LIGNE(INDIRECT("a1:a"&NBCAR(B2)))), ce qui mettra dans un tableau2 qui contiendra 1;2;3;4;5;.... en fonction toujours du nombre de caractères contenus dans la phrase.
Tableau1 Tableau2 Retour
j 1 65536
e 2 65536
a 3 65536
n 4 65536
- 5 65536
m 6 65536
a 7 65536
r 8 65536
c 9 65536
10 10
e 11 11
e 12 12
e 13 13
(Pour voir l'effet du tableau utiliser la police courrier)

Dans la partie PETITE.VALEUR(SI(...;65536;LIGNE(INDIRECT("a1:a"&NBCAR(B2))));1)
le problème avec petite valeur, va nous retourner la plus faible valeur du Tableau 2, vous maintenant vous comprenez pourquoi 65536, donc petite valeur va renvoyer la valeur 10.

Maintenant nous avons le numéro du 1er caractère séparateur du prénom du reste. Pour récupérer le prénom =STXT(B2;1;....-1),
=STXT("jean-marc 30 celiba 1.82";depuis le début; jusqu'au caractère trouvé, mais celui-là ne nous interresse pas donc -1)

J'espère avoir donné des explications sur ce que fait cette formule.

Bonne soirée

@+Jean-Marie

 

[Ti]

Une autre solution, peut-être un peu plus simple, je crée mon tableau directement dans la formule et je ne travaille que sur le texte.

=GAUCHE(A2;MIN(SI(ESTERREUR(TROUVE({"1";"2";"3";"4";"5";"6";"7";"8";"9";";";",";".";" "};A2));NBCAR(A2);TROUVE({"1";"2";"3";"4";"5";"6";"7";"8";"9";";";",";".";" "};A2)-1)))

Ainsi plus besoin de passer par un tableau intermédiaire dans la feuille, c'est donc plus rapide.
Le test ESTERREUR est nécessaire parce que TROUVE (ou RECHERCHE) renvoie une valeur d'erreur s'il ne trouve pas le texte indiqué ; dans ce cas, on renvoie le nombre de caractères de la phrase de recherche.

Bien sûr, en cas de modification de la liste des caractères à rechercher, il faudra le faire deux fois, dans les deux tableaux.

 

[Ti]

oups, j'ai oublié le "0" dans mon tableau ! le rouge de la confusion me couvre les joues !

 

[Jean-Marie]

Re...

Elle est super ta solution Ti

@+Jean-Marie

 

[caroline]

tu n'as pas à rougir à TI... TI m'y TIre une épine du pied!

merci à tous pour votre aide!

ça marche du tonnerre!

Caro