Fonction MID avant un nombre quel qu'il soit?

Question

Un grand bonjour!

Je reviens après une longue absence qui date de 2009.

Revenir ici me tient à cœur car j'ai décidé d'améliorer un outil de tri d'offres d'emploi pour lequel j'avais eu l'aide de Gad à cette époque.

J'ai réussi à aller assez loin dans la définition de ma formule grâce aux formules trouvées ici et là sur les forum mais là je cale

Voici ma question du jour.

PRINCIPE

Il s'agit d'extraire une chaine de caractères placée entre "M." OU "Mme." et [le numéro de la rue] dans des adresses extraites de milliers d'offres d'emploi.

DANS CET EXEMPLE

Je souhaite extraire dans ma colonne V la chaine de caractères "MACHINCHOSE Sylvain" place entre "M." et "10".

Tout marche au poil si je remplace [0-9] par la valeur 10. Mais le hic, c'est que je veux que la formule fonctionne pour n'importe quelle valeur numérique. Mais en replaçant "10" par [0-9], j'obtiens une erreur #VALUE!

EXEMPLE

Cellule U43: Veuillez adresser votre CV et une lettre de motivation, en précisant le numéro de l'offre à : PIERRE MACHINCHOSE PAYSAGISTE M. MACHINCHOSE Sylvain 10 RUE LEONARDI DE VINCI 91220 LE PLESSIS PATE

Cellule V43: =MID(U44;FIND(IF(ISERROR(FIND("Mme.";U44));"M.";"Mme.");U44)+1;(SEARCH("[0-9]";44)-(FIND(IF(ISERROR(FIND("Mme.";U44));"M.";"Mme.");U44)+1)))

Un grand merci pour votre aide et excellent week-end à tous.

h2so4 · Answer

Une solution avec des fonctions personnalisées. findnum(texte, position) renvoie la position du premier chiffre trouvé dans texte findname(texte) renvoie le nom trouvé entre M. ou Mme et un premier chiffre trouvé dans texte Function findnum(s, Optional pos = 1) As Integer For i = pos To Len(s) If Mid(s, i, 1) Like "#" Then findnum = i: Exit Function Next i End Function Function findname(s) s = Replace(s, "M.", "") s = Replace(s, "Mme", "") findname = Left(s, findnum(s) - 1) End Function...

h2so4 · Answer

Avec une formule matricielle, à valider avec CTRl-Shift-Entrée, détecte un chiffre dans les 40 premiers caractères de A2 =TRIM(SUBSTITUTE(SUBSTITUTE(LEFT(A2;MATCH(TRUE;ISNUMBER(1*MID(A2;ROW($1:$40);1));0)-1);"M.";"");"Mme";""))...

h2so4 · Answer

La formule faisant l'hypothèse qu'il n'y avait pas de texte avant M. ou Mme voici une solution en passant par une colonne intermédiaire...

h2so4 · Answer

1ere formule, a pour but d'enlever tout ce qui précède M. ou Mme =MID(S2;FIND("M.";SUBSTITUTE(S2;"Mme";"M."));1000) (1) (2) et (3) représente le résultat des différentes étapes (1) substitute(S2,"Mme","M.") prendre S2 et replacer Mme par M. (ceci permet de faire la recherche uniquement sur M.) (2) find("M.",(1)) rechercher la position de M. dans (1) (3) mid(S2,(2),1000), prendre 1000 caractère de S2 à partir de la position (2) 2ème formule, a pour but d'enlever de prendre le texte qui précède le premier chiffre et d'enlever le M. ou Mme initial (il s'agit 'une formule matricielle (validé par ctrl-Shift-Enter) =TRIM(SUBSTITUTE(SUBSTITUTE(LEFT(T2;MATCH(TRUE;ISNUMBER(1*MID(T2;ROW($1:$300);1));0)-1);"M.";"");"Mme";"")) (1) ROW($1:$300) : crée une matrice avec les nombres de 1 à 300 (2) MID(T2;(1);1) : crée une matrice avec les 300 premiers caractères de T2 (3) ISNUMBER(1*(2));crée une matrice avec vrai ou faux (true or false, selon que le caractère est un chiffre ou non) (4) MATCH(TRUE,(3),0) retourne la position dans la matrice (3) la première occurrence de true (= position du premier chiffre) (5) LEFT(T2,(4)-1) prendre les (4)-1 premiers caractères de T2 (6) SUBSTITUTE((5),"M.", "") supprimer M. (7) SUBSTITUE((6),"Mme","") supprimer Mme (8) TRIM((7)) supprimer les blancs initiaux et finaux. tu peux vérifier ces différentes étapes via le menu (version UK) formulas, evaluate formula (dans le groupe formula auditing), je ne connais pas l'équivalent de ces menus dans la version FR pour moi, il est plus simple de passer via une fonction personnalisée. ces formules sont illisibles et difficiles à comprendre. mais tu voulais une formule sans macro...

vba-new · Answer

Une petite adaptation de la formule de h2so4 (le choix du pseudo a-t-il un rapport avec ta personnalité ? ) L'utilisation d'une formule non matricielle est possible : =SUPPRESPACE(SUBSTITUE(STXT(S2;TROUVE(" ";S2;MIN(TROUVE({"M.";"Mme"};S2&amp;"M.Mme")))+1;9^9);DROITE(S2;NBCAR(S2)-MIN(TROUVE({0;1;2;3;4;5;6;7;8;9};S2&amp;"0123456789"))+1);"")) Pour l'explication, la formule TROUVE({0;1;2;3;4;5;6;7;8;9};S2) permet de trouver n'importe quelle valeur numérique dans une chaîne. Cependant, celle-ci va retourner une erreur si au moins un des chiffres n'est pas trouvé . C'est pourquoi on rajoute la chaîne "0123456789" dans S2&"0123456789" , pour être sûr que tous les chiffres seront trouvés. On n'a plus qu'à utiliser la fonction MIN pour trouver la position du premier chiffre détecté. Edit : Pour évaluer les formules dans la version FR : onglet Formules / groupe Audit de formules / Évaluation de formule...

h2so4 · Answer

Brillant ! Epatant...

JPCescutti · Answer

Je trouve la solution de vba-new astucieuse et efficace pour éviter la gestion d'erreur potentielle. Autant l'utiliser également pour le début, pour trouver la position de M. ou Mme. Voir fichier joint. (pour plus de clarté j'ai utiliser des colonnes pour l'index du caractère de débit et fin, mais on peut facilement mettre tous dans une formule. Ce pb relève du traitement syntaxique de chaines de caractères. Mes vieux souvenirs dans ce domaine, me disent qu'il est très important de bien clarifier les règles de syntaxe avant d'écrire toutes fonctions ou formules: Que se passe t il dans les cas suivants: &gt; le point de M. ou Mme. est oublié ? &gt; la personne utilise un titre tel que Dr , Mtre , ... ? L'approche de vba-new permet une généralisation facile à ces cas (voir formule alternative pour l'index de début dans mon fichier) Mais il y a peut être d'autres cas plus délicat à envisager: M. et Mme. ou toute autre forme dans le genre (je pense en particulier aux personnes allemandes qui peuvent avoir 3 à 4 abréviation / titres avant leur nom). l'adresse ne comporte pas de n°, mais seulement un lieu-dit ou "route de...", classique hors agglomération. ... J'espère ne pas avoir trop compliqué le sujet. Mais j'ai tellement eu de mauvaise expérience dans le domaine que maintenant je suis vigilant. Salut JPC...