Using = Proper() with dutch names??

[Dutchie]

Hi,

How can I format dutch last names that can sometimes have van; van der
van de; van den or de in front of the last name? These need to be i
lower case with the end name with a capital:

eg: van den Berg

I hope someone can help

 

[David McRitchie]

see my page
http://www.mvps.org/dmcritchie/excel/proper.htm

 

[Dutchie]

Thanks David,

The code is great!

One question - I also have the following scenarios where people don'
type "van der" or "van den" but just enter the following:

vd
v/d
v.d.

When I run the macro on these ones it re-formats it to:

Vd
V/d
V.d.

I need them all reformatted to v/d.

Here's an eg of the code I entered:

If Left(cell.Value, 3) = "vd " Then cell.Value = _
"v/d " & Mid(cell.Value, 4, 99)

What have I done wrong?
Thanks for your help

 

[David McRitchie]

Hi Dutchie,
Never seen or heard of that convention before.
Placement is essential this is done after changing to proper.

If Left(cell.Value, 8) = "Van Der " Then cell.Value = _
"van der " & Mid(cell.Value, 9, 99)

Visual Basic is case sensitive and since I did not convert
the argument to UCASE or LCASE for the comjparison, then the
order of begin checked becomes important as well.

If Left(cell.Value, 8) = "Van Den " Then cell.Value = _
"van den " & Mid(cell.Value, 9, 99)
If Left(cell.Value, 8) = "Van Der " Then cell.Value = _
"van der " & Mid(cell.Value, 9, 99)
'-- single parts after those with two part prefixes
If Left(cell.Value, 3) = "Vd " Then cell.Value = _
"vd " & Mid(cell.Value, 4, 99)
If Left(cell.Value, 4) = "V/D " Then cell.Value = _
"v/d " & Mid(cell.Value, 5, 99)
If Left(cell.Value, 4) = "V.D " Then cell.Value = _
"v.d " & Mid(cell.Value, 5, 99)
If Left(cell.Value, 3) = "De " Then cell.Value = _
"de " & Mid(cell.Value, 4, 99)
If Left(cell.Value, 4) = "Van " Then cell.Value = _
"van " & Mid(cell.Value, 5, 99)
If Left(cell.Value, 4) = "Von " Then cell.Value = _
"von " & Mid(cell.Value, 5, 99)
Next

I have made the above changes to both
http://www.mvps.org/dmcritchie/excel/proper.htm
http://www.mvps.org/dmcritchie/excel/code/proper.txt

In American phone books (phone company) the entire last
name is captalized so it doesn't tell me much. Didn't find any "Van Den"
but did find "Van De" oops I meant "van den" and "van de" unless
"van de" isn't Dutch. Also a lot of names slip a space in out so I don't
know how you would handle vanderhoef, vanderhoff, vanderhoof

That's why I say know your data.
I converted my former company's phone book from a list of all capitals to
proper named and manually fixed twenty or additional exceptions that
were not programmed for (in REXX not in Excel) because they were ambiguous and
the result was I had to put the entire last name in capitals everything

I notice some errors in lengths in the macro on the proper.htm web page, and may
differ from code/proper.txt
-
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[Dutchie]

Thanks for the explanation David,

It's solved my problem...I have orders where customers fill in thei
own details, so you get all sorts of combinations and lower case an
upper case etc, spaces, no spaces...etc. Plus being in Holland there'
all kinds of international names as well as a whole lot of dutch names


Thanks for your help
Dutchie