extraction de texte entre 2 valeurs numériques Résolu/Fermé

Question

Bonjour, serait-il possible que quelqu'un me dise comment extraire d'un adresse la partie se trouvant entre le N° de la rue et le code postal.
EXPL. Mr Dupont henri 13 rue montesquieu 56238 lorient.

ce qui devrait renvoyer rue montesquieu

D'avance merçi

Configuration: Windows XP / Firefox 32.0

pijaku · Answer

Bonjour, 

Par macro avec une fonction personnalisée. 

Création : 
Depuis le classeur concerné, tapez Alt + F11, Insertion/Module, copiez-collez y ce code :
Function Extrait_Adresse(Cel As Range) As String
Dim i As Integer, deb As Integer, fin As Integer
i = 0
deb = 0
Do While i < Len(Cel.Value)
    i = i + 1
    If IsNumeric(Mid(Cel.Value, i, 1)) And deb = 0 Then
        Do While IsNumeric(Mid(Cel.Value, i, 1))
            i = i + 1
        Loop
        deb = i
    ElseIf IsNumeric(Mid(Cel.Value, i, 1)) And deb > 0 Then
        fin = i
        Exit Do
    End If
Loop
Extrait_Adresse = Trim(Mid(Cel.Value, deb, fin - deb))
End Function

Utilisation : 
Dans la feuille concernée, tapez la formule : 
=Extrait_Adresse(A1) 
où A1 = Mr Dupont henri 13 rue montesquieu 56238 lorient.

nihcoc · Answer

Bonjour !

Je te remercie pour la macro,mais j'utilise une vieille version et ça ne marche pas.

j'aurais préféré une formule.

Merçi

michel_m · Answer

Bonjour,

Cette formule de Jacques Boisgontier te renvoie "Monstesqieu"
http://boisgontierjacques.free.fr/


=GAUCHE(STXT(A1;EQUIV(VRAI;ESTNUM(CNUM(STXT(A1;LIGNE($1:$255);1)));0)+7;999);CHERCHE(" ";STXT(A1;EQUIV(VRAI;ESTNUM(CNUM(STXT(A1;LIGNE($1:$255);1)));0)+7;999))-1) 
Formule matricielle à valider par « ctrl+maj+entrée » (et non directement par « entrée »), le curseur clignotant dans la barre de formule

A mon avis avec une formule comme ça, il vaut mieux employer une macro comme te l'a proposé Pijaku et je te laisse le soin de retrouver "rue montesqieu" ;o)

Raymond PENTIER · Answer

Dis-nous, nihcoc, qui saisit ces adresses ? toi-même ? un collège ? elles sont extraites d'une base de données ? elles sont téléchargées sur internet ?
En effet je pense que la solution à ton problème passe par la structure et la syntaxe de tes données, parce que si toutes tes adresses se présentent ainsi sans ponctuations, il n'existe pas de formule magique, qui pourra traiter à la fois
  Mr Dupont henri 13 rue montesquieu 56238 lorient  Mr Dupont jean impasse montesquieu 56238 lorient  Mr Dupont henri 13 rue du 18 juin 56238 lorient  Mr Dupont henri 13 arsenal militaire de lorient  Mr Dupont BP 854 56238 lorient

nihcoc · Answer

Bonjour

La structure n'a rien a voir, ce que je cherche en résumé c'est a extraire la partie qui se trouve entre 2 bornes numériques,en l'occurrence dans l'adresse que j'ai mis la partie qui se trouve entre le N° de la rue et le code postal.
soit " rue Montesquieu "
ceci est un exemple.
Merçi

pijaku · Answer

Extraction de la chaine de caractères comprise entre les deux premières bornes numériques :
Suivant la formule de Boisgontier donnée précédemment par Michel_m...

Merci de suivre toutes les étapes de construction de ladite formule pour pas que ducroq y se soit...

Nota : toutes ces formules sont à valider par Ctrl+Shift+Entrée (formules matricielles)

Soit la chaîne de caractères en A2

1- trouver la première occurrence numérique dans la chaîne :
=EQUIV(VRAI;ESTNUM(CNUM(STXT(A2;LIGNE($1:$255);1)));0)
 
2- recherche de l'espace suivant ce caractère :
===> Cette étape est indispensable pour déterminer le nombre de caractères numériques constituant la première borne. Si nous n'avons pas ici un point commun à toutes les chaînes (espace ou autre), c'est fichu!
=CHERCHE(" ";STXT(A2;EQUIV(VRAI;ESTNUM(CNUM(STXT(A2;LIGNE($1:$255);1)));0);999))-1

3- Emplacement de cet espace dans la chaine initiale :
=EQUIV(VRAI;ESTNUM(CNUM(STXT(A2;LIGNE($1:$255);1)));0)+CHERCHE(" ";STXT(A2;EQUIV(VRAI;ESTNUM(CNUM(STXT(A2;LIGNE($1:$255);1)));0);999))-1

4- Extraction de la partie à droite de cet espace :
=STXT(A2;EQUIV(VRAI;ESTNUM(CNUM(STXT(A2;LIGNE($1:$255);1)));0)+CHERCHE(" ";STXT(A2;EQUIV(VRAI;ESTNUM(CNUM(STXT(A2;LIGNE($1:$255);1)));0);999))-1;999)

5- Emplacement du premier caractère numérique après cet espace :
=EQUIV(VRAI;ESTNUM(CNUM(STXT(STXT(A2;EQUIV(VRAI;ESTNUM(CNUM(STXT(A2;LIGNE($1:$255);1)));0)+CHERCHE(" ";STXT(A2;EQUIV(VRAI;ESTNUM(CNUM(STXT(A2;LIGNE($1:$255);1)));0);999))-1;999);LIGNE($2:$256);1)));0)

6- Nombre de caractères à extraire :
=EQUIV(VRAI;ESTNUM(CNUM(STXT(STXT(A2;EQUIV(VRAI;ESTNUM(CNUM(STXT(A2;LIGNE($1:$255);1)));0)+CHERCHE(" ";STXT(A2;EQUIV(VRAI;ESTNUM(CNUM(STXT(A2;LIGNE($1:$255);1)));0);999))-1;999);LIGNE($2:$256);1)));0)-1

7- Formule finale :
=STXT(A2;CHERCHE(" ";STXT(A2;EQUIV(VRAI;ESTNUM(CNUM(STXT(A2;LIGNE($2:$256);1)));0);999);EQUIV(VRAI;ESTNUM(CNUM(STXT(A2;LIGNE($2:$256);1)));0));EQUIV(VRAI;ESTNUM(CNUM(STXT(STXT(A2;EQUIV(VRAI;ESTNUM(CNUM(STXT(A2;LIGNE($1:$255);1)));0)+CHERCHE(" ";STXT(A2;EQUIV(VRAI;ESTNUM(CNUM(STXT(A2;LIGNE($1:$255);1)));0);999))-1;999);LIGNE($2:$256);1)));0)-1)

Je maintiens toutefois ce qui a été dit : il vaut mieux employer une macro

PS : Il existe des écritures différentes, notamment au niveau de la recherche de l'espace (points 2 & 3). Cependant, lors de mes essais, j'ai été confronté à des bizarreries dans les résultats. Selon les chaines initiales, les fonctions retournaient généralement le bon résultat, mais pas de manière systématique...

Exemple pour le point 3 : 
=CHERCHE(" ";STXT(A2;EQUIV(VRAI;ESTNUM(CNUM(STXT(A2;LIGNE($1:$255);1)));0);999);EQUIV(VRAI;ESTNUM(CNUM(STXT(A2;LIGNE($1:$255);1)));0))

fonctionne avec :
0012 Pradelles (Haute loire-43) = Pradelles (Haute loire-
le 12 du mois je travaille a 15 heures = du mois je travaille a

mais pas avec :
Mr Dupont jean 43 rue léon blum 44635 nancy = rue léon blum

Les curiosités d'Excel ou un défaut dù à ma vue ou à mon cerveau embrouillé...

Bonne journée à tous.

nihcoc · Answer

Bonjour!

j'ai rentré les formules que tu me l'a écrit,la 5 et la 6 ,je ne peut pas les valider
et la 7ème , la formule est trop longue.
j'utilise excel 95.

je laisse tomber
                              Merci pour tout.

tontong · Answer

Une seule colonne auxiliaire suffit pour que les formules proposées par pijaku puissent être validées sur Xl2003.
En B2....
1- trouver la première occurrence numérique dans la chaîne :
=EQUIV(VRAI;ESTNUM(CNUM(STXT(A2;LIGNE($1:$255);1)));0)

https://www.cjoint.com/?3JDszIGXsrN

nihcoc · Answer

C'était presque parfait, seul problème la dernière formule est trop longue et donc ne rentre pas.(270 caractères) et je ne peux en rentrer que 255.
Merçi

nihcoc · Answer

bonsoir!

en fait j'ai repris ton classeur que j'ai ouvert avec calc.
je lai enregistrer ensuite en excel 95 et ça marche.

je ne comprend pas pourquoi.

en tout cas merçi a tout le monde , c'est super

Extraction de texte entre 2 valeurs numériques

10 réponses

Discussions similaires