Find and replace with criteria

hello all,

how to find and replace to prefix and suffix string/text, see this below :

JACK MANHOLD ----------- JACK
SISCA JACK HOLAND ------- JACK
ANDREW JACK ------------- JACK

thanks for your help....

sixthsense........

all the result after REPLACE is JACK

thanks but not my goal...

i mean how to using FIND AND REPLACE maybe with wildcard or anything to get the result only text :JACK

it's possible, Sixthsense...

Sixthsense

how about macros...

perhaps...it's solution..

could you help me???

I just tried with *Jack* in the Find What and Jack in the Replace With and it worked for me....

Georgia....

YOU ARE BRILLIANT...

THANKS ALL!!!!!!

use the attached file and check whether it solved your purpose or not?


Regards
Mahaveer Somani

thanks Friend....

it's sweet answer....

and for you, Sixthsense, i am missing with writing structure...and so thanks for your attention....

once again...

for 3 text this below i tried not working :

SPBBS Januari 2006 ------------- SPPBS (desired result)
SPBBS November 2006 ------------ SPPBS
SPBBS 2006 -------------------- SPPBS

WHY???

hi all..

i think for 3 data above (post #13), there is something wrong..maybe it's format type or error, and i don't know why,
actually then i copy paste the 3 data look like good...but not running when i am using FIND AND REPLACE....

=IF(A2="";"";LOOKUP(2*15;SEARCH($C$2:$C$3;SUBSTITUTE(SUBSTITUTE(A2;",";"");" ";""));$C$2:$C$3))

or see the files

Azumi