I need to separate a long list of names into First and Surname.

The trouble is I do not know how to use FIND to find the last space in the
text string. Can you help?

(I see I shall have to use Replace to get rid of White Space first.)



Rev and Mrs R Grosse

Helen & John Hadland

John and Valerie Hall

Peter and Pat Hall

Peter and Linda Halton



Francis Hookham (That's me!)

Get the first name:
=LEFT(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))-1)

Get the last name:
=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))


"Francis Hookham" wrote:

I need to separate a long list of names into First and Surname.

The trouble is I do not know how to use FIND to find the last space in the
text string. Can you help?

(I see I shall have to use Replace to get rid of White Space first.)



Rev and Mrs R Grosse

Helen & John Hadland

John and Valerie Hall

Peter and Pat Hall

Peter and Linda Halton



Francis Hookham (That's me!)

Hi Francis

If Trailing/Leading spaces might be an issue, you could use this
adaptation:

First name(s)

=LEFT(TRIM(A1),FIND("@",SUBSTITUTE(TRIM(A1),"
","@",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ","")))))

Surname

=TRIM(REPLACE(TRIM(A1),1,LEN(B1),""))

Hope this helps!

Richard





Francis Hookham wrote:
I need to separate a long list of names into First and Surname.

The trouble is I do not know how to use FIND to find the last space in the
text string. Can you help?

(I see I shall have to use Replace to get rid of White Space first.)



Rev and Mrs R Grosse

Helen & John Hadland

John and Valerie Hall

Peter and Pat Hall

Peter and Linda Halton



Francis Hookham (That's me!)