Split Names Into Seperate Columns

[ghenderson122]

Hi Everyone - I have data of names that needs to be split into separate columns (first, last, middle). The problem is that the data is not consistent. Some is only first/last names, some is first/middle/last and some is only a middle initial. I have searched for an answer to this but haven't found one that solves the problem of the data not being consistant. Here's an example:

Merlin Colin
Shari Lynn Altenhof
MICHELLE A. HILL
Un Byung Yoon
Aaron David Moore
Shena C. Collins-Flores

How can I get this split into three columns. First, Middle, Last

I also have one other challenge... Once the data is split, I need to merge two first name columns into one. First & First. Is there a way to merge the data of two columns, while adding a space and "&" sign between the two names?

Thanks for your help!

[Kevin UK]

Hi ghenderson122

How do we know which name is which!
If you want to split it as it is, try Text to columns.

[kbkumar]

Hi - Welcome to the forum. Since your data is not consistent, you can achieve this by using delimter. To use this:

1. Select your data, then press ALT + D + E,
2. Select Delimited, press Next
3. Select "Space" option
4. Select "Next" and then "Finish"

This will split all your data into 3 columns - hopefully the way you wanted.

On your second part, to merge two columns use the below (I am assuming that the two cells containing the names are E2 and F2):

Please Login or Register  to view this content.

Hopefully this is what you want to achive.

[ghenderson122]

Thank you for the quick response... This works, but there is one problem. If one of the cells only contained a first and last name, the last name goes into the second column, which is where the middle names/initials are going. Is there a way to get those into the 3rd column?

Thanks!

Hi - Welcome to the forum. Since your data is not consistent, you can achieve this by using delimter. To use this:

1. Select your data, then press ALT + D + E,
2. Select Delimited, press Next
3. Select "Space" option
4. Select "Next" and then "Finish"

This will split all your data into 3 columns - hopefully the way you wanted.

On your second part, to merge two columns use the below (I am assuming that the two cells containing the names are E2 and F2):

Please Login or Register  to view this content.

Hopefully this is what you want to achive.

[Ghozi Alkatiri]

for example your data want to separate at A1 and want to separate at B1, C1 and so on

first formula at B1 than drug to the right

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",100)),(COLUMNS($A:A)-1)*100+1,100))

[kbkumar]

Hi - To get the last name in 3rd column where there are only first name and last names, use the below formulas:

Assumption: Raw data (after using delimiter contains in columns E, F and G), E2 contains first name, F2 contains Middle name and G2 last name.

Use this in cell I2:

Please Login or Register  to view this content.

J2:

Please Login or Register  to view this content.

K2:

Please Login or Register  to view this content.

and drag down, Now you will see "Colin" (from the first record) appearing in the last name column.

Hope this helps. Sorry, am unable to upload the workbook as of now, but can do that later today.

Thank you for the quick response... This works, but there is one problem. If one of the cells only contained a first and last name, the last name goes into the second column, which is where the middle names/initials are going. Is there a way to get those into the 3rd column?

Thanks!

[Marcol]

See if this workbook helps