extraire d'une adresse postale le numéro de rue

[Océane]

Bonsoir à tous
Dans les archives j'ai trouvé des solutions mais avec macro, qui sont trop compliquées pour moi.
Existe t-il une solution avec une formule ?
Les bis et terce ne m'intéressent pas je veux simplement mettre dans la cellule adjacente A, les 1, 2, ou troisième chiffres trouvés en tête dans la cellule B.
Merci d'avance

 

[Jocelyn]

Re : extraire d'une adresse postale le numéro de rue

Bonjour le Forum,
Bonjour Océane,

a tester en A4 :

=SI(ESTNUM(GAUCHE(B4;TROUVE(" ";B4)-1)*1);GAUCHE(B4;TROUVE(" ";B4)-1)*1;"")

estnum sert a vérifier que l'adresse commence par des valeurs numérique

Jocelyn

 

[Excel-lent]

Re : extraire d'une adresse postale le numéro de rue

Bonjour à tous,

Bravo Jocelyn, tu as été plus rapide que moi!

Mais ayant cherché et trouvé une solution différente, je la mets quand même pour information

A placer en cellule A4
=SI(DROITE(GAUCHE(B4;4);1)=" ";CNUM(GAUCHE(B4;3));SI(DROITE(GAUCHE(B4;3);1)=" ";CNUM(GAUCHE(B4;2));SI(DROITE(GAUCHE(B4;2);1)=" ";CNUM(GAUCHE(B4;1));"il n'y a pas de numéro de rue, ou il y a plus de 3 chiffres")))

 

[Excel-lent]

Re : extraire d'une adresse postale le numéro de rue

Sinon, le même style de formule que Jocelyn,

Au lieu d'utiliser la fonction TROUVE, avec la fonction CHERCHE

Toujours à placer en A4 :
=SI(ESTERREUR(CNUM(GAUCHE(B4;CHERCHE(" ";B4)-1)));"Il n'y a pas de numéro de rue !";CNUM(GAUCHE(B4;CHERCHE(" ";B4)-1)))

J'ai utilisé la fonction ESTERREUR volontairement! Cela reviens au même que la formule de Jocelyne (on arrive au même résultat), mais cela permet d'apprendre une nouvelle fonction.

Mais sinon, on peut greffer au coeur de la formule (en gras), la même astuce que celle utilisé par Jocelyne (utilisation de la fonction ESTNUM)

=SI(ESTNUM(GAUCHE(B4;CHERCHE(" ";B4)-1));"Il n'y a pas de numéro de rue !";CNUM(GAUCHE(B4;CHERCHE(" ";B4)-1)))

Le but de ce forum n'est-il pas d'être pédagogique?

 

[Monique]

Re : extraire d'une adresse postale le numéro de rue

Bonjour,

Une suite…

S’il y a une virgule (ou non) après le n° ou si ter ou bis sont collés (ou non) au n° :

=GAUCHE(B4;SOMMEPROD(ESTNUM(STXT(B4;LIGNE(DECALER($A$1;;;NBCAR(B4)));1)*1)*1))

 

[Océane]

Re : extraire d'une adresse postale le numéro de rue

Bravo à tous
Monique c'est parfait, j'avais précisé que les bis ou terce ne m'intéressais pas pour simplifier, mais je ne pensais pas qu'ils généreraient l'écriture...

Si j'osais je vous demanderai si ce numéro , il est possible de le couper pour le copier à coté ( le supprimer en B pour le mettre en A)."Pour l'instant il est copié"
Peut être difficile sans macro , dans ce cas je me contenterai de cette dernière formule
Bonne nuit

 

[Monique]

Re : extraire d'une adresse postale le numéro de rue

Re,

Sans macro, je ne vois pas comment faire

 

[Staple1600]

Re : extraire d'une adresse postale le numéro de rue

Bonsoir



Voici une macro incluant les formules de Monique

Sub Extraire()
Dim Fin As Long
Fin = [B65536].End(xlUp).Row
Application.ScreenUpdating = False
[A4].FormulaR1C1 = _
        "=LEFT(RC[1],SUMPRODUCT(ISNUMBER(MID(RC[1],ROW(OFFSET( R1C1,,,LEN(RC[1]))),1)*1)*1))"
[A4].AutoFill Destination:=Range("A4:A" & Fin)

Range("B4").EntireColumn.Insert
[B4].FormulaR1C1 = "=TRIM(RIGHT(RC[1],LEN(RC[1])-LEN(RC[-1])))"
Range("B4").AutoFill Destination:=Range("B4:B" & Fin)
Range("A4:C" & Fin).Value = Range("A4:C" & Fin).Value
Columns("C:C").Delete
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[kiki29]

Re : extraire d'une adresse postale le numéro de rue

Soir Bon,et une de plus

 

[Océane]

Re : extraire d'une adresse postale le numéro de rue

Un grand merci à tous : çà fonctionne.
J'ai réussi à introduire la macro de staple dans mon appli, par contre Kiki, bien que ton exemple fonctionne aussi je ne vois pas ou tu as caché la formule alors que je la retrouve en clair dans le code de Staple.
Bon wek end

 

[Staple1600]

Re : extraire d'une adresse postale le numéro de rue

Re


--> Océane:
Pour info:
Le code VBA de Kiki29 se trouve dans ThisWorkBook
(pas dans un module)

(Pour aller dans VBE:
ALT+F11 ou clic-droit sur une feuille
et choisir [Visualiser le code] )


Nom de la macro: Tst

Bon week-end à tous

A+

 

[sposs]

Re : extraire d'une adresse postale le numéro de rue

Salut Staple et tous les autres,

très très utile discussion et solution qui correspond à 99% à ce que je recherche.

Le petit % manquant je ne sais pas trop comment le résoudre vu ma nullité en VBA... (mais j'apprends chaque jour !)

donc en fait voilà, le postulat de base est le même, sauf qu'en plus les adresses que j'ai ne sont pas aussi uniformes car on peut trouver :

10/12 RUE DES FLEURS
10-12 RUE DES FLEURS
10/12B RUE DES FLEURS
10-12BIS RUE DES FLEURS...etc

Enfin bon le problème c'est comment gérer le "/" ou le "-"
Dans l'absolu je cherche à obtenir 2 colonnes pour le numéro de rue de façon à pouvoir récupérer les numéros séparément quand il y en a.

J'ai bien examiné la macro et fait quelques tests mais ce n'est franchement pas du tout probant... :-(

Vous auriez une idée ?

Merci !

 

[Tibo]

Re : extraire d'une adresse postale le numéro de rue

Bonjour,

Sur la base des quelques exemples que tu nous donnes, on peut considérer que le principe reviendrait à détecter l'emplacement du premier espace.

Ensuite, avec une fonction SUBSTITUE, on peut traiter les caractères indésirables (/ -)

Il est possible de faire ceci par formule.

Reste à voir si tu n'as pas d'autres adresses qui pourraient présenter d'autres difficultés à traiter.

Indique nous également quel résultat tu souhaites obtenir avec : 10-12BIS RUE DES FLEURS par exemple. Veux tu isoler les 10, 12 et bis ?

A te (re)lire

@+

 

[sposs]

Re : extraire d'une adresse postale le numéro de rue

Bonjour et merci de ce début de réponse.

En fait j'aimerais obtenir 3 colonnes de numéro d'adresse (parce qu'il arrive parfois qu'il y ait jusqu'à 3 numéros), 1 colonne d'adresse et supprimer directement tous les Bis (ou B seuls) et Ter (ou T seuls) qui ne me sont d'aucune utilité.

Ainsi "4-6-8BIS RUE DES FLEURS" ou "4-6-8 BIS RUE DES FLEURS" ou même "4/6/8B RUE DES FLEURS" serait splitté en :

4 | 6 | 8 | RUE DES FLEURS

Ca parait faisable ?

 

[Staple1600]

Re : extraire d'une adresse postale le numéro de rue

Bonjour


Voici un petit fichier qui j'espère pourra t'inspirer ou t'aider.

(voir code VBA --> méthode utilisée Données/Convertir + enregistreur de macro)