Trim a cell that has names and address to only show name

[Christie]

I have quite a few cells that have alot of information in them eg surname,
christian name and address. I only need the surname and christian name in one
cell. How can I do this?
eg Smith Jones 123 Harper St Manly (A1). I need the cell to only show Smith
Jones (B1)

 

[JBeaucaire]

Lengthy, but it always returns the second word from the string searched:

=MID(MID(MID(SUBSTITUTE(A1," ","^",1),1,256),
FIND("^",SUBSTITUTE(A1," ","^",1)),256),2,
FIND(" ",MID(MID(SUBSTITUTE(A1," ","^",1),1,256),
FIND("^",SUBSTITUTE(A1," ","^",1)),256))-2)

Whichever word you want in the string, change the single digit in the
SUBSTITUTE to that number -1. So to get the 3rd word, change all the 1s to 2s.

 

[JBeaucaire]

Just to be clear about changing it if you want to for other purposes, here is
the version that would always return the FOURTH word:

=MID(MID(MID(SUBSTITUTE(A1," ","^",3),1,256),
FIND("^",SUBSTITUTE(A1," ","^",3)),256),2,
FIND(" ",MID(MID(SUBSTITUTE(A1," ","^",3),1,256),
FIND("^",SUBSTITUTE(A1," ","^",3)),256))-2)

 

[Christie]

This works really well, thank you, however I need to also grab the first word
of the cell. I have tried 0 and -1 but that doesn't seem to work.
Can you help with this one?

 

[Rick Rothstein]

Assuming the text after the full name *always* starts with a number (if it
doesn't, it will be nearly impossible to tell if the third "word" is part of
the name or is a street name without a number)...

=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-2)

If there might be more than one space between the name and house number,
then use this instead..

=TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))

 

[JBeaucaire]

First word is easy:

=LEFT(A1,FIND(" ",A1)-1)

 

[Rick Rothstein]

I have two questions for you...

1) What if the person has 3 names? I have two examples of this from friends
of mine at work. The first is a woman whose legal first name is Mary Anne?
That coupled with her last name would give her 3 names on your list. The
second is a man whose last name if Della Rossa. That coupled with his first
name would give you 3 names on your list. How would you handle these cases
with the formulas JBeaucaire has given you?

2) Have you seen the formula I posted elsewhere in this thread?

 

[Christie]

Thank you
Now that I have grabbed the name and the address into seperate cells, I
neeed to see if they match the other spreadsheet. The name matches but now I
net to see if the address also matches the name.

 

[Christie]

Thank you now I need to match this and see if the address match the other
spreadsheet including the names aswell.
How do I do this?