Propercase, strings and arrays
 

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

Propercase, strings and arrays
 

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

Propercase, strings and arrays
 

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

Propercase, strings and arrays
 

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