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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Paul Black" wrote in message
...
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

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

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

--
Rick (MVP - Excel)


"Paul Black" wrote in message
...
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

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

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Paul Black" wrote in message
...
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

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



"Rick Rothstein" wrote in message
...
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

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Paul Black" wrote in message
...
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

You can also use proper as a worksheet function

=PROPER(A1)

Regards,
Peter T

"Paul Black" wrote in message
...
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

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.

--
Rick (MVP - Excel)

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" wrote in message
...
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.

--
Rick (MVP - Excel)

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)


"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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.

--
Rick (MVP - Excel)

On Sep 8, 8:47*am, "Rick Rothstein"
wrote:
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)

"Peter T" <peter_t@discussions wrote in message

...



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" wrote in message
...
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.

--
Rick (MVP - Excel)- Hide quoted text -

- Show quoted text -

Thanks everyone, the answers worked a treat.

Kind Regards,
Paul