XL 2021 Formules que je n'arrive pas à faire

[Usine à gaz]

Bonjour à toutes et à tous 🙂

Malgré toutes mes tentatives et mes recherches, je n'y suis pas arrivé.

Dans le fichier Excel joint, à partir d'un mail, j'ai copié des codes postaux avec noms des communes.
Le collage s'affiche dans une seule colonne (B) comme ceci :
- de la ligne 02 à la ligne 092 : "52 120      aizanville"
- de la ligne 93 à la ligne130 : "10200 lignol le chateau"

J'ai besoin de mettre ces valeurs sur 2 colonnes (C et D) comme ceci :
Colonne C : les codes postaux,
Colonne D : les noms des communes,

Si vous aviez la solution, ça m'arrangerait bien 🙂

En cas, je joins le fichier...
Un grand MERCI à tous
🙂

 

[job75]

Lionel peux-tu répondre à ma dernière question ?

 

[Usine à gaz]

Désolé Gérard, j'avais pas vu
Réponse à "PS : Lionel faut-il la validation matricielle par Ctrl+Maj+Entrée sur ton Excel 2021 ?" du #Post 15

J'ai remplacé la matricielle par formule "normale" et ça fonctionne aussi.
🙂

 

[job75]

Merci Lionel, donc JM se trompait :

NB: Pour XL 2021, je crois que la validation doit être matricielle
(ce qui n'est pas le cas avec O365)

 

[Usine à gaz]

Pas de merci Gérard 🙂
C'est moi qui t'en dois un "GROS WAGON"
Et de plus j'aurais du répondre sans que tu ais à me relancer.
Mais en ce moment, j'ai la tête avec trop de soucis, d'où mes absences.

"donc JM se trompait" : Il semblerait car ça fonctionne très bien sur Office 2021 sans matricielle.
🙂

 

[Staple1600]

Re

@job75
Je parlais uniquement de la formule que j'ai posté dans le message#11
Et j'ai parlé de croyance, pas de certitude.
Tout comme j'ai cru que tu répondrais à mon MP...
🙄

@Usine à gaz
Tu as testé la formule du message#11 en non matriciel ?

 

[Usine à gaz]

Re

@job75
Je parlais uniquement de la formule que j'ai posté dans le message#11
Et j'ai parlé de croyance, pas de certitude.
Tout comme j'ai cru que tu répondrais à mon MP...
🙄

@Usine à gaz
Tu as testé la formule du message#11 en non matriciel ?

Bjr JM 🙂
OUi j'ai testé.
la formule donne : "52 120 aizanville" et j'ai besoin de "aizanville"
Voire fichier joint Feuille "JM2"
Voire aussi : aussi en Feuille "Gérard2" formule sans matricielle
🙂

 

[patricktoulon]

bonjour lionel
chez moi ton fichier post #1

=SUBSTITUE(B2;CAR(32);) NE FONCTIONNE PAS
=SUBSTITUE(B2;CAR(160);)NE FONCTIONNE PAS

pourtant si je lis caractère par caractère en vba c'est bien des chr(32) et il y en a 5 exactement

 

[Staple1600]

Re

@Usine à gaz
Chacune ses besoins 😉
Moi, j'aurais besoin que tu lises mes messages de A à Z

Ensuite on fait dans le classique avec SUBSTITUE, GAUCHE et DROITE
Et là tu devrais savoir faire, Lionel 😉

Et apparemment, tu sais le faire puisque je n'ai jamais écrit cette formule qui se trouve dans JM2
=CNUM(SUBSTITUE(SUBSTITUE(GAUCHE(B2;6);" ";);CAR(160);))
😉

 

[Usine à gaz]

Re

@Usine à gaz
Chacune ses besoins 😉
Moi, j'aurais besoin que tu lises mes messages de A à Z


Et apparemment, tu sais le faire puisque je n'ai jamais écrit cette formule qui se trouve dans JM2
=CNUM(SUBSTITUE(SUBSTITUE(GAUCHE(B2;6);" ";);CAR(160);))
😉

OUI mais je ne parle pas de celle-là, je parle de ta formule :
=SUPPRESPACE(CONCAT(SI(ESTNUM(CHERCHE(STXT(B2;LIGNE(B$1:INDEX(B:B;NBCAR(B2)));1);" 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"));STXT(B2;LIGNE(B$1:INDEX(B:B;NBCAR(B2)));1);" ")))
🙂

 

[Staple1600]

@Usine à gaz
Si il devrait y avoir un onglet JM3
C'est ces formules qu'on y trouverait

En E2
=SUBSTITUE(GAUCHE(D2;TROUVE(" ";D2;4));" ";"")*1
Pour F2
=SUPPRESPACE(STXT(D2;7;9^9))

😉

Comme je disais dans le message#11

Deux formules de "nettoyage"

La 1ère formule ne sevrait qu'à faire le ménage
Ensuite il fallait deux autres formules dans deux colonnes

D'où mon invitation à ce que tu cogites sur ces deux formules 😉

Ensuite on fait dans le classique avec SUBSTITUE, GAUCHE et DROITE
Et là tu devrais savoir faire, Lionel 😉

 

[Usine à gaz]

@Usine à gaz
Si il devrait y avoir un onglet JM3
C'est ces formules qu'on y trouverait

En E2
=SUBSTITUE(GAUCHE(D2;TROUVE(" ";D2;4));" ";"")*1
Pour F2
=SUPPRESPACE(STXT(D2;7;9^9))

😉

Comme je disais dans le message#11
La 1ère formule ne sevrait qu'à faire le ménage
Ensuite il fallait deux autres formules dans deux colonnes

D'où mon invitation à ce que tu cogites sur ces deux formules 😉
Ensuite on fait dans le classique avec SUBSTITUE, GAUCHE et DROITE
Et là tu devrais savoir faire, Lionel 😉

Comme ça, c'est bon
Merci à toi 😉

 

[Staple1600]

@Usine à gaz
Tu étais en mesure de trouver ces deux formules par toi-même, non ?
😉

(depuis le temps que tu fréquentes XLD, tu en as bouffé de la formule)

 

[Staple1600]

Spoiler: aparté pour les croyants

Précisions
Là où je me suis trompé, c'est prendre pour agent comptant ce que qu'on peut lire sur le net
Comme je l'ai précisé, la formule du post#11 venait du web anglophone

Alors quand je lis CSE-entered formula, je traduis : CTRL+Shift+Enter formula
[/aparté]

Pour la question, il reste aussi la piste avec FILTRE.XML
(histoire de finir l'aprés-midi dans Excel)

 

[Staple1600]

Re

@Usine à gaz
Puisque tu as XL 2021, tu dois avoir PowerQuery (aka PQ)
(j'ai laissé tombé la piste FILTRE.XML)

Voici donc un petit bout de code M qui fait le job

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Colonne1", type text}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié", "Personnalisé", each Text.Select([Colonne1],{"A".."z","0".."9"," "})),
    #"Diviser la colonne selon les transitions de caractères" = Table.SplitColumn(#"Personnalisée ajoutée", "Personnalisé", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Personnalisé.1", "Personnalisé.2", "Personnalisé.3"}),
    #"Colonnes fusionnées" = Table.CombineColumns(#"Diviser la colonne selon les transitions de caractères",{"Personnalisé.1", "Personnalisé.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Fusionné"),
    #"Valeur remplacée" = Table.ReplaceValue(#"Colonnes fusionnées"," ","",Replacer.ReplaceText,{"Fusionné"}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Valeur remplacée",{"Colonne1"})
in
    #"Colonnes supprimées"

Ce qui donne ce résultat


PS: je suis sûr qu'on peut faire plus propre et plus concis.
Donc si les PowerQueristes du forum passent par ici, je suis pas contre un petit débroussaillage 😉

 

[Staple1600]

Re

Pour faire place nette dans mon PQ, avant d'aller au dodo 😉

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    #"Personnalisée ajoutée" = Table.AddColumn(Source, "Personnalisé", each Text.Select([Colonne1],{"0".."9"})),
    #"Type modifié" = Table.TransformColumnTypes(#"Personnalisée ajoutée",{{"Personnalisé", Int64.Type}}),
    #"Personnalisée ajoutée1" = Table.AddColumn(#"Type modifié", "Personnalisé.1", each Text.Select([Colonne1],{"A".."z"," "})),
    #"Espaces supprimés" = Table.TransformColumns(#"Personnalisée ajoutée1",{{"Personnalisé.1", Text.Trim, type text}}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Espaces supprimés",{"Colonne1"})
in
    #"Colonnes supprimées"