Split LastName, First Name column into 2 seperate columns

[PhxDelta]

Hi, I inherited a worksheet with a combined Name column. Some entries are
listed as "FirstName LastName" and others are "LastName, First Name ". How
do I split all of the names out into two new columns "LastName" and "First
Name"? Thanks.

 

[Mike H]

Hi,

The is no way Excel can know which is a first and which is a last name but
this will split the names

First part
=TRIM(LEFT(A1,FIND(" ",A1)))

Second part

=RIGHT(A1,FIND(" ",A1)+1)

Mike

 

[PhxDelta]

Hi Mike,

I figured I'd take a 2-step approach to this. All the "LastName, First
Name" are separated by a comma and a space. So how would I convert that
active cell to rearrange it to First Name space Last Name? Then I figured I
could loop through the whole column a second time and use what you wrote
below.

 

[Mike H]

Hi,

You can't do that with code or a formula unless there is some regularly
repeating pattern in the frequency of first_name - last_name. for example
take the name


Michael Michael

Michael (my first name)

Michael ( a very common surname)

there is no way to programatically decide which is which is which. You can
do the split programatically by using the instr function and looping through
the names but will end up with the same problem as with the formula.

Mike

 

[Gary Keramidas]

here's what i'd do. i assumed all of the names are in column A, starting in row
1 on sheet1


Sub movenames()
Dim ws As Worksheet
Dim i As Long
Dim lastrow As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To lastrow
With ws.Range("A" & i)
If InStr(1, .Value, ",") Then
ws.Range("B" & i).Value = Trim(Split(.Value, ",")(1))
ws.Range("C" & i).Value = Trim(Split(.Value, ",")(0))
Else
ws.Range("B" & i).Value = Trim(Split(.Value, " ")(0))
ws.Range("C" & i).Value = Trim(Split(.Value, " ")(1))
End If
End With
Next
End Sub

 

[Bam]

PhxDelta,

Assuming the sytax is always the same, then you could try this.

Last Name
=IF(ISERROR(LEFT(B2,FIND(",",B2,1)-1)),RIGHT(B2,LEN(B2)-(FIND("
",B2,1))),LEFT(B2,FIND(",",B2,1)-1))

First Name
=IF(ISERROR(RIGHT(B1,LEN(B1)-(FIND(",",B1,1)+1))),LEFT(B1,FIND("
",B1,1)+1-1),RIGHT(B1,LEN(B1)-(FIND(",",B1,1)+1)))

Cheers,

Bam

 

[PhxDelta]

Thanks Gary and Bam,
Your answers worked perfectly although I went with Gary's so that I could
use a quick cut and paste macro versus doing it in the cells and dragging the
formulas over the whole range. Have a wonderful Thanksgiving, you've made
mine 100% better!

 

[PhxDelta]

Thanks for your replies Mike. Your answers/formulas gave me enough to build
on but then Gary and Bamn finished it off. What I didn't explain well enough
to you was that there was a pattern, if the last name appeared first, it was
separated by a comma then space. When the first name appeared first, there
are no commas, just a space. Thanks again and Happy Thanksgiving!

 

[tom kula]

hey all you do to seperate first name/last name with a comma is go to data -- text to columns---delimited-- click next- then select only the comma check box and finish the wizard

 

[tom kula]

http://office.microsoft.com/en-au/e...sing-convert-text-to-columns-HA001149851.aspx