Trim cell that has carriage return and split

[cfer]

Hi,
Had a macro that worked, they have now changed the data.

I have a column that can contain a name with the following after the name , space, then (Team Member)

ie George Smith (Team Member)

The next cell may have up to 6 names, each with (Team Member); after each name

Gary Wing (Team Member);
Michael Con (Team Member);
Michael Robinson (Team Member);
Phillip Mee (Team Member);
David Dynon (Team Member)

The trouble now is each name has a carriage return for each line. They are all in the same cell

The end result is to be able to split the 2 names into separate cells, for the complete column, which is dynamic. to the cell next too it

The carriage return is stopping me.

VBA would be great.

Thanks in advance

Cfer

 

[steve the fish]

Im not clear what you want. Maybe you could give us the expected outcome given A1 contains the first example and A2 the 2nd?

 

[Rick Rothstein]

I have a column that can contain a name with the following after the name , space, then (Team Member)

ie George Smith (Team Member)

The next cell may have up to 6 names, each with (Team Member); after each name

Gary Wing (Team Member);
Michael Con (Team Member);
Michael Robinson (Team Member);
Phillip Mee (Team Member);
David Dynon (Team Member)

The trouble now is each name has a carriage return for each line. They are all in the same cell

The end result is to be able to split the 2 names into separate cells, for the complete column, which is dynamic. to the cell next too it

Does this macro do what you want...

[table="width: 500"]
[tr]
	[td]Sub SplitNames()
  Dim StartRow As Long, LastRow As Long, Arr As Variant
  StartRow = 2
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Arr = Split(Replace(Replace(Join(Application.Transpose(Range(Cells(StartRow, "A"), Cells(LastRow, "A"))), ";"), vbLf, ""), " (Team Member)", ""), ";")
  With Cells(StartRow, "B").Resize(UBound(Arr) + 1)
    .Cells = Application.Transpose(Arr)
    .TextToColumns , xlDelimited, , , False, False, False, True, False
  End With
End Sub[/td]
[/tr]
[/table]

 

[cfer]

Hi Rick,

Thank you for the code, it almost worked.

I may have misled you on the final result.

I would like the first and last name in the same cell.

Currently it splits each name first and last into separate cells

If there is more than one persons name, is to copy across not down, with the first and last name in a single cell for each person.

Example below:

Gary Wing Michael Con Michael Robinson Phillip Mee David Dynon.

Apart from that, it worked fine.

Thanks, appreciate the help.

Cfer