Remove the first word and and anything after a specific character
I got a field with different data in it. For an example, the string got the job classification as the first word followed by the Name and sometimes (Grad) or (Post Grad) at the end. refer the below data set.
'ANUM COLLIN, Berry'
'NUM MARGE, Jodie'
'NUM LEE, Mary (Post Grad)'
'RN TASKER, Peter (Grad)'
'RN McDONALD, Ben (Grad)'
'NUM MORFITT, Sunny'
'RN VAN VARUN, Deb (Grad)'
I want to retrieve only the full name of the employee removing the first word and anything after "(" character.
I used "Trim(SubField(Name,'(',1)) As NewName" to remove anything after the "(" character and could get the first word removed from the string.
Can anyone help me with this?
Also, I found, even after using the Trim function, still I get some trailing spaces at the end of the NewName field. Would appreciate your comments on this too.