How to extract Name and Surname from data string

[Alex007]

Hi,

Need urgent help please peeps.

I have a string of data "BAML - BoA:1999 Baaz Smith - Tradeplant - ETT"

I have a table of names on a different tab.

I need a formula to find above name in my table on a different tab and return back a name. I have below but for some reason its returning a wrong name.

=IFERROR(INDEX(Sheet3!A:A,MAX(IF(ISERROR(FIND(Sheet3!A:A,J2)),-1,1)*(ROW(Sheet3!A:A)-ROW(J2)+1))), "None")

PLEASE HELP!!!

While i am here......how hard would it be to have the reverse......find the name a delete it?

 

[Aladin Akyurek]

will do thank you so much!!

You are welcome.

Quick question how hard would it be to do the reverse find the name and delete it? and give me a string with out the name?

=LOOKUP(9.99999999999999E+307,SEARCH(" "&NameList&" "," "&$J30553&" "),NameList)

cannot delete the name it finds from NameList. On the other hand, the formula can be extended to remove the name from J30553 such that we get the string without the name in the formula cell.

 

[Alex007]

Thank you once again. You have saved my weekend. The formula worked in the end on most but not all due to not having all the names!!!!

I didnt really try the other, but thank you!!

 

[Aladin Akyurek]

Thank you once again. You have saved my weekend. The formula worked in the end on most but not all due to not having all the names!!!!

I didnt really try the other, but thank you!!

You are welcome. Thanks for the update.