Problem with Proper Case Text

[Quivolt]

I have the following code given from your forum which does default the text in the excel sheet to Proper Case in Column A. I have a problem when the
Text in Column A contains as below:
Patrick McDonald
David McGinnis

This changes it as Patrick Mcdonald.
David Mcginnis
But I want texts like these to be as Patrick McDonald.....David McGinnis.....Can the following code be modified to reflect this change.
I need Your professional opinion on this.

Please Login or Register  to view this content.

[cytop]

Not easily. You'll have the same problem with other names like O'..., D'..., Fitz.... and others.

If it's any consolation, not all names starting with Mac or Mc have the next letter capitalized

Perhaps I should clarify, the code can be easily changed, covering all the possibilities is the hard part... The Mc/Max prefix means'son of...' and the general rule is if the ending is a proper name (McDonald) then the first letter after the Mc/Mac is capitalised. However, some are named after an occupation or physical trait - Mackenzie (son of the fair one), Mcpherson (son of the parson) or the more common Mcnab (son of the Abbot) - these do not get a second capitalisation. Applies to English (language, not the country) names only. I'm sure other languages are as bizarre in their own way.

[Quivolt]

Thank you Lazarus for explaining with such clarity of the other options as well....and you have all the options given out here which I am having a problem with.....well ...And is there no way that this code can be modified??? keeping my fingers crossed.....

[cytop]

you have all the options given out here which I am having a problem with

No, I haven't. I only indicated a sample.

Names starting O'... (generally Irish) are easy, they are all capitalised. Mc/Mac is something different altogether and you are going to have to build a list of the exceptions (those without a second capital) yourself. Norman (go read your European history) names starting D'... are also subject to variations and it seems to be personal choice rather than anything that can be codified, as is Fitz... (Also Norman).

As to how you'll deal with some Dutch names like 't Hooft is another matter altogether - but the one thing is, as you build your 'library' of names, you will get less and less exceptions - I'd hate to see the final code, though.

And then, of course, don't forget hyphenated names without spaces... Won't even mention the trend recently for 'made up' forenames with apostrophes and hyphens, but at least I've never (yet) seen a hyphenated forename with an apostrophe but 'D'Arcy-McDonald' as a surname is really going to tax your limits.

How far you want to take it is down to you and the time available.

[Quivolt]

ohhhh !!!! thats a lot of info on the names....thanks for all your time....I think i will have to leave it aside as you said and do it manually.....well thanks all in the forum and God bless you all....I will close this thread as solved as of now....with a smile of course ...