XL 2021 Formules que je n'arrive pas à faire

Usine à gaz · 22 Septembre 2023

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 · 22 Septembre 2023

Bonjour Lionel, Phil69970, StagExcelle, laurent950,

Formule en C2, à tirer vers le bas :

Code:

=SUBSTITUE(SUBSTITUE(GAUCHE(B2;6);" ";);CAR(160);)

Formule matricielle en D2, à tirer vers le bas :

Code:

=SUPPRESPACE(JOINDRE.TEXTE("";VRAI;SIERREUR(SI((CODE(STXT(B2;6+LIGNE($1:$99);1))<>160)*(CODE(STXT(B2;6+LIGNE($1:$99);1))<>63);STXT(B2;6+LIGNE($1:$99);1);"");"")))

JOINDRE.TEXTE existe à partir d'Excel 2019.

Sur Excel 2021 la validation matricielle n'est pas nécessaire.

A+

job75 · 23 Septembre 2023

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

Usine à gaz · 23 Septembre 2023

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 · 23 Septembre 2023

Merci Lionel, donc JM se trompait :

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

Usine à gaz · 23 Septembre 2023

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 · 23 Septembre 2023

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 · 23 Septembre 2023

Staple1600 à dit:
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 · 23 Septembre 2023

bonjour lionel
chez moi ton fichier post #1

VB:

=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 · 23 Septembre 2023

Re

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

Staple à dit:
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 · 23 Septembre 2023

Staple1600 à dit:
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 · 23 Septembre 2023

@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 · 23 Septembre 2023

Staple1600 à dit:
@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 · 23 Septembre 2023

@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 · 23 Septembre 2023

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 · 23 Septembre 2023

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

PowerQuery:

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 · 23 Septembre 2023

Re

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

PowerQuery:

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"

XL 2021 Formules que je n'arrive pas à faire

Usine à gaz

XLDnaute Barbatruc

Pièces jointes

job75

job75

XLDnaute Barbatruc

Usine à gaz

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Usine à gaz

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

Usine à gaz

XLDnaute Barbatruc

Pièces jointes

patricktoulon

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

Usine à gaz

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

Usine à gaz

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

Discussions similaires