Split surname from end of name data
 

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

Split surname from end of name data
 

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

Split surname from end of name data
 

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


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

Split surname from end of name data
 

Many thanks for your help.
Serena

"Niek Otten" wrote:

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

Split surname from end of name data
 

Many thanks for your help
Serena

"Peo Sjoblom" wrote:

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


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