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

TIA Walt

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

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!

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!

Did you try Search & Replace?

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

TIA Walt

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

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!

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!

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

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

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

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

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

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