Problem with WGM idea is that if the last name string naturally contains a space (like St Pierre), it will be replaced with a dash (St-Pierre).
Here's a VBA solution that uses a couple of UDFs. It will properly handle:
Smith-jones
smith- jones
smith -jones
smith - jones
smith--jones
smith --jones
smith-- jones
smith -- jones
If the argument is too convoluted, the code will return the original name along with a messagebox recommending manual change required.
Code:
Public Function fcnProper(sLName As String) As String
Dim sLeft As String, sRight As String, sWorker As String
Dim nDashPos As Long
sWorker = Trim(sLName)
sWorker = Replace(sWorker, "--", "-") 'replace double dash
If fcnCountOcc(sWorker, "-") > 1 Then
MsgBox "Unexpected format of name " & sLName & ". Manual correction required."
Exit Function
End If
If InStr(sWorker, "-") = 0 Then
fcnProper = StrConv(sWorker, vbProperCase)
Exit Function
End If
nDashPos = InStr(sWorker, "-")
sRight = Trim(Mid(sWorker, nDashPos + 1))
sLeft = Trim(Mid(sWorker, 1, Len(sRight)))
'Debug.Print sLeft, sRight
sRight = StrConv(sRight, vbProperCase)
sLeft = StrConv(sLeft, vbProperCase)
fcnProper = sLeft & "-" & sRight
End Function
Function fcnCountOcc(sText As String, sTarget As String) As Long
' Counts occurrences of a particular character
Dim nCount As Long, i As Long
sText = Trim(sText)
For i = 1 To Len(sText)
If Mid(sText, i, 1) = sTarget Then
nCount = nCount + 1
End If
Next i
fcnCountOcc = nCount
End Function