Modifying the Proper function to ignore part of works

[bearcub]

I sometimes have to convert Upper case to proper case in my spreadsheets.

Is there a way to inform Proper to leave certain characters alone (on the fly).

Like words that are have prefixes in front of them (like MacDongual instead of Macdongual, or McConnell instead of Mcconnell). It seems to be happening with Mac or Mc joined with the last names but I image their are other words that are added but I can't think of those off the top of my head.

thank you for your help in advance,

Michael

 

[Johnny Thunder]

Here is a complex a$$ formula I wrote up for McConnel, maybe you can adapt to make it work for a bigger list?

=IF(OR(LEFT(E4,2)="Mc",LEFT(E4,3)="Mc"),LEFT(E4,2)&PROPER(MID(E4,FIND("Mc",E4,1)+2,LEN(E4))),"")

E4= the cell with the name

I sometimes have to convert Upper case to proper case in my spreadsheets.

Is there a way to inform Proper to leave certain characters alone (on the fly).

Like words that are have prefixes in front of them (like MacDongual instead of Macdongual, or McConnell instead of Mcconnell). It seems to be happening with Mac or Mc joined with the last names but I image their are other words that are added but I can't think of those off the top of my head.

thank you for your help in advance,

Michael