Option Explicit
Sub extract_prenom()
Dim x As Integer, i As Integer, a As Integer, b As Integer
Dim prenom As String
Dim saisie As String
For x = 1 To 6
saisie = Range("A" & x)
saisie = Replace(saisie, " ", " ")
saisie = Replace(saisie, " ", " ")
saisie = Replace(saisie, " ", " ")
If UBound(Split(saisie, " ")) > 5 Then
For i = 1 To UBound(Split(saisie, " ")) - 5
Mid(saisie, InStr(saisie, " "), i) = "-"
Next i
End If
b = 0
For a = InStr(saisie, " ") + 1 To InStr(saisie, " ") + 50
If Mid(saisie, InStr(saisie, " ") + 1 + b, 1) <> Chr(32) Then
prenom = Mid(saisie, InStr(saisie, " "), a - InStr(saisie, " ") + 1)
b = b + 1
Else
Exit For
End If
Next
Range("C" & x) = prenom
Next x
End Sub