Thread: Challenge: Copy across surname

HI,

I have a list of 60,000 names in A column in different formats.

Example:
Mr John Smith
Mr and Mrs B & A Jones
Z Houlous

Given the massive amount of names and the myriad of different formats of name using 'Test to Columns' and 'sorting' is not an option.

Is there a formulae or series of formuleas I could use to extract the surnames, which will ALWAYS be the last name to the right. I thought maybe an advanced lookup or something.

Anybody willing to accept the challenge?

Cheers

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

notyetnoob wrote:
>
> HI,
>
> I have a list of 60,000 names in A column in different formats.
>
> Example:
> Mr John Smith
> Mr and Mrs B & A Jones
> Z Houlous
>
> Given the massive amount of names and the myriad of different formats
> of name using 'Test to Columns' and 'sorting' is not an option.
>
> Is there a formulae or series of formuleas I could use to extract the
> surnames, which will ALWAYS be the last name to the right. I thought
> maybe an advanced lookup or something.
>
> Anybody willing to accept the challenge?
>
> Cheers
>
> --
> notyetnoob
> ------------------------------------------------------------------------
> notyetnoob's Profile: http://www.excelforum.com/member.php...o&userid=36968
> View this thread: http://www.excelforum.com/showthread...hreadid=566882

--

Dave Peterson

Wow thanks Dave thats excellent....

Works a treat..