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

[Guest]

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?

 

[Frank Kabel]

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

 

[Don Guillett]

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(xlCellTypeConstants)
..Value = Application.Proper(.Value)
End With
End Sub

 

[Guest]

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

 

[Norman Jones]

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.

 

[Don Guillett]

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

OP did say "The WHOLE thing"

 

[Norman Jones]

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(xlCellTypeConstants)
With ar
.value = Application.Proper(.value)
End With
Next ar
On Error GoTo 0
End Sub

 

[Norman Jones]

Hi Don,

I get DON in all three cells!

Should, of course, read:

I get Don in all three cells!

(Proper case!)

 

[Don Guillett]

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

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

 

[Norman Jones]

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