Extraire le nom de la commune sans les informations qui suivent

Version imprimable

06/03/2014, 15h10
bolide7

Extraire le nom de la commune sans les informations qui suivent

Bonjour à tous.

En A1, j’ai des noms de communes, qui peuvent être formulés de plusieurs façons :
- PARIS
- PARIS Cedex
- PARIS Cedex 1
- LA RIVIERE
- LA RIVIERE Cedex
- LA RIVIERE Cedex 10

Comment extraire le nom de la commune sachant que la fonction suivante donne une valeur d’erreur lorsque le nom de la commune n’est suivi d’aucune information complémentaire :

Code:

=STXT(A1;1;CHERCHE(" Cedex";A1;1))

Les 2 fonctions suivantes ne fonctionnent pas avec les communes composées de 2 noms, comme LA RIVIERE (nom composé sans tiret) :

Code:

=GAUCHE(A1;TROUVE(" ";A1)-1)

Code:

=GAUCHE(A1;TROUVE(" ";A1))

D’avance, merci beaucoup.
Bonne après-midi à tous.
bolide7
06/03/2014, 15h39
papouclo

Bonjour,
Si on veut une réponse comme "- PARIS" ou "- LA RIVIERE", ce que laisse supposer les formules essayées, utiliser la formule :

Code:

=SIERREUR(GAUCHE(A1;CHERCHE(" Cedex";A1)-1);A1)

Si on veut une réponse comme "PARIS" ou "LA RIVIERE", utiliser :

Code:

=SUBSTITUE(SIERREUR(GAUCHE(A1;CHERCHE(" Cedex";A1)-1);A1);"- ";"")

Cordialement
Claude
07/03/2014, 03h14
bolide7

Bonjour papouclo,

Je reviens vers toi seulement maintenant et donc tardivement car j’ai un grave souci que je viens seulement d’isoler.

Tout d’abord, merci beaucoup pour tes 2 propositions qui fonctionnent.
Par contre, je n’ai pas vu de différence dans le résultat obtenu.
Ces 2 formules marchent et donnent le même résultat, quelque soit la formulation de la cellule d’origine.
J’ai testé avec plusieurs formulations :
- PARIS
- PARIS Cedex
- PARIS Cedex 1
- LA RIVIERE
- LA RIVIERE Cedex
- LA RIVIERE Cedex 1
- LA RIVIERE-SAINT-JEAN
- LA RIVIERE-SAINT-JEAN Cedex
- LA RIVIERE-SAINT-JEAN Cedex 1
Quelle est donc la différence entre tes 2 formules ?

Comme je travaille sur la cellule C7 d’une feuille dont l’onglet a pour nom la cellule A9, j’ai adapté ta formule en remplaçant A1 par : INDIRECT("'"&$A9&"'!$C$7")

Code:

=SIERREUR(GAUCHE(INDIRECT("'"&$A9&"'!$C$7");CHERCHE(" Cedex";INDIRECT("'"&$A9&"'!$C$7"))-1);INDIRECT("'"&$A9&"'!$C$7"))

Jusque-là tout va bien.
Par contre, malgré des tentatives dans tous les sens, je n’ai pas réussi à faire une MFC sur les cellules obtenues avec ta jolie fonction.
Après une importante recherche, je viens seulement de découvrir l’origine du problème : Ma petite fonction INDIRECT place un espace après le dernier caractère du nom de la commune et la MFC ne peut donc pas fonctionner.
Impossible de le voir, mes cellules sont formatées avec un centrage horizontal.
Ce n’est qu’avec un alignement à droite que j’ai découvert l’origine du problème.
Comment supprimer cet espace dans la petite fonction INDIRECT ?

D’avance, merci beaucoup.
Après cette découverte, je vais pouvoir aller dormir.
A toute à l’heure.
bolide7
07/03/2014, 14h15
bolide7

Bonjour papouclo,
Après une analyse détaillée pour comprendre d’où vient cet espace après le dernier caractère du nom de la commune, je crois avoir trouvé.
Initialement, je pensais qu’il provenait de la petite fonction INDIRECT.
Apparemment, il n’est est rien.
A priori, il ne vient ni de ta fonction SIERREUR, ni de ma petite fonction INDIRECT.
Je pense qu’il trouve son origine dans la cellule ou j’extrais le nom de la commune.
Cette cellule est elle-même une fonction CONCATENER du type : CONCATENER(A1;" ";A2)
A priori, lorsque je deconcatène, cet espace se retrouve dans la cellule obtenue.
Il doit exister 2 solutions :
- Soit éviter que cet espace vienne perturber ta fonction, peut-être en modifiant la fonction CONCATENER.
- Soit retirer l’espace ta fonction en modifiant ta fonction.
D’avance, merci beaucoup.
Bon après-midi sous un joli soleil d’été ou presque.
bolide7
07/03/2014, 14h43
papouclo

Bonjour,
Je viens de voir tes deux messages. Les fonctions INDIRECT, SIERREUR ne peuvent en aucun cas insérer sans demande d'espace supplémentaire.
Il provient donc d'une formule de concaténation.
Puisqu'il reste après le nom de ville obtenu, il y a donc deux espaces entre ce nom et Cedex dans la chaîne de la cellule C1.
Remplace " Cedex" (1 espace & "Cedex") par " Cedex" (2 espaces & "Cedex) comme premier argument de la fonction CHERCHE.
Cordialement
Claude
07/03/2014, 14h52
Daniel.C

Bonjour,

Peut-être (matricielle) :

Code:

=GAUCHE(D1;MAX(SI((STXT(GAUCHE(D1;CHERCHE(" Cedex";D1;1));LIGNE(INDIRECT("1:"&NBCAR(GAUCHE(D1;CHERCHE(" Cedex";D1;1)))));1)=MAJUSCULE(STXT(GAUCHE(D1;CHERCHE(" Cedex";D1;1));LIGNE(INDIRECT("1:"&NBCAR(GAUCHE(D1;CHERCHE(" Cedex";D1;1)))));1)));LIGNE(INDIRECT("1:"&NBCAR(GAUCHE(D1;CHERCHE(" Cedex";D1;1))))))))
07/03/2014, 15h11
Philippe Tulliez

Bonjour,
Pour supprimer les espaces inutiles, il y a lieu d'utiliser la fonction SUPPRESPACE équivalent VBA de Trim
Ainsi

Code:

=SUPPRESPACE(" Excel et Word sont des produits office ")

Donnera
'Excel et Word sont des produits office'

Par contre Trim conserve les espaces inutiles à l'intérieur de la chaîne
07/03/2014, 16h02
bolide7

Rebonjour papouclo,
C’est évidemment évident pour toi, mais pour moi il m’a fallu une grande soirée et une nuit de réflexion pour découvrir l’existence de cet espace et son origine.
Dans la fonction :

Code:

=SIERREUR(GAUCHE(INDIRECT("'"&$A9&"'!$C$7");CHERCHE(" Cedex";INDIRECT("'"&$A9&"'!$C$7"))-1);INDIRECT("'"&$A9&"'!$C$7"))

j’ai donc rajouté un deuxième espace devant le mot Cedex pour avoir :

Code:

=SIERREUR(GAUCHE(INDIRECT("'"&$A9&"'!$C$7");CHERCHE(" Cedex";INDIRECT("'"&$A9&"'!$C$7"))-1);INDIRECT("'"&$A9&"'!$C$7"))

Hélas, l’espace est toujours là.
A mon retour dans la soirée, je testerai les propositions de Daniel.C et de Philippe, que je remercie.
bolide7
07/03/2014, 20h15
bolide7

Bonsoir Daniel.C, Philippe et papouclo,

Merci beaucoup pour vos propositions respectives.

MESSAGE POUR DANIEL :
Ta longue formule fonctionne très bien en matricielle (pour information, en normal, elle donne la première lettre de la commune).
Cette même formule adaptée à ma situation ne marche plus.
Pourtant, dans la formule suivante, j’ai seulement remplacé D1 par INDIRECT("'"&$A9&"'!$C$7").

Code:

=GAUCHE(INDIRECT("'"&$A9&"'!$C$7");MAX(SI((STXT(GAUCHE(INDIRECT("'"&$A9&"'!$C$7");CHERCHE(" Cedex"; INDIRECT("'"&$A9&"'!$C$7");1));LIGNE(INDIRECT("1:"&NBCAR(GAUCHE(INDIRECT("'"&$A9&"'!$C$7");CHERCHE(" Cedex"; INDIRECT("'"&$A9&"'!$C$7");1)))));1)=MAJUSCULE(STXT(GAUCHE(INDIRECT("'"&$A9&"'!$C$7");CHERCHE(" Cedex"; INDIRECT("'"&$A9&"'!$C$7");1));LIGNE(INDIRECT("1:"&NBCAR(GAUCHE(INDIRECT("'"&$A9&"'!$C$7");CHERCHE(" Cedex"; INDIRECT("'"&$A9&"'!$C$7");1)))));1)));LIGNE(INDIRECT("1:"&NBCAR(GAUCHE(INDIRECT("'"&$A9&"'!$C$7");CHERCHE(" Cedex"; INDIRECT("'"&$A9&"'!$C$7");1))))))))

MESSAGE POUR PHILPPE :
Ta formule permet d’adapter la proposition de papouclo.
Dans mon fichier avec la petite fonction INDIRECT elle donne :

Code:

=SUPPRESPACE(SIERREUR(GAUCHE(INDIRECT("'"&$A9&"'!$C$7");CHERCHE(" Cedex"; INDIRECT("'"&$A9&"'!$C$7"))-1); INDIRECT("'"&$A9&"'!$C$7")))

MESSAGE POUR PAPOUCLO :
Pourrais-tu m’expliquer la différence entre tes 2 propositions ?
De mon côté, et comme indiqué précédemment, je n’ai pas vu de différence dans le résultat entre :

Code:

=SIERREUR(GAUCHE(A1;CHERCHE(" Cedex";A1)-1);A1)

Code:

=SUBSTITUE(SIERREUR(GAUCHE(A1;CHERCHE(" Cedex";A1)-1);A1);"- ";"")

Quelle devrait être la différence ?
Il doit pourtant y en avoir une !

Grand merci à vous 3 pour votre aide.
Très bonne soirée.
bolide7
08/03/2014, 08h44
papouclo

Bonjour,
J’ai mal interprété ton premier message.
J’ai cru que les deux caractères "- " (un tiret et un espace) faisaient partie des chaines à traiter alors qu’il s’agissait d’une puce devant chaque élément de la liste de possibilités que tu énonçais.
Ces deux caractères n’existant pas au début d’une chaîne à traiter ne se retrouvent pas dans le résultat obtenu avec la fonction GAUCHE de la première formule,
contrairement à ce que j’avais prévu (voir ma première réponse).
N’existant pas dans la chaine obtenue avec la fonction GAUCHE, la fonction SUSTITUE (2ème formule) n’a aucune action puisqu’elle ne rencontre pas le texte "- ".
La deuxième formule est donc inutile.
Cordialement
Claude
08/03/2014, 10h07
Daniel.C

Bonjour,

Citation:

Cette même formule adaptée à ma situation ne marche plus.

Bon, je crois que tu as ta réponse par d'autres contributeurs, sinon en cherchant bien, je peux faire plus compliqué :D
08/03/2014, 17h40
bolide7

Bonjour papouclo et Daniel.C,

MESSAGE POUR PAPOUCLO :
Tout s’explique ou presque !
Je comprends donc mieux ta fonction SUBSTITUE et ton souhait de vouloir remplacer "- " par "".

MESSAGE POUR DANIEL :
C’est vrai que le mixte de la proposition de PAPOUCLO améliorée avec le SUPPRESPACE de Philippe est beaucoup plus courte.

Bonne fin d’après-midi, peut-être rugbystique pour les sportifs !
On y croit !
Encore merci beaucoup pour vos contributions respectives.
bolide7