How do I capitalize the letter following "Mc" or Mac" in a name?

[Raemon]

Mail merging from an Excel list with names in all caps, some caps and all
small. First, used "PROPER" in Excel to convert names to standard initial
capital letter, the rest small. How can I then capitalize the letter
following "Mc" or "Mac" in Scottish names?

 

[Peter Jamieson]

What is a "Scottish name" ?

Logically, what you are attempting cannot be done unless you are ceertain
that every surname beginning Mc or Mac that you are able to identify as
"Scottish" should have the capitalisation you suggest, i.e.

McName
MacName

If it's obvious what the names ought to be, why not get it right in your
Excel file, then there's no problem anywhere else?

Peter Jamieson

 

[Raemon]

With a list of 8,000 names in Excel, what is the best way to get them right
in Excel other than manuallly?

Thanks.

 

[Guest]

Write a macro, using regular expressions.

I am not an expert on regular expressions, but basically what you do is
build an expression to search for " Mc" and " Mac" (case sensitive) and
then capitalizing the next letter.

another option (if like me you dont know enough reg-exp) is just to
write a macro that searchs for "mac" in the sheet, and then loops over
the results and capitalizes...I will try to find time to write sample
later.

 

[Raemon]

Thanks. That makes sense.

Raemon

Write a macro, using regular expressions.

I am not an expert on regular expressions, but basically what you do is
build an expression to search for " Mc" and " Mac" (case sensitive) and
then capitalizing the next letter.

another option (if like me you dont know enough reg-exp) is just to
write a macro that searchs for "mac" in the sheet, and then loops over
the results and capitalizes...I will try to find time to write sample
later.

 

[Peter Jamieson]

You can only get them "right" if you ask each person how they normally spell
their name, because there is no rule that says that "MacDonald" should be
"MacDonald" or "Macdonald". A workaround (and even that isn't a sure-fire
bet) is to convert the names to upper case, which you can do in Word using a
\*Upper switch in the MERGEFIELD field.

Peter Jamieson

 

[Jonathan West]

Mail merging from an Excel list with names in all caps, some caps and all
small. First, used "PROPER" in Excel to convert names to standard initial
capital letter, the rest small. How can I then capitalize the letter
following "Mc" or "Mac" in Scottish names?

The problem here is that there is no hard & fast rule. MacKenzie might be
spelled with an uppercase K, but Macintosh generally doesn't have any
capitalisation. Machin is a name that starts with Mac but isn't a "mac" name
and so is never capitalised. Macdonald can sometimes be MacDonald. Both
spellings come in the first 3 hits if you type "Macdonald" into Google.

There's a trick you can try out on people. Spell out the following names in
turn, and get them to try & pronounce them

M-A-C - D-U-F-F
M-A-C - D-O-N-A-L-D
M-A-C - H-I-N-E-R-Y

Unfortunately, this doesn't help you a lot. Without contacting the people
concerned, you can only make your best guesses as to the capitalisation of
their names. However, if you have only 8000 names, only a relatively small
proportion of those should be Mac names, so you should be able to check
those ones by hand. Don't forget also to deal with O' names, such as
O'Leary.

--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org