this is a slightly different approach... but one thing to consider.
Write a macro to delete all of the suffixes (i.e. find and replace Jr,
Sr, etc.).
Next use the left and right functions to get the first and last names
of the user.
to get the first name:
=LEFT(A1,FIND(" ",A1))
to get the last name (assumes last name occurs after the last space in
the cell)
=MID(A1,FIND("^^",SUBSTITUTE(" "&A1," ","^^",LEN(" "&A1)-
LEN(SUBSTITUTE(" "&A1," ",""))))-1,1000)
you might also find this site helpful.
http://www.dailydoseofexcel.com/arch...ing-functions/
On Aug 14, 3:33 pm, Tim879 wrote:
the easiest way to do this is to go to Data-Text to Columns and
follow the wizard. this will at least get you all the names in
separate columns
On Aug 14, 3:09 pm, wrote:
I deal with an Excel file that has many names on it all in 1 cell.
Some of the common combinations are below. Is there any way to
separate the 1 column in to 2 columns bases on table below.
FIRST LAST CONVERT TO FIRST LAST
FIRST MIDDLE LAST CONVERT TO FIRST MIDDLE LAST
FIRST M LAST CONVERT TO FIRST M LAST
FIRST M. LAST CONVERT TO FIRST M. LAST
FIRST LAST SUFFIX CONVERT TO FIRST LAST SUFFIX
FIRST MIDDLE LAST SUFFIX
CONVERT TO FIRST MIDDLE LAST SUFFIX
FIRST LAST SUFFIX CONVERT TO FIRST LAST SUFFIX
The problem I have is that all the names are different lengths.
Thanks for your help.