Clever way of formatting names nicely?

I have a column with lots of names, and I want to format them nicely using VBA. I've been trying to use formulas in a spare cell using LEFT, RIGHT, MID, FIND, ISERROR, etc, to get the desired result, then copy/paste the resulting formula into my VBA code, but it's getting very unwieldy, long and hard to follow.

Basically the names as it stands can be anything from:

SMITH, JOHN M.
JONES
TAYLOR, CHRISTOPHER (Chris)
BAKER, MIKE
SMYTH, EDWARD E. (Ted)
MARK.MILLER
PAUL PARKER

I want to format them so that they become First Initial. Surname, unless there's only one word, then I want it left as-is. And I need the whole thing converted to Proper case.

So in the example above, the finished result should be:

J Smith
Jones
C Taylor
M Baker
E Smyth
M Miller
P Parker

Thanks!

Also posted here:

http://www.mrexcel.com/forum/subscri...php?folderid=0

http://www.ozgrid.com/forum/showthread.php?t=197132

example code to identify where a surname stops if in 'surname, firstname' format and convert into correct case:

Please Login or Register  to view this content.

Deleted...........

Please Login or Register  to view this content.