Please Modify this VBA for New Requirement Or Provide a New One

[Paul_Hossler]

Try this

Test data on sheet 2 of attachment

Function BreakOut2(s As String) As Variant
    Dim v As Variant
    Dim aOut As Variant
    Dim i As Long
    Dim s1 As String
    
    Application.Volatile
        
    s1 = s
    
    s1 = Replace(s1, " Designation", ": Designation")
    
    v = Split(s1, ":")
    
    BreakOut2 = v
End Function