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?

"Rick Rothstein" wrote:

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&"012345 6789"))-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&"0 123456789"))-1))

--
Rick (MVP - Excel)


"Christie" wrote in message
...
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)