Changing to lower case

S

Scott Brooks

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
 
B

Billy Liddel

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
 
S

Scott Brooks

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
 
B

Billy Liddel

Thanks for the feedback, your welcome

Peter

Scott Brooks said:
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
Click to expand...
 
Top