SOLVED

Splitting full name into separate columns

Copper Contributor

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:

Automating the Delimiter function.JPG

 

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

 

 

 

3 Replies
best response confirmed by zander140 (Copper Contributor)
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
Thank you so much, Subodh!

You're welcome @zander140!

1 best response

Accepted Solutions
best response confirmed by zander140 (Copper Contributor)
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

View solution in original post