you include sheets name before with an exclamation mark.
suppose Mr john smith is in sheet 1 cell C2
then in any other sheet and in any empty copy this formula
=MID(Sheet1!C2,SEARCH(" ",Sheet1!C2)+1,LEN(Sheet1!C2)-SEARCH(" ",Sheet1!C2)+1)
experiment with this formula