remove middle initial from name

[dshafique]

Hey guys, I have a worksheet with a lot of unstructured data and the middle initial after the first name screws with the alignment when I do text to columns for the data. the data comes from a txt file, and i have my delimiters as tab and space. its really annoying because some data I have

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]brooke[/TD]
[TD]connected[/TD]
[TD]Feb[/TD]
[/TR]
[TR]
[TD]Angela[/TD]
[TD]Connected[/TD]
[TD]Mar
[/TD]
[/TR]
[TR]
[TD]Sharon[/TD]
[TD]L.[/TD]
[TD]Connected[/TD]
[/TR]
</tbody>[/TABLE]

is there a way for me to bring the middle initial in the same cell as the first name or remove it if i have to?

 

[Joe4]

The code should look like this:

Sub MyDeleteMI()

    Dim lastRow As Long
    Dim myRow As Long
    
    Application.ScreenUpdating = False
    
    '   Find last entry in column G
    lastRow = Cells(Rows.Count, "G").End(xlUp).Row
    
    '   Loop through all entries in column G
    For myRow = 2 To lastRow
    '       If entry is two characters and ends in a period, delete it
        If Len(Cells(myRow, "G")) = 2 And Right(Cells(myRow, "G"), 1) = "." Then
            Cells(myRow, "F") = Cells(myRow, "F") & " " & Cells(myRow, "G")
            Cells(myRow, "G").Delete Shift:=xlToLeft
        End If
    Next myRow
            
    Application.ScreenUpdating = True
    
End Sub

 

[dshafique]

thanks!

 

[Joe4]

You are welcome.