problem splitting names from prefixes

[wCJanssen]

Hi

I'm fairly new to vba, but managed to write a code that splits (Dutch) surnames from their prefixes. It works fine for fairly simple names, such as "van den Berg", but when you enter a double-barrelled name, such as "Baron Cohen" of "Rosario de Lima", it mistakes the first part of the name for a prefix. Here's what I've got so far:

Private Sub Test()
pos = InStrRev(txtSurnameIncl.Text, " ")
    txtSurnameExcl.Text = Mid$(txtSurnameIncl.Text, pos + 1, Len(txtSurnameIncl.Text) - pos)
    txtPrefixes.Text = Replace(txtSurnameIncl, " " & txtSurnameExcl.Text, "")
    If txtPrefixes = txtSurnameExcl Then txtPrefixes.Value = ""
txtPrefixes.Text = LCase(txtPrefixes.Text)
End Sub

Does anyone know how this can be solved? Maybe I can add a codeline that makes sure any word longer than three characters is regarded a name? Thanks in advance.

 

[wCJanssen]

perfect; thanks a lot to both of you!

 

[milesUK]

wCJanssen, glad your sorted and thanks to Bill for the solution.

If the names might contain non-alphabetical characters (e.g. "." "-" "/" ) then the test

If UCase(sChr) = sChr And sChr <> " " Then

would result in 'surnames' that had some of the forename(s) in them. Try this bit instead

If sChr >="A" And sChr<="Z" Then