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

 

[mikerickson]

Perhaps something like this put in the sheet's code module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column = 1 Then
        Application.EnableEvents = False
        With Target
            .TextToColumns Destination:=.Cells(1, 1), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=False, Comma:=True, Space:=False, Other:=False
        End With
    End If
    Application.EnableEvents = True
End Sub

 

[Peter_SSs]

... I have used the toolbar data, then text to columns and recorded it using the macro recorder ...

Using Text to Columns in your macro was a good idea. Using Text to Columns is more efficient than looping through each row.