Hi,

I need to write some script that allows me to evaluate a cell values first 2
characters for "MC". If found Uppercase the 3rd Character. This way I can use
it and adapt it for any naming anomoly.

eg "MCCARTHY" - "McCarthy"

My thoughts were to put each character of the value and evaluate the first
character then the second. When a match is found re-write the value out of
the array in the correct casing format.


Cheers
--
Paul Wilson

try this instead

if ucase(Range("A1")) = "MCCARTHY" then
Range("A1") = "McCarthy"
end if


for a more general case

if ucase(left(Range("A1"),3)) = "MCC" then
Range("A1") = "McC" & mid(Range("A1"),4)
end if


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=167693

Microsoft Office Help

Hi Paul

Select the range to be converted and run the below macro which should
convert as specified. (script that allows me to evaluate a cell values first
2 characters for "MC")

Sub Macro()
Dim cell As Range
For Each cell In Selection
If UCase(cell.Text) Like "MC*" Then
cell.Value = "Mc" & StrConv(Mid(cell, 3), vbProperCase)
End If
Next
End Sub

--
Jacob


"Paul Wilson" wrote:

Hi,

I need to write some script that allows me to evaluate a cell values first 2
characters for "MC". If found Uppercase the 3rd Character. This way I can use
it and adapt it for any naming anomoly.

eg "MCCARTHY" - "McCarthy"

My thoughts were to put each character of the value and evaluate the first
character then the second. When a match is found re-write the value out of
the array in the correct casing format.


Cheers
--
Paul Wilson

See David McRitchie's site.

http://www.mvps.org/dmcritchie/excel/proper.htm


Gord Dibben MS Excel MVP

On Wed, 6 Jan 2010 19:39:02 -0800, Paul Wilson
wrote:

Hi,

I need to write some script that allows me to evaluate a cell values first 2
characters for "MC". If found Uppercase the 3rd Character. This way I can use
it and adapt it for any naming anomoly.

eg "MCCARTHY" - "McCarthy"

My thoughts were to put each character of the value and evaluate the first
character then the second. When a match is found re-write the value out of
the array in the correct casing format.


Cheers