Uppercase to Lower Case

  • Thread starter Thread starter Paul Black
  • Start date Start date
P

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
 
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
 
Select all the cell you want to change and then run this macro...

Sub MakeProperCase()
Selection.Value = Application.Proper(Selection.Value)
End Sub
 
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
 
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
 
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
Click to expand...

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.
 
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
 
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.
 
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 -
Click to expand...

Thanks everyone, the answers worked a treat.

Kind Regards,
Paul
 
Back
Top