Challenge: Copy across surname

N

notyetnoob

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
 
D

Dave Peterson

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

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

Excel Comparing two columns 1
Counting unique entries across two or three columns 17
Split surname from end of name data 4
text string extraction help please 1
How to sort a list with Mr, Mrs, Miss ? 5
Splitting cells with significant variances from row to row 2
Function to copy lines 1
Nesting 2 If functions 0

Top