How do I change a spreadsheet from all caps to "Proper"
 

I have a spreadsheet containing data to make mailing labels. The whole thing
was entered in caps. I need to change it to the Proper style ( the first
letter is capitalized and the rest lower case). How can I do this?

Hi
see:
http://www.cpearson.com/excel/case.htm

--
Regards
Frank Kabel
Frankfurt, Germany

"dumbfounded" schrieb im
Newsbeitrag ...
I have a spreadsheet containing data to make mailing labels. The
whole thing
was entered in caps. I need to change it to the Proper style ( the
first
letter is capitalized and the rest lower case). How can I do this?

from a post of mine on the 23rd.
if you have formulas, use this instead or you will wipe out the formulas

Sub makeproper()
With ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants)
..Value = Application.Proper(.Value)
End With
End Sub


--
Don Guillett
SalesAid Software

"dumbfounded" wrote in message
...
I have a spreadsheet containing data to make mailing labels. The whole
thing
was entered in caps. I need to change it to the Proper style ( the first
letter is capitalized and the rest lower case). How can I do this?

If you have an address in cell A1, you can enter =proper(a1) in cell B1.

"dumbfounded" wrote:

I have a spreadsheet containing data to make mailing labels. The whole thing
was entered in caps. I need to change it to the Proper style ( the first
letter is capitalized and the rest lower case). How can I do this?

Hi Don,

I think that your sub will overwrite data if the SpecialCells range
comprises more than one area.

The sub needs to loop through the SpecialCells range's constituent cells.

---
Regards,
Norman



"Don Guillett" wrote in message
...
from a post of mine on the 23rd.
if you have formulas, use this instead or you will wipe out the formulas

Sub makeproper()
With ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants)
.Value = Application.Proper(.Value)
End With
End Sub


--
Don Guillett
SalesAid Software

"dumbfounded" wrote in message
...
I have a spreadsheet containing data to make mailing labels. The whole
thing
was entered in caps. I need to change it to the Proper style ( the first
letter is capitalized and the rest lower case). How can I do this?

UsedRange is the area used for any purpose.
CurrentRegion is the area of contiguous cells with data.

OP did say "The WHOLE thing"

constituent??
--
Don Guillett
SalesAid Software

"Norman Jones" wrote in message
...
Hi Don,

I think that your sub will overwrite data if the SpecialCells range
comprises more than one area.

The sub needs to loop through the SpecialCells range's constituent cells.

---
Regards,
Norman



"Don Guillett" wrote in message
...
from a post of mine on the 23rd.
if you have formulas, use this instead or you will wipe out the formulas

Sub makeproper()
With ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants)
.Value = Application.Proper(.Value)
End With
End Sub


--
Don Guillett
SalesAid Software

"dumbfounded" wrote in message
...
I have a spreadsheet containing data to make mailing labels. The whole
thing
was entered in caps. I need to change it to the Proper style ( the
first
letter is capitalized and the rest lower case). How can I do this?

Hi Don,

On a blank worksheet,
In A1 enter: DON
In A2: enter FRED
In A3 enter TOM

Now run your sub.

What do you get?

I get DON in all three cells!

As I said you need to loop, either through each area or each cell. Adapting
your sub:

Sub makeproper2()
Dim ar As Range

On Error Resume Next
For Each ar In Activesheet.UsedRange.SpecialCells(xlCellTypeConst ants)
With ar
.value = Application.Proper(.value)
End With
Next ar
On Error GoTo 0
End Sub

---
Regards,
Norman



"Don Guillett" wrote in message
...
UsedRange is the area used for any purpose.
CurrentRegion is the area of contiguous cells with data.

OP did say "The WHOLE thing"

constituent??
--
Don Guillett
SalesAid Software

"Norman Jones" wrote in message
...
Hi Don,

I think that your sub will overwrite data if the SpecialCells range
comprises more than one area.

The sub needs to loop through the SpecialCells range's constituent cells.

---
Regards,
Norman



"Don Guillett" wrote in message
...
from a post of mine on the 23rd.
if you have formulas, use this instead or you will wipe out the
formulas

Sub makeproper()
With ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants)
.Value = Application.Proper(.Value)
End With
End Sub


--
Don Guillett
SalesAid Software

"dumbfounded" wrote in message
...
I have a spreadsheet containing data to make mailing labels. The whole
thing
was entered in caps. I need to change it to the Proper style ( the
first
letter is capitalized and the rest lower case). How can I do this?

Hi Don,

I get DON in all three cells!

Should, of course, read:

I get Don in all three cells!

(Proper case!)

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Don,

On a blank worksheet,
In A1 enter: DON
In A2: enter FRED
In A3 enter TOM

Now run your sub.

What do you get?

I get DON in all three cells!

As I said you need to loop, either through each area or each cell.
Adapting your sub:

Sub makeproper2()
Dim ar As Range

On Error Resume Next
For Each ar In Activesheet.UsedRange.SpecialCells(xlCellTypeConst ants)
With ar
.value = Application.Proper(.value)
End With
Next ar
On Error GoTo 0
End Sub

---
Regards,
Norman



"Don Guillett" wrote in message
...
UsedRange is the area used for any purpose.
CurrentRegion is the area of contiguous cells with data.

OP did say "The WHOLE thing"

constituent??
--
Don Guillett
SalesAid Software

"Norman Jones" wrote in message
...
Hi Don,

I think that your sub will overwrite data if the SpecialCells range
comprises more than one area.

The sub needs to loop through the SpecialCells range's constituent
cells.

---
Regards,
Norman



"Don Guillett" wrote in message
...
from a post of mine on the 23rd.
if you have formulas, use this instead or you will wipe out the
formulas

Sub makeproper()
With ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants)
.Value = Application.Proper(.Value)
End With
End Sub


--
Don Guillett
SalesAid Software

"dumbfounded" wrote in message
...
I have a spreadsheet containing data to make mailing labels. The
whole
thing
was entered in caps. I need to change it to the Proper style ( the
first
letter is capitalized and the rest lower case). How can I do this?

Setting up your test and using below with xl2002 SP2, I got
a1 Don
a2 Fred
a3 Tom

Sub makeproper()
With ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants)
..Value = Application.Proper(.Value)
End With
End Sub
=====
I could NOT replicate.



--
Don Guillett
SalesAid Software

"Norman Jones" wrote in message
...
Hi Don,

On a blank worksheet,
In A1 enter: DON
In A2: enter FRED
In A3 enter TOM

Now run your sub.

What do you get?

I get DON in all three cells!

As I said you need to loop, either through each area or each cell.
Adapting
your sub:

Sub makeproper2()
Dim ar As Range

On Error Resume Next
For Each ar In Activesheet.UsedRange.SpecialCells(xlCellTypeConst ants)
With ar
.value = Application.Proper(.value)
End With
Next ar
On Error GoTo 0
End Sub

---
Regards,
Norman



"Don Guillett" wrote in message
...
UsedRange is the area used for any purpose.
CurrentRegion is the area of contiguous cells with data.

OP did say "The WHOLE thing"

constituent??
--
Don Guillett
SalesAid Software

"Norman Jones" wrote in message
...
Hi Don,

I think that your sub will overwrite data if the SpecialCells range
comprises more than one area.

The sub needs to loop through the SpecialCells range's constituent
cells.

---
Regards,
Norman



"Don Guillett" wrote in message
...
from a post of mine on the 23rd.
if you have formulas, use this instead or you will wipe out the
formulas

Sub makeproper()
With ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants)
.Value = Application.Proper(.Value)
End With
End Sub


--
Don Guillett
SalesAid Software

"dumbfounded" wrote in
message
...
I have a spreadsheet containing data to make mailing labels. The
whole
thing
was entered in caps. I need to change it to the Proper style ( the
first
letter is capitalized and the rest lower case). How can I do this?

Hi Don,

I could NOT replicate

Because I erroneously gave the test cells as A1. A2 and A3 instead of A1, A3
and A5. Try testing again using these cells.

As another example, I entered the following data into a blank sheet:

DON
123 HIGH STREET
ANYTOWN
<<==Blank row
FRED
345 OTHER STREET
ANOTHER TOWN
<<==Blank row
TOM
678 YET ANOTHER STREET
YET ANOTHER TOWN

After running your sub I get:

Don
123 High Street
Anytown
<<==Blank row
Don
Don
Don
<<==Blank row
Don
123 High Street
Anytown

---
Regards,
Norman



"Don Guillett" wrote in message
...
Setting up your test and using below with xl2002 SP2, I got
a1 Don
a2 Fred
a3 Tom

Sub makeproper()
With ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants)
.Value = Application.Proper(.Value)
End With
End Sub
=====
I could NOT replicate.



--
Don Guillett
SalesAid Software

"Norman Jones" wrote in message
...
Hi Don,

On a blank worksheet,
In A1 enter: DON
In A2: enter FRED
In A3 enter TOM

Now run your sub.

What do you get?

I get DON in all three cells!

As I said you need to loop, either through each area or each cell.
Adapting
your sub:

Sub makeproper2()
Dim ar As Range

On Error Resume Next
For Each ar In
Activesheet.UsedRange.SpecialCells(xlCellTypeConst ants)
With ar
.value = Application.Proper(.value)
End With
Next ar
On Error GoTo 0
End Sub

---
Regards,
Norman



"Don Guillett" wrote in message
...
UsedRange is the area used for any purpose.
CurrentRegion is the area of contiguous cells with data.

OP did say "The WHOLE thing"

constituent??
--
Don Guillett
SalesAid Software

"Norman Jones" wrote in message
...
Hi Don,

I think that your sub will overwrite data if the SpecialCells range
comprises more than one area.

The sub needs to loop through the SpecialCells range's constituent
cells.

---
Regards,
Norman



"Don Guillett" wrote in message
...
from a post of mine on the 23rd.
if you have formulas, use this instead or you will wipe out the
formulas

Sub makeproper()
With ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants)
.Value = Application.Proper(.Value)
End With
End Sub


--
Don Guillett
SalesAid Software

"dumbfounded" wrote in
message
...
I have a spreadsheet containing data to make mailing labels. The
whole
thing
was entered in caps. I need to change it to the Proper style ( the
first
letter is capitalized and the rest lower case). How can I do this?