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

brooke	connected	Feb
Angela	Connected	Mar
Sharon	L.	Connected

<tbody>
</tbody>

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]

Assuming these values are in column B and are always two characters long and end in a period, this will delete them and clean things up:

Sub MyDeleteMI()

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

 

[tonyyy]

And if you want to "bring the middle initial in the same cell as the first name" - add the line in red...

Sub MyDeleteMI()

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

Cheers,

tonyyy

 

[dshafique]

Thanks! that worked for the most part, My data is in cells F and G respective to A and B, I'm going to make this dynamic, by adding names to the headers,
when I run this code, it only adds the first instance of a middle initial, the rest of the middle initials get deleted without appending to the first name.

 

[Joe4]

it only adds the first instance of a middle initial, the rest of the middle initials get deleted without appending to the first name

Please post your variation of the code you are using, and some sample data that is not working properly, so we can try re-construct your scenario on our side.

 

[dshafique]

Sub MyDeleteMI()

    Dim lastRow As Long
    Dim myRow As Long
    
    Application.ScreenUpdating = False
    
    '   Find last entry in column B
    lastRow = Cells(Rows.Count, "G").End(xlUp).Row
    
    '   Loop through all entries in column B
    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

the data I have is a lot, is there a way to attache the spreadsheet here? I dont see a way to.

 

[Joe4]

You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

By the way, we do not want/need to see ALL your data. Just a few examples of records that are not working properly.

 

[dshafique]

if a screenshot works, let me try that

 

[dshafique]

I am actually having a lot of trouble posting a screenshot here, or even pasting the table with borders, So i just shared a snippit of the data in google drive.

https://drive.google.com/file/d/0B9BEohjaeK8eZzBabTlpb05aelE/view?usp=sharing

 

[Joe4]

That site (as well as pretty much all other file sharing sites) are blocked for me at my current location.
I won't be able to look at it until I am home tonight.