View Full Version : [SOLVED:] Removing middle initials with space in last name
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
04-21-2016, 05:34 PM
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
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?
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.
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
04-21-2016, 06:37 PM
"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
15979
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
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)?
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
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.