Sujet Précédent Sujet Suivant

Séparation Adresse Excel

Résolu/Fermé
Rockoff - 1 août 2011 à 11:00
pilas31 Messages postés 1825 Date d'inscription vendredi 5 septembre 2008 Statut Contributeur Dernière intervention 24 avril 2020 - 2 août 2011 à 11:56
Bonjour,
Tout d'abord j'ai cherché une solution à mon problème pendant pas mal de temps mais je n'ai pas trouvé de solution efficace , en effet j'ai besoin d'extraire la ville de ma colonne "adresse" mais voila je n'ai pas le format habituel RUE CP VILLE "9 avenue General De gaulle 75008 Paris" mais RUE VILLE CP "9 avenue General de Gaulle Paris 75008" ce qui m'empeche d'utiliser une formule pour scinder l'adresse après une suite de 5 chiffres...
Si quelqu'un peut m'éviter le calvaire de 14 768 lignes grâce à une petite macro ou formule :)
Merci d'avance !


A voir également:

2 réponses

Réponse 1 / 2
pilas31 Messages postés 1825 Date d'inscription vendredi 5 septembre 2008 Statut Contributeur Dernière intervention 24 avril 2020 644
1 août 2011 à 15:57
Voilà j'ai fait une petite macro exemple avec un algorithme en VBA:

Le fichier est un peu lourd car j'ai mis un onglet CP_VILLE dans lequel se trouvent tous les codes postaux associés aux villes (je pense qu'elle vient de l'INSEE).
Comme la liste des villes est sans tiret, sans apostrophe et qu'il y a ST à la place de SAINT. Je normalise au début l'adresse.

La macro à lancer s'appelle « EXTRAIRE_VILLE »

Quand il ne trouve pas il indique l'erreur en colonne 3


télécharger ici :
http://www.cijoint.fr/cjlink.php?file=cj201108/cij06iXBdv.xls

Me dire si c'est ok. C'est à retravailler en fonction des échecs rencontrés.
Elle n'est pas optimisée elle va "ramer" un peu ....


A+

3
Rockoff Messages postés 3 Date d'inscription lundi 1 août 2011 Statut Membre Dernière intervention 2 août 2011
1 août 2011 à 16:37
Merci pour ta macro ! Cela fonctionne , il vrai que cela rame un peu ( 12 adresses sans ramer puis apres leger lag de 5s 6s et ça repart donc ça va ) , par contre y'aurait il une solution pour que la macro ne s'arrete pas lorsque qu'il ya une erreur d'incompatibilité de type ? en effet je me suis aperçus que parfois ( rarement) le code postal manque ou alors l'adresse se présente sous cette forme "9 avenue de strasbourg Paris 75003 FR" ce qui provoque l'arret de la macro.
J'ai l'impression de demander toujours trop, ce que tu as fait m'aide deja beaucoup !
Je te remercie pour ton aide !
0
pilas31 Messages postés 1825 Date d'inscription vendredi 5 septembre 2008 Statut Contributeur Dernière intervention 24 avril 2020 644
Modifié par pilas31 le 1/08/2011 à 17:01
Oui, je te propose de faire une gestion d'erreur en modifiant légèrement la macro comme cela :
le On error juste après le For
et le FIN: juste avant le next 

For Ligne = 2 To DernLigne 
    On Error GoTo FIN: 
    ...  
    ...
FIN: 
If Err.Number <> 0 Then 
    Cells(Ligne, 2) = "" 
    Cells(Ligne, 3) = "Erreur Excel N° " & Err.Number 
End If 
Next Ligne 
End Sub

Le reste de la macro est inchangé. Si il se produit une erreur excel, il ne bloque pas il inscrit le numéro de l'erreur en colonne 3.
0
Rockoff Messages postés 3 Date d'inscription lundi 1 août 2011 Statut Membre Dernière intervention 2 août 2011
2 août 2011 à 11:24
Bonjour Pilas 31 , Merci d'avoir peaufiner ta macro pour moi , mais lorsque j'effectue les changements et que je lance la macro VBA me lance une
"Erreur de compilation
Next sans For"
et me surligne le next ligne avant End Sub
une idée ?
0
pilas31 Messages postés 1825 Date d'inscription vendredi 5 septembre 2008 Statut Contributeur Dernière intervention 24 avril 2020 644
2 août 2011 à 11:31
Bonjour,

Je pense que tu as peut-être involontairement écrasé une instruction. Comme elle n'est pas trés longue je te redonne la totalité de la macro qui fonctionne chez moi: 
Sub EXTRAIRE_VILLE()

Dim TabVille(100)

Sheets("Adresses").Select
DernLigne = Columns(1).Find("*", , , , xlByColumns, xlPrevious).Row
For Ligne = 2 To DernLigne
    On Error GoTo FIN:
    'initialisations
    NbVille = 0
    For Ville = 1 To 100
        TabVille(Ville) = ""
    Next Ville
    'Adresse à tester on enlève les espaces avant et aprés
    Adresse = Trim(Cells(Ligne, 1))
    'on remplace les apostrophes par des espaces
    Adresse = Replace(Adresse, "'", " ")
    'on remplace les tirets par des espaces
    Adresse = Replace(Adresse, "-", " ")
    'on remplace les "SAINT" par "ST"
    Adresse = Replace(Adresse, "SAINT", "ST", 1, -1, 1)
    'Extraire le CP
    CP = CLng(Mid(Adresse, Len(Adresse) - 4, 5))
    'Trouver les villes correspondantes
    
    For LigCP = 2 To 38949
        If CP = CLng(Sheets("CP_VILLE").Cells(LigCP, 1)) Then
            NbVille = NbVille + 1
            TabVille(NbVille) = Sheets("CP_VILLE").Cells(LigCP, 2)
        End If
    Next LigCP
    If NbVille <> 0 Then
        If NbVille = 1 Then
            'Une seule ville pas de pb
            Cells(Ligne, 2) = TabVille(1)
            Cells(Ligne, 3) = ""
        Else
            'il y a plus d'une ville on va essayer de trouver la bonne
            ' on teste la présence du nom de la ville dans la chaine initiale
            Ville_Trouv = 0
            For Ville = 1 To NbVille
                If InStr(1, Adresse, TabVille(Ville), 1) <> 0 Then
                    Ville_Trouv = Ville
                    Exit For
                End If
            Next Ville
            If Ville_Trouv <> 0 Then
                Cells(Ligne, 2) = TabVille(Ville_Trouv)
                Cells(Ligne, 3) = ""
            Else
                'Erreur : par trouvé de ville
                Cells(Ligne, 2) = Join(TabVille, " ")
                Cells(Ligne, 3) = "Echec choix de la ville"
            End If
        End If
    Else
        'Erreur : pas trouvé le CP
        Cells(Ligne, 2) = ""
        Cells(Ligne, 3) = "Code Postal faux"
    End If
FIN:
    If Err.Number <> 0 Then
        Cells(Ligne, 2) = ""
        Cells(Ligne, 3) = "Erreur Excel N° " & Err.Number
    End If
Next Ligne
End Sub

A+
0
Rockoff Messages postés 3 Date d'inscription lundi 1 août 2011 Statut Membre Dernière intervention 2 août 2011
2 août 2011 à 11:54
Qu'un seul mot à dire : parfait !
Merci beaucoup pour le temps que tu as passé dessus !
Problem solved
0
Réponse 2 / 2
pilas31 Messages postés 1825 Date d'inscription vendredi 5 septembre 2008 Statut Contributeur Dernière intervention 24 avril 2020 644
1 août 2011 à 11:27
Bonjour,

Il est possible de supprimer les cinq derniers caractères et d'essayer de retrouver la ville mais il n'y a pas de séparateurs fiable (car l'espace peut être dans le nom composé d'une ville).

Ma suggestion est la suivante. Télécharger un tableau de correspondance entre le code postal et la ville (on en trouve en téléchargement sous format excel avec 36 000 lignes). mettre ce tableau dans une feuille du classeur

Extraire le code postal de l'adresse avec une formule du type =DROITE(A1;5)

Puis avec un RECHERCHEV trouver dans le tableau de correspondance la ville.

A+

1
chossette9 Messages postés 4239 Date d'inscription lundi 20 avril 2009 Statut Contributeur Dernière intervention 12 septembre 2014 1 308
1 août 2011 à 11:37
Bonjour pilas,

ta solution semble adaptée aux besoins de Rockoff, mais je pense qu'elle a des limites, car comment choisir la bonne ville quand plusieurs ont le même code postal ?

Cordialement.
0
pilas31 Messages postés 1825 Date d'inscription vendredi 5 septembre 2008 Statut Contributeur Dernière intervention 24 avril 2020 644
1 août 2011 à 11:50
Oui, tu as raison, plusieurs villes peuvent avoir le même CP. Donc je ne vois pas de solution simple sauf faire un algorithme en VBA pour retrouver dans la liste des villes correspondant à ce CP celle qui se trouve dans l'adresse initiale mais il risque d'y avoir des échecs si la ville n'est pas exactement orthographiée de la même manière...
0
chossette9 Messages postés 4239 Date d'inscription lundi 20 avril 2009 Statut Contributeur Dernière intervention 12 septembre 2014 1 308
1 août 2011 à 11:52
Effectivement, il ne reste dans ce cas que le VBA, et ça risque d'être compliqué à réaliser...
0
Rockoff
1 août 2011 à 11:57
Merci pour vos réponses , je viens de tester la solution de Pilas31 et en effet il y 'a un leger problème pour les villes ayant des CP identiques. Je pense aussi que le VBA reste la derniere solution mais que la réalisation est difficile ( voir impossible ?) . Si quelqu'un passe par là et est tenté de m'aider :)
0
pilas31 Messages postés 1825 Date d'inscription vendredi 5 septembre 2008 Statut Contributeur Dernière intervention 24 avril 2020 644
1 août 2011 à 14:20
Re bonjour,

Je vais essayer de proposer quelque chose dans cet esprit...
A+
0

Discussions similaires

Ça veut dire quoi intitulé d’une adresse ??
Mousse -
did -

4 réponses