Split surname from end of name data

S

Serena

Hi all

I've seen lots of posts about how to split up name data when there are a
known number of names within the cell or when the surname is the first item
in the cell.

Can someone please tell me how to split out the surname when I have data
such as "Dr Andrew Frederick Smith" and "Dr Jane Jones" i.e. the number of
names, initials, whatever is variable - therefore I need to find the space by
starting at the right hand end instead of the left ?

Any assistance would be much appreciated.
Hinemoa
 
N

Niek Otten

=RIGHT(A1,LEN(A1)-FIND("%",SUBSTITUTE(A1," ","%",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi all
|
| I've seen lots of posts about how to split up name data when there are a
| known number of names within the cell or when the surname is the first item
| in the cell.
|
| Can someone please tell me how to split out the surname when I have data
| such as "Dr Andrew Frederick Smith" and "Dr Jane Jones" i.e. the number of
| names, initials, whatever is variable - therefore I need to find the space by
| starting at the right hand end instead of the left ?
|
| Any assistance would be much appreciated.
| Hinemoa
 
P

Peo Sjoblom

One way

=MID(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,1024)


will return the last word/string separated by a space, make sure there are
no trailing spaces or else you will get a blank cell

=MID(TRIM(A1),FIND("^^",SUBSTITUTE(TRIM(A1),"
","^^",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))+1,1024)

will take care of trailing spaces



--


Regards,


Peo Sjoblom
 
S

Serena

Many thanks for your help
Serena

Peo Sjoblom said:
One way

=MID(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,1024)


will return the last word/string separated by a space, make sure there are
no trailing spaces or else you will get a blank cell

=MID(TRIM(A1),FIND("^^",SUBSTITUTE(TRIM(A1),"
","^^",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))+1,1024)

will take care of trailing spaces



--


Regards,


Peo Sjoblom
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA - exporting serial letters to single documents 0
Trim a cell that has names and address to only show name 8
Sorting by surname when first name and surname are in the same cel 2
Index Match to add employee number 2
Extract name, address and ph# 8
Extract given and surname a string 3
VLOOKUP please help 2
Data Conversion - times, dates & rows 5

Top