Split LastName, First Name column into 2 seperate columns
 

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.

Split LastName, First Name column into 2 seperate columns
 

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" wrote:

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.

Split LastName, First Name column into 2 seperate columns
 

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" wrote:

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" wrote:

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.

Split LastName, First Name column into 2 seperate columns
 

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

"PhxDelta" wrote:

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" wrote:

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" wrote:

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.

Split LastName, First Name column into 2 seperate columns
 

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
--


Gary

"PhxDelta" wrote in message
...
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.

Split LastName, First Name column into 2 seperate columns
 

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
"Mike H" wrote:

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

"PhxDelta" wrote:

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" wrote:

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" wrote:

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.

Split LastName, First Name column into 2 seperate columns
 

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!

"Gary Keramidas" wrote:

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
--


Gary

"PhxDelta" wrote in message
...
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.

Split LastName, First Name column into 2 seperate columns
 

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!

"Mike H" wrote:

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

"PhxDelta" wrote:

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" wrote:

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" wrote:

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.

first name/last name comma seperate
 

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

On Wednesday, November 26, 2008 5:19 PM PhxDelt wrote:

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.


On Wednesday, November 26, 2008 5:25 PM Mike wrote:

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" wrote:


On Wednesday, November 26, 2008 5:35 PM PhxDelt wrote:

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" wrote:


On Wednesday, November 26, 2008 5:52 PM Mike wrote:

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

"PhxDelta" wrote:


On Wednesday, November 26, 2008 6:03 PM Gary Keramidas wrote:

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
--


Gary

"PhxDelta" wrote in message
...


On Wednesday, November 26, 2008 6:30 PM Ba wrote:

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
"Mike H" wrote:


On Thursday, November 27, 2008 7:33 AM PhxDelt wrote:

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!

"Gary Keramidas" wrote:


On Thursday, November 27, 2008 7:39 AM PhxDelt wrote:

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!

"Mike H" wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Nested IF Statement ? Excel 2007
http://www.eggheadcafe.com/tutorials...xcel-2007.aspx

how to do first name last name
 

http://office.microsoft.com/en-au/ex...001149851.aspx

On Wednesday, November 26, 2008 5:19 PM PhxDelt wrote:

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.


On Wednesday, November 26, 2008 5:25 PM Mike wrote:

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" wrote:


On Wednesday, November 26, 2008 5:35 PM PhxDelt wrote:

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" wrote:


On Wednesday, November 26, 2008 5:52 PM Mike wrote:

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

"PhxDelta" wrote:


On Wednesday, November 26, 2008 6:03 PM Gary Keramidas wrote:

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
--


Gary

"PhxDelta" wrote in message
...


On Wednesday, November 26, 2008 6:30 PM Ba wrote:

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
"Mike H" wrote:


On Thursday, November 27, 2008 7:33 AM PhxDelt wrote:

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!

"Gary Keramidas" wrote:


On Thursday, November 27, 2008 7:39 AM PhxDelt wrote:

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!

"Mike H" wrote:


On Tuesday, October 12, 2010 1:53 PM tom kula wrote:

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


Submitted via EggHeadCafe - Software Developer Portal of Choice
Review of DevExpress DXperience Control Suite
http://www.eggheadcafe.com/tutorials...rol-suite.aspx