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 insta...

admin

BI & Data Analysis

Charting

excel

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

Subodh_Tiwari_sktneer

Silver Contributor

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

zander140

Copper Contributor

Jun 19, 2022

Thank you so much, Subodh!

Subodh_Tiwari_sktneer
Silver Contributor
Jun 19, 2022
You're welcome zander140!

Forum Discussion

Splitting full name into separate columns

Resources