Switching order of words (ex: names) in a cell

[Nichalia]

Hi all, here's what I'm trying to do (and thanks for helping, I'm very stuck):

On one sheet, there is a list of names like this in one column:
Smith (STAFF) Amy L (SALES)
Picard (CAPTAIN) Jean Luc

On another sheet, the names are listed like this:
Amy L Smith(STAFF)
Jean Luc Picard

Ultimately, I need to find a way to let Excel search between the two pages and match up names, and then check the status of each person (Example, Column A has all names and Column B has a status) and report it.

So how can I get Excel to:

1) Strip out / Ignore the information in the ( )...
2) Switch the order of the names so that excel can MATCH the names up?

Major thanks in advance, this has me stuck and Google hasn't been able to help with it (yet!)

 

[RonB1111]

First, to get rid of the ()'s including the words within them, select the column, then Ctrl-H to bring up the replace function window, enter (*) in the FindWhat box and leave the ReplaceWith box empty, then click "Replace All". (Make sure you do this on a test copy first).

Then to switch the order of the names, if the name is in cell A1, enter this in a blank cell of the same row and copy down:
=MID(TRIM(A1),SEARCH(" ",TRIM(A1))+1,99)&" "&LEFT(A1,SEARCH(" ",A1)-1)

 

[Andrew Poulsom]

Alternatively:

=TRIM(MID(SUBSTITUTE(A1,"(",REPT(" ",99)&"("),FIND(")",SUBSTITUTE(A1,"(",REPT(" ",99)&"("))+2,99))&" "&LEFT(A1,FIND(" ",A1)-1)