Change existing VBA code to adjust results

[fjcunninghamjr]

Good afternoon,
Currently I am using the following VBA:
Private Sub LAST_NAME_AfterUpdate()
Me.[LAST NAME] = StrConv(Me.[LAST NAME], vbProperCase)
End Sub

to change any typing of the last name to proper case, and it works great.
Today, more and more people are hyphenating their last name, i.e. Smith-Brown
How can I change my above code to make this work? Otherwise, the result is Smith-brown.
Thank you,


Frank

[Welshgasman]

Substitute the - for a space, ProperCase it and replace the - back?

[davegri]

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.

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

[CJ_London]

I would use a different substitution

?replace(strconv(replace("smith-brown","-"," - "),vbProperCase)," - ","-")
Smith-Brown
?replace(strconv(replace("st pierre","-"," - "),vbProperCase)," - ","-")
St Pierre