Uppercase to Lower Case

[Paul Black]

Good afternoon everybody,

In column "A" i have for example 09.E.BROWN
In column "B" i have ELIZABETH
In column "C" i have BROWN

.... what I ideally want is ...

In column "A" 09.E.Brown
In column "B" Elizabeth
In column "C" Brown

.... please. Is there three formulas that I can use to change to the
required text.

Thanks in advance.
Paul

 

[Don Guillett]

One way

Sub fixcase()
For i = 3 To cells(rows.count,1).end(xlup).row
Cells(i, 1).Resize(, 3).Value = _
Application.Proper(Cells(i, 1).Resize(, 3).Value)
Next
End Sub

 

[Rick Rothstein]

Select all the cell you want to change and then run this macro...

Sub MakeProperCase()
Selection.Value = Application.Proper(Selection.Value)
End Sub

 

[Rick Rothstein]

Or, without the loop...

Sub FixCase()
With Range("A3").Resize(Cells(Rows.Count, 1).End(xlUp).Row - 2, 3)
.Value = Application.Proper(.Value)
End With
End Sub

 

[Peter T]

A bit surprised that works!

Dim arr, arrP
arr = Array("AAA", "BBB", "CCC")
arrP = Application.Proper(arr)

bounds of arr are 0 to 2 but arrP is 1 to 3

FWIW, Lower & Upper do not work similarly

Regards,
Peter T

 

[Peter T]

You can also use proper as a worksheet function

=PROPER(A1)

Regards,
Peter T

 

[Rick Rothstein]

Dim arr, arrP

arr = Array("AAA", "BBB", "CCC")
arrP = Application.Proper(arr)

bounds of arr are 0 to 2 but arrP is 1 to 3

The bounds for the array that the Array function creates take their cue from
the Option Base... without an Option Base statement, or with an Option Base
0 statement, the bounds are as you stated (0 to 2); however, if Option Base
1 is specified, then the bounds will be 1 to 3. This is different than the
array that the Split function produces... the Split function *always*
creates zero-based arrays no matter what the Option Base setting is.

 

[Peter T]

Even with Option Base 0 the array returned from the Proper function is 1
base.

Any ideas why Proper can process an array as an Application function, but
not as a Worksheet function.

Regards,
Peter T

 

[Rick Rothstein]

I'm only guessing here...

Let me address your second question first... the Proper function returns a
(string) value... if you fed it an array, where would the elements of the
array go (keeping in mind that a worksheet function can only affect the
contents of the cell the formula is in)?

As for your first question... whatever the underlying worksheet array
processing functionality is, it treats arrays as ranges and a range cannot
have a 0 row or a 0 column, so it would appear the array (range) produced
would necessarily have to be 1-based.

Let me repeat my opening statement... I am only guessing here.

 

[paul_black27]

I'm only guessing here...

Let me address your second question first... the Proper function returns a
(string) value... if you fed it an array, where would the elements of the
array go (keeping in mind that a worksheet function can only affect the
contents of the cell the formula is in)?

As for your first question... whatever the underlying worksheet array
processing functionality is, it treats arrays as ranges and a range cannot
have a 0 row or a 0 column, so it would appear the array (range) produced
would necessarily have to be 1-based.

Let me repeat my opening statement... I am only guessing here.

--
Rick (MVP - Excel)









- Show quoted text -

Thanks everyone, the answers worked a treat.

Kind Regards,
Paul