Rogers method is great if the text is sepated by a comma. If not you could
use the SUbstitute function to insert a "," after the nth space. see data in
A1:A4

John Atherton 10 Crescent Close
Fred Smith 24 The Hi Road
James Graham, 16 the Low Road
Mr James Spade The Bronx

In B1 type =SUBSTITUTE(A7," ",", ",2) and copy down. This results in

John Atherton, 10 Crescent Close
Fred Smith, 24 The Hi Road
James Graham,, 16 the Low Road
Mr James, Spade The Bronx

You can see that JAmes Graham now has two comas and James Spade has a coma
after his first name. What I'm saying is that it is awkward to give a
solution that will cover every situtation. Still, after converting the
formulas to value you cuold then use Data, Text to Column

Providing that there are no titles (Mr, Mrs, Ms et al) then you could use a
formula to extract the Names with the Left function.

To extract the First NAme only use
=Left(a1,Find(" ",a1)-1)
First 2 Names
=LEFT($A1,FIND(" ",$A1,FIND(" ",$A1)+1)-1)
The Last Name
=MID(A1,FIND(" ",A1)+1,FIND(" ",$A1,FIND(" ",$A1,FIND(" ",$A1)+1))-FIND("
",A1))
The rest of the address
=TRIM(RIGHT(A1,LEN(A1)-FIND(" ",$A1,FIND(" ",$A1)+1)))

Remember to convert results to values and you have a few tools to work
through your list.

Regards
Peter

"Willing to learn" wrote:

Ok, I have this sheet that has 700 cells which includes name, last name of
the person and his/her address in it.

note: Name, last name and address are in the same cell.

Now I would like to transfer JUST the names and last name to another cell.

Is it possible to do that? if so, how?
I don't want to erase 700 address of each cell and then copy and paste the
name/last name. That will take a lot of time.

Could somebody guide me? please, I am willing to learn.

Regards.