Petite fonction du jour : comment séparer en ligne et en colonnes.
Sur Excel 365 Insider, ca se fait en une seule formule (fractionner.texte), mais en attendant, voici ce qui devrait faire l'affaire pour 365 (et 2021, je pense) :
Attention, il faut exactement le bon nombre de séparateur dans les données initiales (voir copie d'écran)
=LET(
plage;A1:A3;
DelimiteurL;".";
DelimiteurC;",";
resultat1;FILTRE.XML("<b><a>"&SUBSTITUE(JOINDRE.TEXTE(DelimiteurL;;plage);DelimiteurL;"</a><a>")&"</a></b>";"//a");
INDEX(TRANSPOSE(
FILTRE.XML("<b><a>"&SUBSTITUE(JOINDRE.TEXTE(DelimiteurC;;resultat1);DelimiteurC;"</a><a>")&"</a></b>";"//a")
);
SEQUENCE(LIGNES(resultat1);
NBCAR(INDEX(resultat1;1;1))-NBCAR(
SUBSTITUE(INDEX(resultat1;1;1);DelimiteurC;)
)+1)))
et avec une Lambda (pour plus d'informations sur la création de Lambda, c'est par ici), par exemple L.FRACTIONNER.TEXTE :
=LAMBDA(plage;DelimiteurC;DelimiteurL; LET(resultat1;FILTRE.XML("<b><a>"&SUBSTITUE(JOINDRE.TEXTE(DelimiteurL;;plage);DelimiteurL;"</a><a>")&"</a></b>";"//a"); INDEX(TRANSPOSE( FILTRE.XML("<b><a>"&SUBSTITUE(JOINDRE.TEXTE(DelimiteurC;;resultat1);DelimiteurC;"</a><a>")&"</a></b>";"//a") ); SEQUENCE(LIGNES(resultat1); NBCAR(INDEX(resultat1;1;1))-NBCAR( SUBSTITUE(INDEX(resultat1;1;1);DelimiteurC;) )+1))))
bonjour,
j’ai voulu m’inspirer de votre fonction ( sur office 2021 ) …
ma modification “fonctionne” sauf que le resultat
s’affiche seulement sur une colonne ( cela ne decale pas sur d’autre colonne )
et si la donnee dans la cellule a separer est NULLE ( cellule vierge ),
les donnees ne sont pas décalées dans la bonne ligne …
bref j’ai toutes les données separées en resultat sur une seule et meme colonne, le resultat ne s’affiche pas sur la ligne voulu …
pourrait-on passer en parametre une cellule au lieu d’une plage ?
=LET(plage;U2:U3133;DelimiteurL;”#”;DelimiteurC;”,”;resultat1;FILTRE.XML(““&SUBSTITUE(JOINDRE.TEXTE(DelimiteurL;;plage);DelimiteurL;”“)&”“;”//a”);
INDEX(TRANSPOSE(FILTRE.XML(““&SUBSTITUE(JOINDRE.TEXTE(DelimiteurC;;resultat1);DelimiteurC;”“)&”“;”//a”));SEQUENCE(LIGNES(resultat1);NBCAR(INDEX(resultat1;1;1))-NBCAR(SUBSTITUE(INDEX(resultat1;1;1);DelimiteurC;))+1)))
comme cela :
=LET(cellule;$U2;DelimiteurL;”#”;DelimiteurC;”,”;resultat1;FILTRE.XML(““&SUBSTITUE(JOINDRE.TEXTE(DelimiteurL;;plage);DelimiteurL;”“)&”“;”//a”);
et recopier la fonction sur chaque ligne ?
Pas le temps de tester aujourd’hui, mais vérifiez si le problème n’est pas lié au séparateur qui est un #.
Ca fonctionne bien avec le #. Pourriez vous faire un exemple simple qui ne fonctionne pas ?
Ma formule :
=LET(
plage;A1:A2;
DelimiteurL;”.”;
DelimiteurC;”#”;
resultat1;FILTRE.XML(““&SUBSTITUE(JOINDRE.TEXTE(DelimiteurL;;plage);DelimiteurL;”“)&”“;”//a”);
INDEX(TRANSPOSE(
FILTRE.XML(““&SUBSTITUE(JOINDRE.TEXTE(DelimiteurC;;resultat1);DelimiteurC;”“)&”“;”//a”)
);
SEQUENCE(LIGNES(resultat1);
NBCAR(INDEX(resultat1;1;1))-NBCAR(
SUBSTITUE(INDEX(resultat1;1;1);DelimiteurC;)
)+1)))
ah !
j’avais mis ce separateur “#” pour le pas utiliser votre separateur “.”
car je veux separer des adresse mail qui comporte un “.” evidament ….
je vais essayer de remplacer # par ;
je peut vous envoyer le fichier excel xlsx sous office 2021
si vous voulez … ce serait mon meilleur exemple !
maintenant, il n’y a pas d’urgence pour moi …
vous regarderez ( ou pas ) quand vous pourrez
d’avance merci
Oui, si vous voulez. Cela m’intéresse et peut faire émerger de nouvelles idées.
je vous envoie ca comment ?
Sur cette adresse : contact@xlerateur.com