=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