les retours chariot dans une cellule

[nicolase78]

Bonjour à tous,

Je travais sur un fichier avec plus de 2000 lignes et qui contient dans les cellules des retours chariot.

Je voudrais savoir si il y avait une méthode simple pour que les infos séparés par des retours chariot dans une cellule soit crée dans une nouvelle cellule.

Si j'ai une cellule avec deux retours chariot, elle crée alors 3 cellules comprenant les informations de la cellule avec comme délimiteur le retour chariot.


Pour l'instant j'ai vu qu'en copiant les cellules dans un fichier texte et en important ce fichier texte dans l'excel je pouvais avoir ce que je voulais mais je pense qu'il y a plus simple (surtout que ça créé des cellules avec des guillements, je suis obligé de passer par la fonction substitue après...)

J'ai mis un fichier d'exemple au cas où je n'ai pas été assez claire.


Merci à tous par avance.

 

[Kiseki]

Re : les retours chariot dans une cellule

Bonsoir,

Utilise l'outil convertisseur en délimitant par : Autre ALT+010

Qui est en fait le caractère de ALT+ENTRER

Ceci à très bien fonctionné.

[EDIT]
Ce n'est pas encore ça, alors voici une macro pour ce que tu désire :

Sub Transpose2()
Dim Plage As Range, F As Worksheet, Cel As Range

Set Plage = Application.InputBox("Sélectionner la plage à transposer", Type:=8)

Application.ScreenUpdating = False
Set F = Sheets.Add(, ActiveSheet)

Plage.Copy F.Range("A1")

F.UsedRange.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="" & Chr(10) & "", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
        TrailingMinusNumbers:=True

F.Columns("A").Insert Shift:=xlToRight

For Each Cel In F.UsedRange
    If Cel <> "" Then F.Range("A35536").End(xlUp).Offset(1) = Cel
Next Cel
F.UsedRange.Offset(, 1).ClearContents
F.Range("A2").CurrentRegion.Copy
Application.ScreenUpdating = True
End Sub

En gros tu sélectionnera la plage, il va la copier et faire les manipulation dans une nouvelle feuille et copier le résultat.

Soit MACRO, sélectionner la plage, Coller ou tu souhaite.


J'ai pas pu aller dormir sans régler ça ^^.

 

[job75]

Re : les retours chariot dans une cellule

Bonjour nicolase78, Kiseki, le forum,

Voir le fichier joint avec cette macro :

Sub Separe()
Dim tablo, t, liste$, s
tablo = Application.Transpose(Range("A1", [A1].End(xlDown))) 'plus rapide
For Each t In tablo
  liste = liste & vbLf & t 'vbLf = Chr(10)
Next
s = Split(Mid(liste, 2), vbLf)
'restitution à partir de A9
[A9].Resize(UBound(s) + 1) = Application.Transpose(s)
End Sub

A+

 

[job75]

Re : les retours chariot dans une cellule

Re,

Au cas où aucune cellule sous A1 n'est remplie, utiliser plutôt :

Sub Separe()
Dim tablo, t, liste$, s
tablo = Application.Transpose(Range("A1", [A1].End(xlDown))) 'plus rapide
If Application.CountA([A:A]) < 2 Then tablo = [A1:A2]
For Each t In tablo
liste = liste & vbLf & t 'vbLf = Chr(10)
Next
s = Split(Mid(liste, 2), vbLf)
'restitution à partir de A9
[A9].Resize(UBound(s) + 1) = Application.Transpose(s)
End Sub

Fichier (2).

A+

 

[job75]

Re : les retours chariot dans une cellule

Re,

Si l'on veut la restitution à partir de A1 il vaut mieux utiliser End(xlUp) :

Sub Separe()
Dim tablo, t, liste$, s
tablo = Application.Transpose(Range("A1:A2", Cells(Rows.Count, 1).End(xlUp))) 'plus rapide
For Each t In tablo
liste = liste & vbLf & t 'vbLf = Chr(10)
Next
s = Split(Mid(liste, 2), vbLf)
'restitution à partir de A1
[A1].Resize(Application.Min(UBound(s) + 1, Rows.Count)) = Application.Transpose(s)
End Sub

Fichier (3).

A+

 

[nicolase78]

Re : les retours chariot dans une cellule

Oula beaucoup de réponse.
Je suis sûr que je vais trouver mon bonheur là dedans.

merci à tous.

 

[job75]

Re : les retours chariot dans une cellule

Re,

Sur Excel 2003 j'ai testé sur un tableau de 65535 cellules, chaque cellule avec 3 lignes (2 retours chariot).

On obtient donc un vecteur s de 65535 x 3 = 196605 éléments.

Malheureusement la fonction Transpose refuse les vecteurs de plus de 65536 éléments...

Il faut donc utiliser cette macro :

Sub Separe()
Dim tablo, i&, liste$, s, E()
tablo = Range("A1:A2", Cells(Rows.Count, 1).End(xlUp)) 'plus rapide
For i = 1 To UBound(tablo)
  liste = liste & vbLf & tablo(i, 1) 'vbLf = Chr(10)
Next
s = Split(Mid(liste, 2), vbLf)
'création du tableau E (transposé de s)
ReDim E(UBound(s), 0)
For i = 0 To UBound(s)
  E(i, 0) = s(i)
Next
'restitution à partir de A1
[A1].Resize(Application.Min(UBound(s) + 1, Rows.Count)) = E
End Sub

La durée d'exécution de la macro est assez longue : 327 s (plus de 5 mn).

Fichier (4).

A+

 

[job75]

Re : les retours chariot dans une cellule

Re,

Bon on y arrive, voici LA solution (avec un tableau de plus) :

Sub Separe()
Dim tablo, i&, E1(), liste$, s, E2()
tablo = Range("A1:A2", Cells(Rows.Count, 1).End(xlUp)) 'plus rapide
'création du tableau E1 (transposé de tablo)
ReDim E1(1 To UBound(tablo))
For i = 1 To UBound(tablo)
  E1(i) = tablo(i, 1)
Next
liste = Join(E1, vbLf) 'vbLf = Chr(10)
s = Split(liste, vbLf)
'création du tableau E2 (transposé de s)
ReDim E2(UBound(s), 0)
For i = 0 To UBound(s)
  E2(i, 0) = s(i)
Next
'restitution à partir de A1
[A1].Resize(Application.Min(UBound(s) + 1, Rows.Count)) = E2
End Sub

Sur les 65535 cellules la durée d'exécution passe à 1,6 s - au lieu de 327 s...

Fichier (5).

Nota : bien sûr voir l'aide VBA sur les fonctions Join et Split.

A+

 

[nicolase78]

Re : les retours chariot dans une cellule

Vraiment merci beaucoup à tous pour votre rapidité et la qualité de vos fichiers. (merci Job75 pour m'avoir avertit par message privé)

 

[job75]

Re : les retours chariot dans une cellule

Bonjour à tous,

Pour ceux qui voudraient tester la rapidité de leur ordi sur 65535 cellules j'ai déposé le fichier (zippé) sur cjoint.com :

http://cjoint.com/?AKsmoCEiwSa

A+

 

[MJ13]

Re : les retours chariot dans une cellule

Bonjour à tous

Merci Job pour cette dernière solution. Mais pourquoi passer par Cjoint. Une petite macro pour recopier vers le bas et le tour était joué .

Autre chose: Dans ta macro qui contient 65535 cellules, Ok. Mais alors, on perd des données

Mais bon, c'est pour le fun, c'est plus pour te taquiner .

Pour la durée 1,4 secondes .

 

[job75]

Re : les retours chariot dans une cellule

Bonjour Michel

Mais pourquoi passer par Cjoint. Une petite macro pour recopier vers le bas et le tour était joué .

Pas besoin de macro :

- copier ce qu'il y a dans la barre de formule de A2

- touche F5 => A1:A65535 => OK

- clic dans la barre de formule, effacer le texte, puis touches Ctrl+V, puis Ctrl+Entrée.

Mais tout le monde ne sait pas faire ça...

Mais alors, on perd des données

Sur Excel 2003 bien sûr (65536 lignes disponibles).

Sur Excel 2007/2010 enregistrer le fichier en .xlsm, pas de problème pour remplir 65535 x 3 = 196605 cellules.

A+