Recherche CP et ville dans une cellule

[KOGITUS]

Bonjour,

Je voudrai récupérer le code postal ainsi que la ville qui se trouve mélanger à d’autres infos dans une cellule et si possible avec une formule.

Exemple :
XXXX OOOO - ZZZZZ 1020-Route d'AAAA-31200-Toulouse-France
JJJJJ KK 1050 AVENUE DU GRAND TOTO-37170-Chambray-lès-Tours-France
BBBBB AA CCCCC LLLLLL-72-GGGGGGGGGGG-52068-Aachen-Allemagne

Sur votre site J’ai trouvé cette formule :

=STXT(A3;(NBCAR(A3)-EQUIV(VRAI;ESTNUM(--(STXT(A3;NBCAR(A3)-LIGNE($1:$255);5)));0)-6)+6;99)

Qui me donne : « 31200-Toulouse-France »

J’ai un souci avec :
MMM VVVVVV IIIIIIIIIII DE LA MMMMMM LLLLLLL-13011-Marseille-France
La formule me donne : « 1-Marseille-France »

Il y a peut-être mieux comme formule ?
Moi je voudrai récupérer le CP dans une cellule et la ville dans une autre ??
Exemple
XXXX OOOO - ZZZZZ 1020-Route d'AAAA-31200-Toulouse-France 31200 TOULOUSE

Merci pour votre aide.

 

[CISCO]

Re : Recherche CP et ville dans une cellule

Bonjour

Une possibilité en pièce jointe.

Le principe : La formule recherche la position du premier "-" suivi d'un nombre, et fait d'autres recherches à partir de cette valeur. Donc si cette combinaison se trouve ailleurs sur une des lignes, avant le code postal, la réponse renvoyée ne sera pas bonne.

@ plus

 

[Theze]

Re : Recherche CP et ville dans une cellule

Bonjour,

Avec une fonction perso à mettre dans un module standard :

Public Function CP_ou_VILLE(Texte As String, Position As Integer) As String

    CP_ou_VILLE = Split(Texte, "-")(Position)

End Function

En admettant que le texte (XXXX OOOO - ZZZZZ 1020-Route d'AAAA-31200-Toulouse-France) est en A1, dans une cellule entrer la formule ci-dessous pour le CP :

=CP_ou_VILLE(A1;3)

et dans une autre cellule formule ci-dessous pour la ville :

=CP_ou_VILLE(A1;4)

où 3 et 4 sont les positions ordinales du code postal et de la ville dans les blocs de lettres/chiffres séparés par les tirets "-", 3 car la base est 0 :
"XXXX OOOO " <-- bloc 0
" ZZZZZ 1020" <-- bloc 1
"Route d'AAAA" <-- bloc 2
"31200" <-- bloc 3
"Toulouse" <-- bloc 4
"France" <--bloc 5

Dommage que la fonction "Split" n'existe pas directement dans Excel !

Hervé.

 

[CHALET53]

Re : Recherche CP et ville dans une cellule

Bonjour,

en m'inspirant de ce qu'a fait These

a+

 

[haonv]

Re : Recherche CP et ville dans une cellule

Bonjour à tous,

Le problème vient du fait qu'excel considère "1-mar" comme "1°mars" donc un comme nombre.
Ce qui créé le décalage lors de l'application de la formule.
En substituant "-" par un autre signe, on peut résoudre ce problème.
Pour ce qui est d'isoler la ville, je ne pense pas que l'on puisse le faire avec la présentation actuelle.

Cordialement

 

[CISCO]

Re : Recherche CP et ville dans une cellule

Bonjour

Bonjour à tous,
Pour ce qui est d'isoler la ville, je ne pense pas que l'on puisse le faire avec la présentation actuelle.

Cordialement

Si on tient à le faire directement, sans passer par la cellule donnant le code postal, c'est faisable en affichant le 8ème caractère, et les suivants, après le premier couple "-"&chiffre.

@ plus

 

[KOGITUS]

Re : Recherche CP et ville dans une cellule

Bonjour à tous,

Grand merci à vous qui avez pris le temps de travailler sur mon problème.

Je pense que je vais retenir la dernière solution de haonv du fichier "Pour kogitus". Suivant mes besoins par la suite je passerai peut-être par les macros.

Le hic c'est que je patoge !!!
Cette super formule j'ai beaucoup de mal à comprendre.
Pourquoi il y a la fonction ligne ?
Il faut donc que je modifie le nombre "245" en fonction du nombre de ligne que j'ai dans mon tableau?

Est-il possible de déterminer la position du dernier tiret et de faire un gauche pour ne garder que la ville ?
Par exemple : Chambray-lès-Tours-France
Ici le dernier tiret est en position 19, avec la fonction gauche je peux récupérer la ville.

Merci à vous

 

[CISCO]

Re : Recherche CP et ville dans une cellule

Bonsoir

Pour ce qui est du 245 de la formule de haonv, non, tu n'as pas besoin de le changer. C'est une valeur mise là un peu au pif (moi, j'avais pris 255), mais qui doit forcément être supérieure au nombre maximal de caractère de ton texte le plus long, en colonne A.

Pour ce qui est du nom de la ville, regardes ma proposition. Cela fonctionne... mais ce n'est pas forcément facile à comprendre. Le principe : On recherche le premier "-" suivant le code postal (trouvé dans la colonne précédente), pour savoir où se trouve le début du nom de la ville, et le second"-" suivant ce code postal, pour savoir où se trouve la fin du nom de la ville. Cela ne fonctionne donc pas si tu as un ville du style Soisy-sous-Montmorency. Dans ce dernier cas, la formule ne récupèrera que le premier terme, Soisy.


@ plus

 

[mapomme]

Re : Recherche CP et ville dans une cellule

Bonjour à tous ,

J'y vais aussi de mes formules matricielles qui devraient fonctionner aussi avec des codes postaux inférieures à 5 caractères pourvu que le pays ne comporte pas de trait d'union.

 

[KOGITUS]

Re : Recherche CP et ville dans une cellule

Bonjour,

De mon coté j'avais commencé à bidouiller les fichiers de Cisco et Haonv mais Mapomme tu as mis dans le mille

J'avais aussi trouvé cette formule qui trouve le dernier espace :

=NBCAR(A1)-EQUIV(" ";STXT(A1;NBCAR(A1)-LIGNE(INDIRECT("1:"&NBCAR(A1)));1);0)
Valider avec Maj+Ctrl+Entrée

Trouvé ici :
Ce lien n'existe plus

Grâce à vous je vais gagner beaucoup de temps car 600 lignes d'adresse à modifier manuellement tous les mois c'est pas de la tarte.

Merci à tous.

 

[CISCO]

Re : Recherche CP et ville dans une cellule

Bonjour

Ma proposition pour le CP est nettement plus courte, il me semble :

=STXT(A1;1+MAX(SI((STXT(A1;LIGNE($1:$255);1)="-")*(ESTNUM(--(STXT(A1;LIGNE($1:$255)+1;1))));LIGNE($1:$255)));5)

mais ne fonctionne correctement que pour ceux comportant 5 chiffres (comme dans les exemples donnés dans le premier post).

@ plus