Search and copy certain text

[Guest]

I am trying to find any instances of certain text in a column and if it
contains this text, I then want to copy a portion of this cell to another
cell.

eg
Column A contains first names and surnames
I would like to find all names with firstname John and then copy their
surname to another worksheet.
I can get the portion where it copies the surname but I am unable to search
for the first name and only report the instances that contain John.
A1 is where I type in the name that I want to find (The +3 is for the extra
spaces and other characters that are in the cell).
=IF(TRUE,MID(Sheet1!A3,(LEN($A$1)+3),20),)

 

[Guest]

Hi

Your problem is not clear, but here i am giving you one solution according
to what I understood.

Eg: =MID(VLOOKUP("Jone*",$A$1:$A$10,1),FIND("
",VLOOKUP("Jone*",$A$1:$A$10,1),1),LEN(VLOOKUP("Jone*",$A$1:$A$10,1)))

 

[Guest]

Thanks Muhammed. This is close but not quite there.

I will try and explain it better for you.

Sheet1
Column A
John Jackson
Paul Fredson
Jack May
Sam Gallagher
John Westbye

Sheet2
I type in "John" in Cell A1 because this is the name that I want the
surnames for.
The formula which is in Cell A2 down to A10 returns the following results
from the information on sheet1
Cell A2 = Jackson (This is from Sheet1 A1)
Cell A3 = Westbye (This is from Sheet1 A5)

I hope that explains in better.