IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Excel Discussion :

Extraire le nom de la commune sans les informations qui suivent [XL-2013]


Sujet :

Excel

  1. #1
    Membre habitué
    Profil pro
    Inscrit en
    Juillet 2008
    Messages
    670
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Juillet 2008
    Messages : 670
    Points : 147
    Points
    147
    Par défaut 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 : Sélectionner tout - Visualiser dans une fenêtre à part
    =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 : Sélectionner tout - Visualiser dans une fenêtre à part
    =GAUCHE(A1;TROUVE(" ";A1)-1)
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =GAUCHE(A1;TROUVE(" ";A1))
    D’avance, merci beaucoup.
    Bonne après-midi à tous.
    bolide7

  2. #2
    Membre expert

    Homme Profil pro
    Retraité
    Inscrit en
    Juin 2012
    Messages
    1 564
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Alpes Maritimes (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Retraité
    Secteur : Enseignement

    Informations forums :
    Inscription : Juin 2012
    Messages : 1 564
    Points : 3 554
    Points
    3 554
    Billets dans le blog
    1
    Par défaut
    Bonjour,
    Si on veut une réponse comme "- PARIS" ou "- LA RIVIERE", ce que laisse supposer les formules essayées, utiliser la formule :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SIERREUR(GAUCHE(A1;CHERCHE(" Cedex";A1)-1);A1)
    Si on veut une réponse comme "PARIS" ou "LA RIVIERE", utiliser :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SUBSTITUE(SIERREUR(GAUCHE(A1;CHERCHE(" Cedex";A1)-1);A1);"- ";"")
    Cordialement
    Claude

  3. #3
    Membre habitué
    Profil pro
    Inscrit en
    Juillet 2008
    Messages
    670
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Juillet 2008
    Messages : 670
    Points : 147
    Points
    147
    Par défaut
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    =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

  4. #4
    Membre habitué
    Profil pro
    Inscrit en
    Juillet 2008
    Messages
    670
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Juillet 2008
    Messages : 670
    Points : 147
    Points
    147
    Par défaut
    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

  5. #5
    Membre expert

    Homme Profil pro
    Retraité
    Inscrit en
    Juin 2012
    Messages
    1 564
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Alpes Maritimes (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Retraité
    Secteur : Enseignement

    Informations forums :
    Inscription : Juin 2012
    Messages : 1 564
    Points : 3 554
    Points
    3 554
    Billets dans le blog
    1
    Par défaut
    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

  6. #6
    Expert éminent sénior
    Homme Profil pro
    aucune
    Inscrit en
    Septembre 2011
    Messages
    8 200
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : aucune

    Informations forums :
    Inscription : Septembre 2011
    Messages : 8 200
    Points : 14 346
    Points
    14 346
    Par défaut
    Bonjour,

    Peut-être (matricielle) :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =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))))))))
    Cordialement.

    Daniel

    La plus perdue de toutes les journées est celle où l'on n'a pas ri. Chamfort

  7. #7
    Rédacteur
    Avatar de Philippe Tulliez
    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    Janvier 2010
    Messages
    12 729
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur, développeur et consultant Excel, Access, Word et VBA

    Informations forums :
    Inscription : Janvier 2010
    Messages : 12 729
    Points : 28 555
    Points
    28 555
    Billets dans le blog
    53
    Par défaut
    Bonjour,
    Pour supprimer les espaces inutiles, il y a lieu d'utiliser la fonction SUPPRESPACE équivalent VBA de Trim
    Ainsi
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =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
    Philippe Tulliez
    Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau)
    Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Mon dernier billet : Utilisation de la fonction Dir en VBA pour vérifier l'existence d'un fichier

  8. #8
    Membre habitué
    Profil pro
    Inscrit en
    Juillet 2008
    Messages
    670
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Juillet 2008
    Messages : 670
    Points : 147
    Points
    147
    Par défaut
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    =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 : Sélectionner tout - Visualiser dans une fenêtre à part
    =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

  9. #9
    Membre habitué
    Profil pro
    Inscrit en
    Juillet 2008
    Messages
    670
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Juillet 2008
    Messages : 670
    Points : 147
    Points
    147
    Par défaut
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    =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 : Sélectionner tout - Visualiser dans une fenêtre à part
    =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 : Sélectionner tout - Visualiser dans une fenêtre à part
    =SIERREUR(GAUCHE(A1;CHERCHE(" Cedex";A1)-1);A1)
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =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

  10. #10
    Membre expert

    Homme Profil pro
    Retraité
    Inscrit en
    Juin 2012
    Messages
    1 564
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Alpes Maritimes (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Retraité
    Secteur : Enseignement

    Informations forums :
    Inscription : Juin 2012
    Messages : 1 564
    Points : 3 554
    Points
    3 554
    Billets dans le blog
    1
    Par défaut
    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

  11. #11
    Expert éminent sénior
    Homme Profil pro
    aucune
    Inscrit en
    Septembre 2011
    Messages
    8 200
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : aucune

    Informations forums :
    Inscription : Septembre 2011
    Messages : 8 200
    Points : 14 346
    Points
    14 346
    Par défaut
    Bonjour,

    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é
    Cordialement.

    Daniel

    La plus perdue de toutes les journées est celle où l'on n'a pas ri. Chamfort

  12. #12
    Membre habitué
    Profil pro
    Inscrit en
    Juillet 2008
    Messages
    670
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Juillet 2008
    Messages : 670
    Points : 147
    Points
    147
    Par défaut
    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

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [XL-2007] copy destination sans les formules qui sont dans certaines Cellules de la source
    Par scoubi77 dans le forum Macros et VBA Excel
    Réponses: 5
    Dernier message: 21/09/2014, 11h48
  2. [MySQL] Extraire le texte d'une phrase sans les chiffres
    Par lordlifen dans le forum PHP & Base de données
    Réponses: 1
    Dernier message: 03/09/2010, 14h25
  3. Réponses: 2
    Dernier message: 23/03/2009, 17h45
  4. Réponses: 1
    Dernier message: 30/03/2007, 17h38
  5. Ou trouver les informations qui defile lors du boot ?
    Par piff62 dans le forum Administration système
    Réponses: 2
    Dernier message: 17/03/2005, 18h19

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo