Excel: extraction de caracteres

'Procédure principale
Sub Main()
Dim ws As Worksheet
Dim derlig As Long
Dim i As Long

    Set ws = Worksheets(1)
    derlig = ws.Range("B65536").End(xlUp).Row
    
    For i = 1 To derlig
        If InStr(1, ws.Range("B" & i).Value, "(") = 0 Then
            ws.Range("A" & i).Value = returnPrefixe(ws.Range("B" & i).Value)
        Else
            ws.Range("B" & i).Value = returnModif(ws.Range("B" & i).Value)
        End If
    Next i
    
End Sub

'Fonction qui retourne le préfixe 
Private Function returnPrefixe(ByVal str As String) As String
Dim mTab

    mTab = Split(str, " ")
    
    returnPrefixe = mTab(0)
    
End Function

'Fonction qui retourne le texte modifié
Private Function returnModif(ByVal str As String) As String
Dim char As String

    If InStr(str, ")") = 0 Or Right(str, 1) = "(" Then
        returnModif = ""
        Exit Function
    End If
    
    char = Mid(str, InStrRev(str, "(") + 1)
    
    char = Mid(char, 1, Len(char) - 1)
    
    returnModif = char & " " & Mid(str, 1, Len(char) + 3)

End Function

Sub Main()
Dim ws As Worksheet
Dim derlig As Long
Dim i As Long
Dim NomTableau As Variant
Dim val As Variant
   
    NomTableau = Array("SPRLU", "SPRL", "SCRL", "ASBL", "BVBA", "SA", "SC")
    
    Set ws = Worksheets(1)
    derlig = ws.Range("B65536").End(xlUp).Row
    
    For i = 2 To derlig
        For Each val In NomTableau
            If ws.Range("B" & i).Value Like "*" & val & "*" Then
                ws.Range("A" & i).Value = val
                ws.Range("B" & i).Value = Replace(ws.Range("B" & i).Value, val, "")
            End If
        Next
        If InStr(1, ws.Range("B" & i).Value, "(") <> 0 Then
            ws.Range("B" & i).Value = returnModif(ws.Range("B" & i).Value)
        End If
    Next i
    
End Sub

Private Function returnModif(ByVal str As String) As String
Dim char As String

    If InStr(str, ")") = 0 Or Right(str, 1) = "(" Then
        returnModif = ""
        Exit Function
    End If
    
    char = Mid(str, InStrRev(str, "(") + 1)
    If InStr(char, " ") <> 0 Then
        char = Replace(char, " ", "")
    End If
    char = Mid(char, 1, Len(char) - 1)
    char = char & " " & Replace(str, char, "")
    char = Replace(char, "(", "")
    char = Replace(char, ")", "")
    
    returnModif = char

End Function