| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 -- notyetnoob ------------------------------------------------------------------------ notyetnoob's Profile: http://www.excelforum.com/member.php...o&userid=36968 View this thread: http://www.excelforum.com/showthread...hreadid=566882 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Challenge: Copy across surname
=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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Challenge: Copy across surname
Wow thanks Dave thats excellent.... Works a treat.. :) -- notyetnoob ------------------------------------------------------------------------ notyetnoob's Profile: http://www.excelforum.com/member.php...o&userid=36968 View this thread: http://www.excelforum.com/showthread...hreadid=566882 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match, Copy, Merge, Delete and Repeat... MVP's this one's for you. | Excel Discussion (Misc queries) | |||
| Disable Cut & Copy then restore on exiting | Excel Discussion (Misc queries) | |||
| copy between worksheets does not copy formulae just values | Excel Discussion (Misc queries) | |||
| Conform a total to a list of results? | Excel Discussion (Misc queries) | |||
| Copy without Hidden Cols - How | Excel Discussion (Misc queries) | |||
Linear Mode
