séparer nom prénom complexe

Option Explicit

Function Eclate(Chaine As String, Info As String, Num As Byte) As String
    Dim Cpt As Byte
    Dim Tmp As String
    
    Application.Volatile
    
    If Len(Chaine) <> 0 Then
        Tmp = Chaine
        For Cpt = 1 To Num
            If InStr(1, Tmp, " à ") > 0 Then
                Tmp = Mid(Tmp, InStr(1, Tmp, " à ") + 3)
            Else
                Tmp = ""
            End If
        Next Cpt
    End If
    
    If Tmp <> "" Then
        Select Case UCase(Info)
            Case "PRENOM"
                Eclate = Mid(Tmp, 1, InStr(1, Tmp, " ") - 1)
            Case "NOM"
                Eclate = Mid(Tmp, InStr(1, Tmp, " ") + 1, InStr(1, Tmp, " en ") - InStr(1, Tmp, " ") - 1)
        End Select
    End If
End Function

=eclate(A1;"prenom";1)