How to separate first and last name where some names are couples

[stwarp]

I have a list of first and last names combined into one cell and some of the names are two couples names separated by the "&" symbol. I would like to separate into 2 columns where the first name or names are in one column and the last name is in another column. Thanks in advance for your help it is greatly appreciated.

 

[Zack Barresse]

Hi there, welcome to the board!

I would probably do this with Text-to-Columns, delimited by the ampersand, then use formulas from there. Assuming there are no other variants, you'll end up with some rows having two columns and others having one (those which didn't contain & symbol). Then some formulas could get your columns lined out, take care of leading/trailing spaces, maybe something like...

=TRIM(A1)

Hard to tell anymore without seeing at least a sample of the data.

 

[stwarp]

[TABLE="width: 231"]
<tbody>[TR]
[TD]Thank you. I don't know how to upload a sample but here is the first 30 lines the file. You can see one line has 3 first names so I realize I'll have to fix those instances manually. Name
[/TD]
[/TR]
[TR]
[TD]Adam Feeley
[/TD]
[/TR]
[TR]
[TD]Ann Lori Tucci
[/TD]
[/TR]
[TR]
[TD]Tara Case
[/TD]
[/TR]
[TR]
[TD]James & Revelyn Gunn
[/TD]
[/TR]
[TR]
[TD]Adam & Dana Swickle
[/TD]
[/TR]
[TR]
[TD]Adrian & Janet Morales
[/TD]
[/TR]
[TR]
[TD]Adriana Duque
[/TD]
[/TR]
[TR]
[TD]Aimee & George Alexander
[/TD]
[/TR]
[TR]
[TD]Aimee & Raul Lopez
[/TD]
[/TR]
[TR]
[TD]Alain & Chantal Bray
[/TD]
[/TR]
[TR]
[TD]Alain & Mirtha Flores
[/TD]
[/TR]
[TR]
[TD]Alan & Andrea Kanter
[/TD]
[/TR]
[TR]
[TD]Alan Drossin
[/TD]
[/TR]
[TR]
[TD]Alan Jorge Rice
[/TD]
[/TR]
[TR]
[TD]Alan Olstein
[/TD]
[/TR]
[TR]
[TD]Alcides Pedroza
[/TD]
[/TR]
[TR]
[TD]Alejandro & Bressy Rubio
[/TD]
[/TR]
[TR]
[TD]Alejandro & Martha Dominguez
[/TD]
[/TR]
[TR]
[TD]Alex & Deborah Gomez
[/TD]
[/TR]
[TR]
[TD]Alexander & Margaret Mozo
[/TD]
[/TR]
[TR]
[TD]Alexander & Sara Vanyi
[/TD]
[/TR]
[TR]
[TD]Alexis & Mayte Meizoso
[/TD]
[/TR]
[TR]
[TD]Alfonso Smith
[/TD]
[/TR]
[TR]
[TD]Alfredo & Nydia Gutierrez
[/TD]
[/TR]
[TR]
[TD]Alfredo Jurado
[/TD]
[/TR]
[TR]
[TD]Alwyn York & Cathleen Mcconnell
[/TD]
[/TR]
[TR]
[TD]Amit & Michelle Zemach
[/TD]
[/TR]
[TR]
[TD]Amy & Ron Linares
[/TD]
[/TR]
</tbody>[/TABLE]

 

[FDibbins]

With your data in column A, try this for the 1st name/& name...
=IF(B1=0,LEFT(A1,FIND(" ",A1,1)-1),LEFT(A1,SEARCH("xx",SUBSTITUTE(A1," ","XX",3),1)-1))
And this if teh last name...
=RIGHT(A1,LEN(A1)-LEN(C1)-1)

This, in a spare column, will give you an indicator that you have 2 1st names and no &...
=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=2,"Check","")

 

[Zack Barresse]

Maybe it would be better to get the last name first, then derive the first name from it. This assumes there is only one last name not separated by a space, so hyphenated names will work just fine...

Last name:
=RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

First name (derived from last name):
=LEFT(A1,LEN(A1)-LEN(L1)-1)

This also assumes your value starts in A1, adjust accordingly.