Changing to lower case

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Scott Brooks - 02 Mar 2007 02:53 GMT

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

Reply to this Message

JE McGimpsey - 02 Mar 2007 03:22 GMT

See

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

> 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
[quoted text clipped - 5 lines]
>
> -Scott

Reply to this Message

Billy Liddel - 02 Mar 2007 11:08 GMT

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

> 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
[quoted text clipped - 5 lines]
>
> -Scott

Reply to this Message

Scott Brooks - 02 Mar 2007 17:25 GMT

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

>Scott
>
[quoted text clipped - 35 lines]
>>
>> -Scott

Reply to this Message

Billy Liddel - 02 Mar 2007 22:43 GMT

Thanks for the feedback, your welcome

Peter

> 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
[quoted text clipped - 41 lines]
> >>
> >> -Scott

Reply to this Message