PDA

View Full Version : [SOLVED] Removing middle initials with space in last name



oam
04-21-2016, 04:01 PM
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

SamT
04-21-2016, 05:37 PM
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
04-21-2016, 05:48 PM
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
04-21-2016, 05:51 PM
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

GTO
04-21-2016, 11:47 PM
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
04-22-2016, 07:16 AM
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
04-22-2016, 03:37 PM
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.