Sub test()
Dim i&, dep$, fin$
For i = 1 To Range("A65536").End(xlUp).Row
cellule = Cells(i, 1).Value
If InStr(1, cellule, "STE") <> 0 Then
If Mid(cellule, InStr(1, cellule, "STE") - 1, 1) = " " Then
dep = Left(cellule, InStr(1, cellule, "STE") - 1)
fin = Mid(cellule, InStr(1, cellule, "STE") + 3, Len(cellule))
Cells(i, 2).Value = dep & "SAINTE " & fin
End If
ElseIf InStr(1, cellule, "ST") <> 0 Then
If Mid(cellule, InStr(1, cellule, "ST") - 1, 1) = " " Then
dep = Left(cellule, InStr(1, cellule, "ST") - 1)
fin = Mid(cellule, InStr(1, cellule, "ST") + 3, Len(cellule))
Cells(i, 2).Value = dep & "SAINT " & fin
End If
End If
Next i
End Sub