Mcdonalds - McDonalds
 

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

TIA Walt

Mcdonalds - McDonalds
 

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

--
Don Guillett
SalesAid Software
Granite Shoals, TX

"walt" wrote in message
...
Any ideas how to change all(!) Mc names from Mcwhatever to McWhatever ?

TIA Walt

Mcdonalds - McDonalds
 

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!

Mcdonalds - McDonalds
 

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

--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Walter Becke" wrote in message
...
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!

Mcdonalds - McDonalds
 

Did you try Search & Replace?

"walt" wrote in message
...
Any ideas how to change all(!) Mc names from Mcwhatever to McWhatever ?

TIA Walt

Mcdonalds - McDonalds
 

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


"walt" wrote in message
...
Any ideas how to change all(!) Mc names from Mcwhatever to McWhatever ?

TIA Walt

Mcdonalds - McDonalds
 

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!

Mcdonalds - McDonalds
 

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!

Mcdonalds - McDonalds
 

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


--
http://www.standards.com/; Howard Kaikow's web site.
------------------------------------------------
"walt" wrote in message
...
Any ideas how to change all(!) Mc names from Mcwhatever to McWhatever ?

TIA Walt

Mcdonalds - McDonalds
 

Which works for W

--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Howard Kaikow" wrote in message
...
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


--
http://www.standards.com/; Howard Kaikow's web site.
------------------------------------------------
"walt" wrote in message
...
Any ideas how to change all(!) Mc names from Mcwhatever to McWhatever ?

TIA Walt

Mcdonalds - McDonalds
 

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

Mcdonalds - McDonalds
 

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

--
http://www.standards.com/; Howard Kaikow's web site.
------------------------------------------------
"Don Guillett" wrote in message
...
Which works for W

--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Howard Kaikow" wrote in message
...
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


--
http://www.standards.com/; Howard Kaikow's web site.
------------------------------------------------
"walt" wrote in message
...
Any ideas how to change all(!) Mc names from Mcwhatever to McWhatever
?

TIA Walt

Mcdonalds - McDonalds
 

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 wrote:

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

--

Dave Peterson

Mcdonalds - McDonalds
 

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

Dave Peterson wrote:
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 wrote:

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