Fonction MID avant un nombre quel qu'il soit?

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.

bonsoir,

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

Bonjour H2s04,

D'abord, merci pour le temps consacré à mon problème.

J'ai besoin d'avoir la solution sous la forme d'une formule.

Est-ce possible selon vous ?

D'avance merci.

bonjour,

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";""))

Un grand merci pour votre réactivité!

Je sens que la solution approche.

Je vous joins mon fichier Excel de 3 lignes

La formule est en T. Elle utiliser les données des cellules en S.

Ça me renvoie un message #N/A , je dois faire un truc de travers?

14offres-emploi.zip (5.72 Ko)

Bonjour,

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.

15offres-emploi.zip (8.28 Ko)

Un grand merci à vous pour cette solution précieuse!

Est-ce que je peux vous demander de m'expliquer "en français de tous les jours" ces deux formules s'il vous plait?

colonne intermédiaire =MID(S2;FIND("M.";SUBSTITUTE(S2;"Mme";"M."));1000)

Je ne comprends pas le rôle de la fonction subsitute

colonne finale = =TRIM(SUBSTITUTE(SUBSTITUTE(LEFT(T2;MATCH(TRUE;ISNUMBER(1*MID(T2;ROW($1:$300);1));0)-1);"M.";"");"Mme";""))

Je ne comprends...pas grnad chose dans celle-ci...

Merci, merci!

bonsoir,

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.

Bonjour à tous,

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&"M.Mme")))+1;9^9);DROITE(S2;NBCAR(S2)-MIN(TROUVE({0;1;2;3;4;5;6;7;8;9};S2&"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

Brillant ! Epatant

Bonjour,

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:

  • > le point de M. ou Mme. est oublié ?
  • > 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

Bonjour à tous,

Un grand, grand, grand merci à h2s04 pour avoir pris le taureau par les cornes et JP Cescutti et vba-new pour leurs contributions.

Je vais dépiotter minutieusement les explications que vous avez eu la gentillesse de me fournir pour me "upgrader" sur la traitement des chaines de caractères!

Excellente soirée à tous!

Rechercher des sujets similaires à "fonction mid nombre quel soit"