Fixing Surnames with O/ Mc etc.

[loughgill]

Hi everyone,

I have a very long list of names extracted from email addresses (still in progress, may need another thread for some of this).

However, a lot of the names have "O" and "Mc" in them.
At the moment this comes out as "Obrien" when I need it to be "O'Brien" and "Mckenna" when I need it to be "McKenna".

Is there any shortcut I can devise for doing this? I know I would need separate ones for "O"s and "Mc", and would still have to go threw all the names manually to see where it needed to be applied, but it would still save a lot of time.
Basically I'd like to be able to press keyboard shortcuts or run a Macro every time I come across a cell that needs this treatment, rather than having to select right part of the text every time, delete the lower case letter, replace with upper case and add the apostrophe.

I really hope someone can help!

Thanks

[xladept]

Hi Loughgill,

Welcome to the Forum!

How would you handle Machinery??

[pjwhitfield]

I think you're correct in thinking you'd still have to check manually, add the following Sub to a module:

Please Login or Register  to view this content.

then go into Developer, go to Macros>highlight the McFix macro and select Options

Add a shortcut keystroke (I did Ctrl-Alt-M).

et voila, click a cell that contains the name, pres the shortcut keys and the names fixed.

[FDibbins]

Hi Loughgill,

Welcome to the Forum!

How would you handle Machinery??

or something like Obama?