Splitting full name into separate columns

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

Formulas and Functions

Subodh_Tiwari_sktneer

Jun 19, 2022

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

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

Forum Discussion

Splitting full name into separate columns

3 Replies

Resources