Concaténation avec contrainte Espace / Nb de caractères

Sub Test()
   Dim ar, ar2(0 To 100), i As Integer, n As Integer
   ar = Split(ActiveCell, " ")
   n = 0
   ar2(0) = ar(0)
   For i = 1 To UBound(ar)
      If Len(ar2(n) & " " & ar(i)) > 35 Then
         n = n + 1
         ar2(n) = ar(i)
      Else
         ar2(n) = ar2(n) & " " & ar(i)
      End If        
   Next i
   ActiveCell.Offset(0, 1).Resize(, n + 1) = ar2
End Sub

Function SEPAR(t$, n%)
Dim i%, j%, p%
While i < Len(t)
  i = i + 1
  j = j + 1
  If Mid(t, i, 1) = " " Then p = i
  If j = n Then
    t = Application.Replace(t, p, 0, Chr(1))
    j = 0
  End If
Wend
SEPAR = Split(t, Chr(1))
End Function

=SIERREUR(INDEX(SEPAR($A8;35);COLONNES($B8:B8));"")

=SI(OU($A8="";COLONNES($B8:B8)>NBVAL(SEPAR($A8;35)));"";INDEX(SEPAR($A8;35);COLONNES($B8:B8)))

Function SEPAR(t$, n%)
Dim i%, j%, p%
While i < Len(t)
  i = i + 1
  j = j + 1
  If Mid(t, i, 1) = " " Then p = i
  If j = n + 1 Then
    t = Application.Replace(t, p, 0, Chr(1))
    j = i - p 'comptage à partir de l'espace
  End If
Wend
SEPAR = Split(t, Chr(1))
End Function

Function SEPAR(t$, n%)
Dim s, i%, X$(), j%
s = Split(t)
For i = 0 To UBound(s)
  ReDim Preserve X(j)
  t = Trim(X(j) & " " & s(i))
  If Len(t) > n Then i = i - 1: j = j + 1 Else X(j) = t
Next
SEPAR = X
End Function