W
walt
Any ideas how to change all(!) Mc names from Mcwhatever to McWhatever ?
TIA Walt
TIA Walt
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
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
=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
W
Walter Becke
yes, a macro would be great! Thanks for your troubles
Walter
Walter
D
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
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
R
Robert Black
Did you try Search & Replace?
B
Bernard V Liengme
Be careful with 'Machinery' becoming MacHinery! Drill dress with bagpipes?
Bernard
Bernard
W
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....
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....
W
Walter Becke
Thanks a lot, it is working now!!!
Walter
Walter
H
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
might try:
Cells.Replace What:="Mcw", Replacement:="McW", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
D
Don Guillett
Which works for W
J
Jerry Park
I use an onchange event to format proper names, and the McWhateverwalt said:Any ideas how to change all(!) Mc names from Mcwhatever to McWhatever ?
TIA Walt
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:
H
Howard Kaikow
Yes, I gave it as the basis for a starting point.
D
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.
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.
J
Jerry Park
I did LTrim the flagged entry. Just didn't post that part of the code...