séparer du texte sur plusieurs

 Columns("D:G").ClearContents

Sub test()
Dim tabtemp As Variant
Dim TabRecup As Variant
Dim TabFinal() As Variant
Dim L As Integer, Item As Integer
Dim x As Integer
Dim rep As Integer
 rep = Application.CountA(Range("A2:A1000")) [COLOR=Green]'on teste s'il y a des données à convertir[/COLOR]
If rep < 1 Then Exit Sub  [COLOR=Green]'sinon on quitte[/COLOR]
'[COLOR=Green]ici on remplit le tableau[/COLOR]
tabtemp = Range("A2:A" & Range("A65536").End(xlUp).Row).Value
For L = 1 To UBound(tabtemp, 1) [COLOR=Green]'pour chaque ligne du tableau temporaire[/COLOR]
 TabRecup = Split(tabtemp(L, 1), Chr(32)) '[COLOR=Green]on récupére les éléments via Split dans un tableau[/COLOR]
 
 If UBound(TabRecup, 1) = 0 Then Exit Sub [COLOR=Green]'si pas de donnée on quitte[/COLOR]
 ReDim Preserve TabFinal(3, x) [COLOR=Green]'on redimensionne[/COLOR]
 For Item = 0 To 2 [COLOR=Green]'on transfere les trois premieres lignes[/COLOR]
      TabFinal(Item, x) = TabRecup(Item)
 Next
             x = x + 1 [COLOR=Green]'on ajoute une colonne[/COLOR]
     Erase TabRecup [COLOR=Green]'on efface le tableau[/COLOR]
  Next

Application.ScreenUpdating = False [COLOR=Green]'on inhibe le raffraichissement de la fenêtre[/COLOR]
      Range("A2").Resize(UBound(TabFinal, 2) + 1, 3) =         Application.Transpose(TabFinal) [COLOR=Green]'ici on transpose le tableau[/COLOR]
  Application.ScreenUpdating = True '[COLOR=Green]on réactive[/COLOR]
End Sub

Sub eclate()
c = 1
esp = 0
a2$ = Range("a2").Value
For t = 1 To Len(a2$)
If (Mid(a2$, t, 1) = Chr$(32) And esp < 2) Then
Cells(7, c).Value = a7$
a7$ = ""
esp = esp + 1
c = c + 1
Else
a7$ = a7$ & Mid(a2$, t, 1)
End If
Next
a7$ = a7$ & Mid(a2$, t, 1)
Cells(7, 3).Value = a7$
End Sub

Dim c As Range

For Each c In Range("a2:a" & Range("a65536").End(xlUp).Row)
    Range(c, c.Offset(0, 2)) = Split(c, " ")
Next c

Sub essai()
Dim c As Range
For Each c In Range("J1:J" & Range("J65536").End(xlUp).Row)
    Range(c, c.Offset(0, 2)) = Split(c, " ", 3)
Next c
End Sub