kristian84
New Member
- Joined
- Nov 30, 2015
- Messages
- 30
Hi,
I currently run a vba script that looks at a column of last names and runs the proper function. While doing so it also looks for names like McDonald and ensures the 'D' is upper case.
my current script works perfectly.
my new issue is a name such as wagland-mcdonald - my result im getting is "Wagland-Mcdonald" can someone please help me and advise what i'm missing to have the result "Wagland-McDonald"?
thanks
<code>
Public Function fxLastName(ByVal strLastName As String) As String
'Convert last names to proper case with special name consideration
' Mc eg: McGurgan
' O’ eg: O'Shea
' - eg: Sabatino-Morseu
' ' eg: Dell'Aquila
strLastName = Application.Trim(LCase(strLastName))
Select Case True
Case strLastName Like "mc*"
fxLastName = "Mc" & StrConv(Mid(strLastName, 3), vbProperCase)
Case strLastName Like "o'*"
fxLastName = "O'" & StrConv(Mid(strLastName, 3), vbProperCase)
Case strLastName Like "*-*"
fxLastName = Replace(StrConv(Replace(strLastName, "-", " "), vbProperCase), " ", "-")
Case strLastName Like "* (*"
fxLastName = Replace(StrConv(Replace(strLastName, " (", " "), vbProperCase), " ", " (")
Case strLastName Like "*.*"
fxLastName = Replace(StrConv(Replace(strLastName, ".", " "), vbProperCase), " ", ".")
Case strLastName Like "*'*"
fxLastName = Replace(StrConv(Replace(strLastName, "'", " "), vbProperCase), " ", "'")
Case Else
fxLastName = StrConv(strLastName, vbProperCase)
End Select
End Function
</code>
I currently run a vba script that looks at a column of last names and runs the proper function. While doing so it also looks for names like McDonald and ensures the 'D' is upper case.
my current script works perfectly.
my new issue is a name such as wagland-mcdonald - my result im getting is "Wagland-Mcdonald" can someone please help me and advise what i'm missing to have the result "Wagland-McDonald"?
thanks
<code>
Public Function fxLastName(ByVal strLastName As String) As String
'Convert last names to proper case with special name consideration
' Mc eg: McGurgan
' O’ eg: O'Shea
' - eg: Sabatino-Morseu
' ' eg: Dell'Aquila
strLastName = Application.Trim(LCase(strLastName))
Select Case True
Case strLastName Like "mc*"
fxLastName = "Mc" & StrConv(Mid(strLastName, 3), vbProperCase)
Case strLastName Like "o'*"
fxLastName = "O'" & StrConv(Mid(strLastName, 3), vbProperCase)
Case strLastName Like "*-*"
fxLastName = Replace(StrConv(Replace(strLastName, "-", " "), vbProperCase), " ", "-")
Case strLastName Like "* (*"
fxLastName = Replace(StrConv(Replace(strLastName, " (", " "), vbProperCase), " ", " (")
Case strLastName Like "*.*"
fxLastName = Replace(StrConv(Replace(strLastName, ".", " "), vbProperCase), " ", ".")
Case strLastName Like "*'*"
fxLastName = Replace(StrConv(Replace(strLastName, "'", " "), vbProperCase), " ", "'")
Case Else
fxLastName = StrConv(strLastName, vbProperCase)
End Select
End Function
</code>