Splitting full name into separate columns

[zander140]

Hello everyone,

I want to apply the below VBA code so the result of the deliminating would be in another column that is several columns away from the starting cell but in the same row.

For instance, if I added "Smith, John" in cell B3, I would like "Smith" to show up in cell I2 and "John" to show up in cell J2. Please see the attached picture. Thank you.

I tried to do this by adding a TextToColumn destination but I am not sure what to put. Please see below.

Can you please help me with this.

Thank you.

Kind regards,

Zander

This is what I am looking to do:

The code is for A column:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
On Error GoTo Skip
If Target.Column = 2 Then
    Application.EnableEvents = False
    For Each cel In Target.Cells
        If cel <> "," Then
            cel.TextToColumns comma:=True
        End If
    Next cel
End If
Skip:
Application.EnableEvents = True
End Sub

[#2 · Accepted solution]

@zander140 

Please try this tweaked code...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
Dim r   As Long

On Error GoTo Skip
If Target.Column = 2 Then
    Application.EnableEvents = False
    For Each cel In Target.Cells
        If InStr(cel.Value, ",") > 0 Then
            r = cel.Row
            Range("I" & r).Value = Split(cel.Value, ",")(0)
            Range("J" & r).Value = Split(cel.Value, ",")(1)
        End If
    Next cel
End If
Skip:
Application.EnableEvents = True
End Sub

[#3]

Thank you so much, Subodh!

[#4]

You're welcome @zander140!