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

  • Thread starter Thread starter Christie
  • Start date Start date
C

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)
 
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.
 
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)
 
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?
 
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))
 
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?
 
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.
 
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?
 
Back
Top