Mcdonalds -> McDonalds

[walt]

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

TIA Walt

 

[Don Guillett]

a formula
=IF(LEFT(D13,2)="mc",LEFT(D13,2)&UPPER(MID(D13,3,1))&RIGHT(D13,LEN(D13)-3),"
")
if you want a macro to change, post back

 

[Walter Becke]

yes, a macro would be great! Thanks for your troubles

Walter

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Don Guillett]

this will REPLACE what you have
Sub capmc() 'adjust range to suit.
For Each c In Range("f11:f" & Cells(65536, "f").End(xlUp).Row)
If UCase(Left(c, 2)) = "MC" Then
c.Value = Left(c, 2) & UCase(Mid(c, 3, 1)) _
& Right(c, Len(c) - 3)
End If
Next
End Sub

 

[Robert Black]

Did you try Search & Replace?

 

[Bernard V Liengme]

Be careful with 'Machinery' becoming MacHinery! Drill dress with bagpipes?
Bernard

 

[Walter Becke]

sure, but i have to do it within a giant macro that scans for names. and
the (if a = b then)-clause is case-sensitive. that is the main problem!
All 'normal' names are no problem. i use the 'vbProperCase' thing to
make them all equal. But that changes MCDONALDS to Mcdonalds and that is
not(!) equal to McDonalds...

Still trying....




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Walter Becke]

Thanks a lot, it is working now!!!

Walter

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Howard Kaikow]

If you are sure that the characters are always at the start of a word, you
might try:

Cells.Replace What:="Mcw", Replacement:="McW", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

 

[Don Guillett]

Which works for W

 

[Jerry Park]

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:

 

[Howard Kaikow]

Yes, I gave it as the basis for a starting point.

 

[Dave Peterson]

That's a neat idea to give more control to the typist (usually me!).

But if this were in a worksheet_change event (as opposed to a macro run on
demand), I add the LTrim() to clean up my "flagged" entry, too.

 

[Jerry Park]

I did LTrim the flagged entry. Just didn't post that part of the code...