XL 2019 Cellule répartie en 4

E

EliotNaiss

XLDnaute Nouveau
Bonjour à vous.
Je viens chercher de l'aide car je ne m'en sors pas
icon_sad.gif

Dans une cellule j'ai des informations issues d'extraction.
C'est du genre, au maxi sur 4 lignes, a priori avec des retours lignes
icon_sad.gif
:

"Non renseigné
F1602 - Électricité bâtiment
N4101 - Conduite de transport de marchandises sur longue distance
N4105 - Conduite et livraison par tournées sur courte distance"

ou

"G1602 - Personnel de cuisine
F1602 - Électricité bâtiment
N4101 - Conduite de transport de marchandises sur longue distance
N4105 - Conduite et livraison par tournées sur courte distance"

Selon le contenu je veux répartir la donnée en 4 cellules...

Ce qui pourrait donner avec 1 ligne renseignée :

Nonrenseigné
ou
G1602 - Personnel de cuisine


Ce qui pourrait donner avec 2 lignes renseignées :

NonrenseignéF1602 - Électricité bâtiment
ou
G1602 - Personnel de cuisineF1602 - Électricité bâtiment

Ce qui pourrait donner avec 3 lignes renseignées :

NonrenseignéF1602 - Électricité bâtimentN4101 - Conduite de transport de marchandises sur longue distance
ou
G1602 - Personnel de cuisineF1602 - Électricité bâtimentN4101 - Conduite de transport de marchandises sur longue distance


Ce qui pourrait donner avec 4 lignes renseignées :

NonrenseignéF1602 - Électricité bâtimentN4101 - Conduite de transport de marchandises sur longue distanceN4105 - Conduite et livraison par tournées sur courte distance
ou
G1602 - Personnel de cuisineF1602 - Électricité bâtimentN4101 - Conduite de transport de marchandises sur longue distanceN4105 - Conduite et livraison par tournées sur courte distance


Mon tableau d'origine est assez grand, je cherche la formule idéale, et peut-être serait-il possible d'en faire une fonction ?

Grand merci pour le temps que vous pourrez passer sur mon cas
icon_redface.gif



Bien à vous
 
Trier par date Trier par votes
E

EliotNaiss

XLDnaute Nouveau
@
sylvanu à dit:
Fonction perso :
La syntaxe est =Separe(Chaine,N°) , par ex :
=separe($B7;2) va prendre la chaine B7 et en extraire l'item N°2 soit "J1202 - Pharmacie"
Le code est :
VB: 
Function Separe(C$, N%)
    Tablo = Split(C, Chr(10))
    If N <= 1 + UBound(Tablo) Then
        Separe = Tablo(N - 1)
    End If
    If Separe = 0 Then Separe = ""
End Function
On récupère la chaine à traiter C$, les sauts de ligne dans les cellules est le caractère ASCII 10.
Donc on sépare cette chaine avec le Split. Par ex pour B11 on obtient le tableau suivant :
Regarde la pièce jointe 1155133
ensuite il suffit de lire l'item demandé, et de le remonter avec : Separe = Tablo(N - 1)
Si le Tablo ne contenait rien alors il remonte 0, d'où la ligne If Separe = 0 Then Separe = "" .
( pour les fonctions perso, un petit tuto si ça vous interesse : LIEN

Macro : ( d'ailleurs en commentant, j'ai vu qu'il restait un résidu inutile, d'où cette nouvelle PJ )
Code: 
Sub Découpe()
    Dim DL%, C%, L%, Tablo
    Application.ScreenUpdating = False                  ' Fige l'écran
    DL = Range("B65500").End(xlUp).Row                  ' Recherche de la dernière ligne utilisée
    For L = 7 To DL                                     ' De la ligne 7 à la fin
        Tablo = Split(Cells(L, 2), Chr(10))             ' On sépare la chaine avec les reours à la ligne
        For C = 0 To UBound(Tablo)                      ' Pour tous les éléments du tableau ( Ubound donne la taille )
            Cells(L, C + 3) = Tablo(C)                  ' On écrit l'item désiré
        Next C
    Next L
    [A1].Select                                         ' On sélectionne A1, par pure esthètique.
End Sub
Cliquez pour agrandir...
Je vais lire et essayer de comprendre...
Merci beaucoup.
Est-ce que ce serait compliqué de créer une formule spécifique dans chaque cellule (des 4 colonnes) plutôt que passer par une fonction ?
En fait je prépare à la maison, et en y réfléchissant de plus près, j'ai peur que les macros soient bloqués au boulot avec 365 en cloud 😖
 
Vote positif 0 Vote négatif
sylvanu

sylvanu

XLDnaute Barbatruc
Supporter XLD
A votre avis, pourquoi nous avons tous proposé du VBA ?
Car en formules c'est ch...t. :mad:
En PJ un essai avec :
VB: 
Item 1:
=SI(B7="";"";SIERREUR(STXT($B7;1;CHERCHE(CAR(10);$B7));$B7))
Item 2:
=SIERREUR(SI(CHERCHE(CAR(10);STXT(B7;NBCAR(C7);1000))>1;SIERREUR(STXT(STXT(STXT(B7;NBCAR(C7);1000);1+CHERCHE(CAR(10);STXT(B7;NBCAR(C7);1000));1000);1;CHERCHE(CAR(10);STXT(STXT(B7;NBCAR(C7);1000);1+CHERCHE(CAR(10);STXT(B7;NBCAR(C7);1000));1000)));"");SIERREUR(STXT(STXT(B7;1+NBCAR(C7);1000);1;CHERCHE(CAR(10);STXT(B7;1+NBCAR(C7);1000)));STXT(STXT(B7;1+NBCAR(C7);1000);1;1000)));"")
Item 3:
=SIERREUR(STXT(STXT(B7;1+NBCAR(C7)+NBCAR(D7);1000);1;CHERCHE(CAR(10);STXT(B7;1+NBCAR(C7)+NBCAR(D7);1000)));STXT(STXT(B7;1+NBCAR(C7)+NBCAR(D7);1000);1;1000))
Item 4:
=SIERREUR(STXT(STXT(B7;1+NBCAR(C7)+NBCAR(D7);1000);CHERCHE(CAR(10);STXT(B7;1+NBCAR(C7)+NBCAR(D7);1000));1000);"")
Evidemment indigeste, ingérable, et surtout non maintenable.

Je n'assure pas la maintenance de ça ! 😂

NB: je ne connais pas LAMBDA, je suis toujours sous XL2007. :)
 

Pièces jointes

  • Cellule répartie en 4 (V7).xlsm
    24.1 KB · Affichages: 3
Vote positif 0 Vote négatif
E

EliotNaiss

XLDnaute Nouveau
sylvanu à dit:
A votre avis, pourquoi nous avons tous proposé du VBA ?
Car en formules c'est ch...t. :mad:
En PJ un essai avec :
VB: 
Item 1:
=SI(B7="";"";SIERREUR(STXT($B7;1;CHERCHE(CAR(10);$B7));$B7))
Item 2:
=SIERREUR(SI(CHERCHE(CAR(10);STXT(B7;NBCAR(C7);1000))>1;SIERREUR(STXT(STXT(STXT(B7;NBCAR(C7);1000);1+CHERCHE(CAR(10);STXT(B7;NBCAR(C7);1000));1000);1;CHERCHE(CAR(10);STXT(STXT(B7;NBCAR(C7);1000);1+CHERCHE(CAR(10);STXT(B7;NBCAR(C7);1000));1000)));"");SIERREUR(STXT(STXT(B7;1+NBCAR(C7);1000);1;CHERCHE(CAR(10);STXT(B7;1+NBCAR(C7);1000)));STXT(STXT(B7;1+NBCAR(C7);1000);1;1000)));"")
Item 3:
=SIERREUR(STXT(STXT(B7;1+NBCAR(C7)+NBCAR(D7);1000);1;CHERCHE(CAR(10);STXT(B7;1+NBCAR(C7)+NBCAR(D7);1000)));STXT(STXT(B7;1+NBCAR(C7)+NBCAR(D7);1000);1;1000))
Item 4:
=SIERREUR(STXT(STXT(B7;1+NBCAR(C7)+NBCAR(D7);1000);CHERCHE(CAR(10);STXT(B7;1+NBCAR(C7)+NBCAR(D7);1000));1000);"")
Evidemment indigeste, ingérable, et surtout non maintenable.

Je n'assure pas la maintenance de ça ! 😂

NB: je ne connais pas LAMBDA, je suis toujours sous XL2007. :)
Cliquez pour agrandir...
Bah oui je comprend bien votre étonnement...
S'il n'y avait que moi, je foncerais avec le VBA évidemment !
Mais si quelqu'un s'y connait avec Excel web...
En tous cas grand merci !!!
 
Vote positif 0 Vote négatif
mapomme

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonsoir à tous,

Une version par formules.
  • Une formule pour l'élément extrait n° 1 (colonne C) en C7 à recopier vers le bas
  • Une autre formule pour les autres éléments extraits n° 2 à 4 (ou plus le cas échéant), en D7, à recopier vers le bas et vers la droite
La formule en C7 à recopier vers le bas:
VB: 
=GAUCHE(B7;CHERCHE(CAR(10);B7 & CAR(10))-1)

La formule en D7 à recopier vers le bas et vers la droite :
VB: 
=SIERREUR(GAUCHE(STXT($B7&CAR(10);CHERCHE("|";SUBSTITUE($B7&CAR(10);CAR(10);"|";COLONNES($A:A)))+1;999);CHERCHE(CAR(10);STXT($B7&CAR(10);CHERCHE("|";SUBSTITUE($B7&CAR(10);CAR(10);"|";COLONNES($A:A)))+1;999);1));"")

Voir classeur joint.

Et comme ne disait pas @sylvanu, j'assure la maintenance de ça ! 😜😂
 

Pièces jointes

  • EliotNaiss- eclater texte- v1.xlsx
    18.4 KB · Affichages: 7
Dernière édition:
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Bonsoir EliotNaiss, le fil,

Voici une solution très simple.

1) Prenez le fichier du post #4 et en C7 entrez la formule =SUBSTITUE(B7;CAR(10):"µ")
- tirez-la sur C7:C11
- sélectionnez C7:C11, clic droit => Copier, clic droit => Collage spécial => Valeurs

C'est l'état où se trouve le fichier joint.

2) Ensuite C7:C11 étant sélectionnée, menu Données, commande Convertir
- Délimité
- séparateur => Autre => µ => Suivant => Terminer.

A+
 

Pièces jointes

  • Cellule répartie en 4.xlsx
    17.9 KB · Affichages: 1
Vote positif 0 Vote négatif
E

EliotNaiss

XLDnaute Nouveau
mapomme à dit:
Bonsoir à tous,

Une version par formules.
  • Une formule pour l'élément extrait n° 1 (colonne C) en C7 à recopier vers le bas
  • Une autre formule pour les autres éléments extraits n° 2 à 4 (ou plus le cas échéant), en D7, à recopier vers le bas et vers la droite
La formule en C7 à recopier vers le bas:
VB: 
=GAUCHE(B7;CHERCHE(CAR(10);B7 & CAR(10))-1)

La formule en D7 à recopier vers le bas et vers la droite :
VB: 
=SIERREUR(GAUCHE(STXT($B7&CAR(10);CHERCHE("|";SUBSTITUE($B7&CAR(10);CAR(10);"|";COLONNES($A:A)))+1;999);CHERCHE(CAR(10);STXT($B7&CAR(10);CHERCHE("|";SUBSTITUE($B7&CAR(10);CAR(10);"|";COLONNES($A:A)))+1;999);1));"")

Voir classeur joint.

Et comme ne disait pas @sylvanu, j'assure la maintenance de ça !😜😂
Cliquez pour agrandir...
Merci beaucoup du coup de main !!
 
Vote positif 0 Vote négatif
E

EliotNaiss

XLDnaute Nouveau
mapomme à dit:
Bonsoir à tous,

Une version par formules.
  • Une formule pour l'élément extrait n° 1 (colonne C) en C7 à recopier vers le bas
  • Une autre formule pour les autres éléments extraits n° 2 à 4 (ou plus le cas échéant), en D7, à recopier vers le bas et vers la droite
La formule en C7 à recopier vers le bas:
VB: 
=GAUCHE(B7;CHERCHE(CAR(10);B7 & CAR(10))-1)

La formule en D7 à recopier vers le bas et vers la droite :
VB: 
=SIERREUR(GAUCHE(STXT($B7&CAR(10);CHERCHE("|";SUBSTITUE($B7&CAR(10);CAR(10);"|";COLONNES($A:A)))+1;999);CHERCHE(CAR(10);STXT($B7&CAR(10);CHERCHE("|";SUBSTITUE($B7&CAR(10);CAR(10);"|";COLONNES($A:A)))+1;999);1));"")

Voir classeur joint.

Et comme ne disait pas @sylvanu, j'assure la maintenance de ça !😜😂
Cliquez pour agrandir...
 
Vote positif 0 Vote négatif
mapomme

mapomme

XLDnaute Barbatruc
Supporter XLD
Re,

Une version avec une seule formule.
La formule en C7 est à recopier vers la droite et vers le bas :
VB: 
=SIERREUR(GAUCHE(STXT(CAR(10)&$B7&CAR(10);CHERCHE("|";SUBSTITUE(CAR(10)&$B7&CAR(10);CAR(10);"|";COLONNES($A:A)))+1;999);CHERCHE(CAR(10);STXT(CAR(10)&$B7&CAR(10);CHERCHE("|";SUBSTITUE(CAR(10)&$B7&CAR(10);CAR(10);"|";COLONNES($A:A)))+1;999);1));"")
 

Pièces jointes

  • EliotNaiss- eclater texte- v2.xlsx
    18.3 KB · Affichages: 3
Vote positif 0 Vote négatif

Discussions similaires

J
  • Question
XL 2019 Calculer une liste de distances avec l'adresse exacte en utilisant l'api Matrix Google
Réponses
3
Affichages
577
crocrocro
C
I
  • Question
XL 2021 Incendie Pompier
Réponses
2
Affichages
330
ibraso
I
I
  • Question
Microsoft 365 Code VBA pour sélectionner une colonne sur 4
Réponses
13
Affichages
387
infernotronic
I
P
  • Question
Microsoft 365 garder en mémoire plusieurs listview d'un userform
Réponses
4
Affichages
218
patricktoulon
patricktoulon
L
  • Question
XL 2013 Attribuer un rang
2 3
Réponses
39
Affichages
2 K
R@chid
R@chid
Hayholten
  • Question
Microsoft 365 [MFC Excel] Simplification des Règles
Réponses
9
Affichages
459
lav
L
Y
  • Résolu(e)
XL 2016 Comment supprimer des chiffres + texte en fin de cellule
Réponses
2
Affichages
186
Yayanne76
Y
P
  • Résolu(e)
XL 2013 Mise en forme suite à utilisation de UserForm
Réponses
4
Affichages
414
PR.
P
P
  • Question
Microsoft 365 Automatisation avec création de fichier et d'onglets en fonction des valeurs
2
Réponses
15
Affichages
879
pene
P
M
  • Résolu(e)
Microsoft 365 Copier/coller - Ajout/suppression lignes automatiques
Réponses
6
Affichages
732
M.Ben
M

Membres actuellement en ligne

8 membres et 107 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 330
Messages
2 087 349
Membres
103 526
dernier inscrit
HEC