Proper case Help!!

  • Thread starter Thread starter Ian Brown
  • Start date Start date
I

Ian Brown

I wonder if you can help me. I have a spreadsheet containing addresses,
however, they are all typed in UPPER CASE. How do I change the format so
it's in Proper Case?

I've tried the PROPER formula but it doesn't actually change the text. I
followed the examples here
http://office.microsoft.com/en-gb/assistance/HP052092241033.aspx and here
http://office.microsoft.com/en-gb/assistance/HP030561191033.aspx but it
doesn't 'change' the case at all. It just gives an illusion that it's
changed. The formula remains in the cell not the changed text.

In these examples column B shows the 'Proper Case'. Now delete column A and
you get error messages. The case wasn't changed at all, the formula just
gave it the illusions that it had been changed.

So, how do I actually change text in a spreadsheet from UPPER CASE to Proper
Case?

Thank you

Ian.
 
Do you know how to use macros? if so then try the following cribbed from one of
the folks in here, just not sure who unfortunately:-

Sub ProperCase()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim myCell As Range
Dim myRng As Range

On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells _
.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please select a range that contains text--no formulas!"
Exit Sub
End If

For Each myCell In myRng.Cells
myCell.Value = StrConv(myCell.Value, vbProperCase)
Next myCell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
 
Back
Top