Formule pour séparer deux noms

=SUPPRESPACE(STXT(D2;MIN(SI(EXACT(MAJUSCULE(STXT(D2;LIGNE($A$15:$A$260);6));STXT(D2;LIGNE($A$15:$A$260);6))*LIGNE($A$15:$A$260)>0;EXACT(MAJUSCULE(STXT(D2;LIGNE($A$15:$A$260);6));STXT(D2;LIGNE($A$15:$A$260);6))*LIGNE($A$15:$A$260);99));99))

Function SepNom(c As String) As String
Dim oRegExp As Object, occ As Object
Set oRegExp = CreateObject("vbscript.regexp")
With oRegExp
.Global = True
    .Pattern = "([A-Z]{2,}.+°.+[^A-Z]{2,})([A-Z]{2,}.+)"
    If .test(c) Then
        Set occ = .Execute(c)
        SepNom = .Replace(c, occ.Item(0).submatches(1)): Exit Function
    End If
    
    .Pattern = "([^A-Z]+)([A-Z]{2,}.+)"
    If .test(c) Then
        Set occ = .Execute(c)
        SepNom = .Replace(c, occ.Item(0).submatches(1)): Exit Function
    End If
End With
End Function