PDA

View Full Version : How to separate words in a cell



Mykasd
10-15-2007, 01:02 PM
If I have a list of words in a cell and want to separate them in to other cells, how would I do that? These words are separated by commas within a certain cell and I want them to be in different cells.

For example:
Cell A1 contains: Mike, John, Pete, Sarah

And I would like a macro to split this list so that:
B1 contains: Mike
C1 contains: John
D1 contains: Pete
E1 contains: Sarah


Can anyone help?

JKwan
10-15-2007, 01:33 PM
Try using:
Data - Text to Columns
Pick Delimited by and choose Comma

Brandtrock
10-16-2007, 04:43 AM
Try using:
Data - Text to Columns
Pick Delimited by and choose Comma

That will parse your text, but it will begin in A1 and go to D1.

This Sub routine will parse A1 into B1 to E1 and clear A1.

Sub Separate()
With Range("A1")
.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:= _
True
.Clear
End With
End Sub


Regards,