I have a 7000+ line worksheet that is ballooned into a very large
address book. Over the years some of the addresses were entered in all
caps, others (~6000) in corect case. Is there a way that I can
automatically change to proper case, ala MS Word? I am using Excel
2003, but can use Excel 2007.

Thanks for the advice.

-Scott

See

http://www.mvps.org/dmcritchie/excel/proper.htm

In article <(E-Mail Removed)>,
Scott Brooks <> wrote:

> I have a 7000+ line worksheet that is ballooned into a very large
> address book. Over the years some of the addresses were entered in all
> caps, others (~6000) in corect case. Is there a way that I can
> automatically change to proper case, ala MS Word? I am using Excel
> 2003, but can use Excel 2007.
>
> Thanks for the advice.
>
> -Scott

Scott

You could use this macro

Option Explicit

Dim rsp, c, sep As String, sp, rgt As String, tmp As String
Dim i As Integer, L As Integer, count As Integer
Dim cnt As Integer, rng As Range

Sub changeCase()
' Amended by Gord Gibbon MSVP
' so not to overwrite formulas in selection

rsp = InputBox("Enter U, P or L to choose Upper, Proper or Lower case", _
"Choose Case to Alter Text", "p", 100, 100)
For Each c In Selection
If UCase(rsp) = "U" Then
c.Formula = UCase(c.Formula)
ElseIf UCase(rsp) = "P" Then
c.Formula = Application.WorksheetFunction.Proper(c.Formula)
ElseIf UCase(rsp) = "L" Then
c.Formula = LCase(c.Formula)
End If
Next
End Sub

Regards
Peter
"Scott Brooks" wrote:

> I have a 7000+ line worksheet that is ballooned into a very large
> address book. Over the years some of the addresses were entered in all
> caps, others (~6000) in corect case. Is there a way that I can
> automatically change to proper case, ala MS Word? I am using Excel
> 2003, but can use Excel 2007.
>
> Thanks for the advice.
>
> -Scott
>

Thanks for the macro, that worked a bit too well, now I have to change
Cpa, Llc and some email addresses, but a find and replace has still
saved me MANY hours this weekend. Thanks again.

~Scott

On Fri, 2 Mar 2007 03:08:03 -0800, Billy Liddel
<(E-Mail Removed)> wrote:

>Scott
>
>You could use this macro
>
>Option Explicit
>
>Dim rsp, c, sep As String, sp, rgt As String, tmp As String
>Dim i As Integer, L As Integer, count As Integer
>Dim cnt As Integer, rng As Range
>
>Sub changeCase()
>' Amended by Gord Gibbon MSVP
>' so not to overwrite formulas in selection
>
>rsp = InputBox("Enter U, P or L to choose Upper, Proper or Lower case", _
> "Choose Case to Alter Text", "p", 100, 100)
>For Each c In Selection
> If UCase(rsp) = "U" Then
> c.Formula = UCase(c.Formula)
> ElseIf UCase(rsp) = "P" Then
> c.Formula = Application.WorksheetFunction.Proper(c.Formula)
> ElseIf UCase(rsp) = "L" Then
> c.Formula = LCase(c.Formula)
> End If
>Next
>End Sub
>
>Regards
>Peter
>"Scott Brooks" wrote:
>
>> I have a 7000+ line worksheet that is ballooned into a very large
>> address book. Over the years some of the addresses were entered in all
>> caps, others (~6000) in corect case. Is there a way that I can
>> automatically change to proper case, ala MS Word? I am using Excel
>> 2003, but can use Excel 2007.
>>
>> Thanks for the advice.
>>
>> -Scott
>>

Thanks for the feedback, your welcome

Peter

"Scott Brooks" wrote:

> Thanks for the macro, that worked a bit too well, now I have to change
> Cpa, Llc and some email addresses, but a find and replace has still
> saved me MANY hours this weekend. Thanks again.
>
> ~Scott
>
> On Fri, 2 Mar 2007 03:08:03 -0800, Billy Liddel
> <(E-Mail Removed)> wrote:
>
> >Scott
> >
> >You could use this macro
> >
> >Option Explicit
> >
> >Dim rsp, c, sep As String, sp, rgt As String, tmp As String
> >Dim i As Integer, L As Integer, count As Integer
> >Dim cnt As Integer, rng As Range
> >
> >Sub changeCase()
> >' Amended by Gord Gibbon MSVP
> >' so not to overwrite formulas in selection
> >
> >rsp = InputBox("Enter U, P or L to choose Upper, Proper or Lower case", _
> > "Choose Case to Alter Text", "p", 100, 100)
> >For Each c In Selection
> > If UCase(rsp) = "U" Then
> > c.Formula = UCase(c.Formula)
> > ElseIf UCase(rsp) = "P" Then
> > c.Formula = Application.WorksheetFunction.Proper(c.Formula)
> > ElseIf UCase(rsp) = "L" Then
> > c.Formula = LCase(c.Formula)
> > End If
> >Next
> >End Sub
> >
> >Regards
> >Peter
> >"Scott Brooks" wrote:
> >
> >> I have a 7000+ line worksheet that is ballooned into a very large
> >> address book. Over the years some of the addresses were entered in all
> >> caps, others (~6000) in corect case. Is there a way that I can
> >> automatically change to proper case, ala MS Word? I am using Excel
> >> 2003, but can use Excel 2007.
> >>
> >> Thanks for the advice.
> >>
> >> -Scott
> >>
>
>