Tex Case in spreadsheet

A

AndrewS

I have approximately 1000 rows of address data in a
cloumn that I need to change from Upper Case to
Upper/Lower Case

e.g.
From:
123 BLUEJAY WAY AUSTRALIA
To:
123 Bluejay Way Australia

Thanks in advance for all assistance
 
C

CarlosV

Add a new column and copy the PROPER() function all the
way down.
A B C
1 123 BLUEJAY WAY =PROPER(B1)
2 987 SEAHORSE PL =PROPER(B2)
 
N

Norman Harker

Hi Andrew!

You could use a helper column for just one column to change and use:

=PROPER(A1)
Copy down
Select the range changed > Copy
Edit > Paste Special > Values
OK

Or you could use a subroutine:

Sub Propercase_macro()
Dim MySelect As Range
Dim EachCell As Range
On Error Resume Next
Set MySelect = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
If MySelect Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each EachCell In MySelect
EachCell.Value = StrConv(EachCell.Value, vbProperCase)
Next EachCell
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


And you might see the following for more help:

http://www.mvps.org/dmcritchie/excel/proper.htm
Or
http://www.cpearson.com/excel/case.htm
 
Top