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),)