Using CATX to extract text after a specific index word

[sufiya]

Hi, I have a column from which I need to extract text after a specific index word (MP).using the following code: 

catx(" ", scan(TEST_NAME , 2, "MP")) as NAME,

this is what I end up with:

TEST_NAME	NAME
MP KONSTANTINE ABCDE
WNP BM OPS TEST MP CIGI FANNING	BM O
MP DAN BkENTHER
ERA MONTREAL TEST MP MARIA PUBRANTI	MARIA PUBRANTI
MP MARIA PUBRANTI
MP BRUCE WAYNE
MYABCD DIG CHAN TEST MP ABCDE DADOUN	ABCDE DADOUN
MP JENNIFER NOSNIKOF	OSNIKOF
MP STEVE PETERS	E PETERS

below is what I want - However, I can't seem to get the code right to extract the text after "MP", any suggestions, please? Kind regards 🙂

NAME

KONSTANTINE ABCDE

CIGI FANNING

DAN BkENTHER

MARIA PUBRANTI

MARIA PUBRANTI

BRUCE WAYNE

ABCDE DADOUN

MP JENNIFER NOSNIKOF

STEVE PETERS

[PaigeMiller · Accepted solution]

You can use FINDW and SUBSTR

newname=substr(test_name,findw(test_name,'MP')+3);

[sufiya]

Thank you this works!