Proper case Help!!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ian Brown - 14 Sep 2004 22:22 GMT

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.

Reply to this Message

Ken Wright - 14 Sep 2004 22:58 GMT

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

Signature

Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

>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
[quoted text clipped - 17 lines]
>
> Ian.

Reply to this Message

David McRitchie - 16 Sep 2004 00:27 GMT

Take a look at
http://www.mvps.org/dmcritchie/excel/proper.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

> 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
[quoted text clipped - 17 lines]
>
> Ian.

Reply to this Message