Removing middle initials with space in last name

[oam]

I have several names in which I need to remove the middle initial from the end on the name but a few of the names have a space in the last name (example: Van Johnson and Van Houten) and all the formulas I can find work good as long as there is no space in the last name.

I need a formula that will remove the middle initials from the end of the name even if there is a space in the last name.

Hope this makes sense and thank you for your help.

[Paul_Hossler]

I'd use a user defined function, unless you really need a WS formula

Option Explicit
Function NoMI(s As String) As String
    Dim v As Variant
    Dim s1 As String
    Dim i As Long
    
    v = Split(s, " ")
    
    'John Smith
    If UBound(v) - LBound(v) + 1 <= 2 Then
        NoMI = s
        Exit Function
    End If
    
       
    'assume first name
    s1 = v(LBound(v)) & " "

    'John H Smith
    If Len(v(LBound(v) + 1)) = 1 Then
        If v(LBound(v) + 1) Like "[a-zA-Z]" Then
            For i = LBound(v) + 2 To UBound(v)
                s1 = s1 & v(i) & " "
            Next i
            
            NoMI = Trim(s1)
            Exit Function
        End If
        
    'John H. Smith
    ElseIf Len(v(LBound(v) + 1)) = 2 Then
        If v(LBound(v) + 1) Like "[a-zA-Z][.]" Then
            For i = LBound(v) + 2 To UBound(v)
                s1 = s1 & v(i) & " "
            Next i
        End If
            
        NoMI = Trim(s1)
        Exit Function
    
    Else
        NoMI = s
    End If
End Function

[SamT]

I assume that the names can be 2, 3, or 4 words long,with the middle initial being a one character word.

Is the initial always followed by a period? Preceded by a comma+space?

[oam]

I think I can make the User Defined Function work as opposed to a formula but the function did not remove the middle initial from "Van Johnson, Jessica L.". The desired result would be "Van Johnson, Jessica"

Hope this helps and thank you for a quick response.

[oam]

So far, if the person has a middle initial, all the names are followed by period. If someone does not have a middle initial HR enters (NMN) in their name and I would need that removed also.

[Paul_Hossler]

"Van Johnson, Jessica L.". The desired result would be "Van Johnson, Jessica"

You didn't say the names were Last First Middle. I assumed First Middle Last, and added the NMN part

Capture.JPG


Try this, still a UDF

Option Explicit
Function NoMI(s As String) As String
    Dim v As Variant
    Dim s1 As String
    Dim i As Long, n As Long
     
    v = Split(s, " ")
    n = UBound(v)
    If v(n) Like "[a-zA-Z]" Or v(n) Like "[a-zA-Z][.]" Or UCase(v(n)) = "NMN" Then
        n = n - 1
    End If
    For i = LBound(v) To n
        s1 = s1 & v(i) & " "
    Next i
    
    NoMI = Trim(s1)
End Function

[GTO]

So far, if the person has a middle initial, all the names are followed by period. If someone does not have a middle initial HR enters (NMN) in their name and I would need that removed also.

Please answer Sam's question about the comma. Can we always count on LName and FName being separated by a comma (and probably a space too, but that can be overlooked)?

[SamT]

Another UDF. To use, place code in a Standard module; In a cell place formula "=CleanName(A1)" where existing name is in Cell A1. Can be treated like any other Excel function.

Public Function CleanName(ExistingNameCell As Range) As String 
'Requires Original Name String in Format: 
'LastName(s)+Comma+Space+FirstName+Space+Init(or NMN) 
'
'For Help, see: http://www.vbaexpress.com/forum/showthread.php?55820 

Dim FullName As Variant 
Dim LastPart As Variant  

FullName = Split(ExistingNameCell.Value, ", ") 
LastPart = Split(FullName(UBound(FullName)), " ") 
CleanName = FullName(LBound(FullName)) & ", " & LastPart(LBound(LastPart))  

End Function

[oam]

To All,
First of all, I apologize for not giving all the requested/required information for you to give me an answer, at times I think I am giving you all the required information or answering all you questions but I didn’t, again I apologize.

Secondly, thank you for all your responses, plus the User Defined Functions all work! So again, thank you for your help on this matter even though I only gave you part of the information.