walt wrote:
Any ideas how to change all(!) Mc names from Mcwhatever to McWhatever ?

TIA Walt


I use an onchange event to format proper names, and the McWhatever
construction is the main problem. But you also need to allow for other
constructions (like de Beers, DePriest, etc.). I've found a simple way
to allow the user of the form to bypass the formating function for non
standard names -- add a space to the end:

If Not (Mid(Target.Text, Len(Target.Text), 1) = " ") Then
' If ends with a space, don't format.
NewText = String(1, " ") + LCase(Target.Text)
NewText = Application.WorksheetFunction.Substitute(NewText, " mc", " mc~")
NewText = Application.WorksheetFunction.Proper(NewText)
NewText = Application.WorksheetFunction.Substitute(NewText, " Mc~", " Mc")
NewText = LTrim(NewText)
Target = NewText
End If