Split Cells Upon Comma

[Jaye7]

Can someone please help with a code that splits all cells in column A upon finding a comma.

example text
Cell A1 contains - hello, how, are, you.

It would split A1 to

Cell A1 to hello
Cell B1 to how
Cell C1 to are
Cell D1 to you.

Thanks

 

[bobsan42]

do all cells contain four words or it's different in each?
approx. how many cells in A to process?

 

[Peter_SSs]

Do you need code? Have you tried Data|Text to Columns...|Delimited with comma as the delimiter?

 

[Thelfer]

Another way:

hello, how, are, you 'cell A1

=MID(A1;1;FIND(",";A1)-1) 'hello
=MID(A1;FIND(",";A1)+2;(FIND(",";A1;FIND(",";A1)+2))-(FIND(",";A1)+2)) 'how
=MID(A1;(FIND(",";A1;FIND(",";A1)+2))+2;FIND(",";A1;((FIND(",";A1;FIND(",";A1)+2))+2))-((FIND(",";A1;FIND(",";A1)+2))+2)) 'are
=MID(A1;FIND(",";A1;((FIND(",";A1;FIND(",";A1)+2))+2))+2;FIND(",";A1;((FIND(",";A1;FIND(",";A1)+2))+2))-((FIND(",";A1;FIND(",";A1)+2))+2)) 'you


This is not a good solution. Easier with "Text to function" in Data menu, using Delimited and Other set to ",".

 

[Jaye7]

Thanks for your replies, I always forget to be more specific, I would like a VBA script so that it runs in the background and I don't have to remember the data to type in etc... the lazy man's approach or the work smarter not harder approach.
There could be up to 50 commas in column A.

 

[gsbelbin]

This should give you the basis to work from

Sub SplitCell()
    Dim MyText() As String
    Dim i As Integer
 
    MyText = Split(Cells(1, 1).Value, ",")
 
    For i = 1 To UBound(MyText)+1
        Cells(1, i).Value = MyText(i - 1)
    Next i
 
End Sub

 

[Jaye7]

Thanks Gsbelbin,

The script works but only for cell A1, it should work for each cell in column A if there is a value.
I tried this on my home computer which is excel 2007, it may work on my work computer which is 2000, I will try it in 2 days when back at work.

 

[gsbelbin]

Try this

Sub SplitCell()
    Dim MyText() As String
    Dim i As Integer
    Dim cell As Range
 
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
 
    For Each cell In Selection
        MyText = Split(cell.Value, ",")
 
        For i = 1 To UBound(MyText) + 1
            Cells(cell.Row, i).Value = MyText(i - 1)
        Next i
    Next cell
 
End Sub

 

[Jaye7]

Thanks for you help everyone, I have used the toolbar data, then text to columns and recorded it using the macro recorder so that I can put the code into one of my userforms, so that in 6 months time if I haven't used it I won't have to try to remember how I did it again.

Thanks again, if you could help with one of my other posts on duplicates I would greatly appreciate it.

Link
http://www.mrexcel.com/forum/showthread.php?t=489857

 

[Jaye7]

Gsbelbin,

That works great, thanks again.