Private Sub CommandButton1_Click()
Dim c As Range, i%, flag As Boolean, t$
Set c = [B2] 'à adapter
For i = 1 To Len(c)
If c.Characters(i, 1).Font.ColorIndex <> xlAutomatic Then
flag = True
t = t & Mid(c, i, 1)
ElseIf flag Then
flag = False
t = t & vbLf
End If
Next
TextBox1 = t
End Sub