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

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

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)

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

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