How to do I change last name to last intial

[Ivn68]

Inside a cell I have First name, Last name
I want to use a vba to convert a it to First name, and Last initial for a whole list of names

 

[Joe4]

Will there always be a comma & one space between the first name and last name?

 

[Joe4]

If my assumption above is true, simply select the entries you want to update and run this code:

Sub FixNames()

    Dim cell As Range
    Dim x As Long
    
    Application.ScreenUpdating = False
    
'   Loop through each cell in selection
    For Each cell In Selection
'       Locate comma and space
        x = InStr(cell, ", ")
'       If found, remove end of last name
        If x > 0 Then cell.Value = Left(cell, x + 2)
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

 

[Ivn68]

Do I copy the whole thing or the green part is explaining what's happening

If my assumption above is true, simply select the entries you want to update and run this code:

Sub FixNames()

    Dim cell As Range
    Dim x As Long
   
    Application.ScreenUpdating = False
   
'   Loop through each cell in selection
    For Each cell In Selection
'       Locate comma and space
        x = InStr(cell, ", ")
'       If found, remove end of last name
        If x > 0 Then cell.Value = Left(cell, x + 2)
    Next cell
   
    Application.ScreenUpdating = True
   
End Sub

 

[Ivn68]

How do I make it select to then bottom of the column?

 

[Joe4]

If you hit CTRL+Down Arrow, it will select all the cells down to the bottom of the column.

If you want the VBA code to automatically select the data range, please let me know the first cell in the range (what column/row).

 

[Ivn68]

A2 and down to the end

 

[Joe4]

Try this:

Sub FixNames()

    Dim lr As Long
    Dim cell As Range
    Dim x As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through each cell in selection
    For Each cell In Range("A2:A" & lr)
'       Locate comma and space
        x = InStr(cell, ", ")
'       If found, remove end of last name
        If x > 0 Then cell.Value = Left(cell, x + 2)
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

 

[Ivn68]

What if the cell has "last name, first name" and I need it "first name, last initial"

 

[Joe4]

Can you say for certain that no record will ever have more than one comma in it?
Like, you won't ever have a record which looks like this:
Jones, Jr, Roy