How do I change the case of text

[Guest]

I have a large spreadsheet and the text is all in uppercase. How would I
apply the formula for proper case to this data?

 

[Don Guillett]

try putting this in your personal.xls to use anytime needed.

Sub ChangeCase()
Application.ScreenUpdating = False
Dim r As Range
nCase = UCase(InputBox("Enter U for UPPER" & Chr$(13) & " L for
lower" & Chr$(13) & " Or " & Chr$(13) & " P for Proper", "Select
Case Desired"))
Select Case nCase
Case "L"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = LCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = LCase(r.Value)
End If
Next

Case "U"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = UCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = UCase(r.Value)
End If
Next
Case "P"

For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = Application.Proper(r.Formula)
'R.Formula = R.Value
Else
r.Value = StrConv(r.Value, vbProperCase)
End If
Next
End Select
Application.ScreenUpdating = True
End Sub

 

[Frank Kabel]

Hi
=PROPER(A1)

 

[David McRitchie]

You definitely want to go with a subroutine rather than a Worksheet Formula
to fix an entire worksheet. Because the subroutine can fix your data in place.

The worksheet formula requires an extra column for each conversion then you
have a lot of fixing up to do afterwards.

I would suggest the macros in
http://www.mvps.org/dmcritchie/excel/proper.htm
you have to copy two or three macros for this, but it will run considerably
faster than the macro you were already provided and you can
make some changes to the macro to match your data, as you probably
do not want each word changed to proper case. For instance my own
lastname would very strange as Mcritchie, and IBM would look strange
as Ibm. You can't program for every case but you can certainly program
for what you are most likely to encounter with your own data.

 

[Guest]

Hi:
I got frustrated with the change case problem and I've been reading the
mails as a beginner. Your reply sounds like speed dial compared to the
others.
But what does put it in your personal .xls file mean?
Carrie

 

[Don Guillett]

I assume that it works for you. Instead of putting in each workbook you use
you can put in your personal.xls file. Do window>unhide. If you have it then
touch the cancel button>use alt f11 to view code>find personal.xls and put
in a module. You can even assign a custom button to your toolbar if you use
often.

 

[Gord Dibben]

Carrie

Personal.xls is created the first time you record a Macro using Macro
Recorder.

It is used for making macros available to all open workbooks.

Tools>Macro>Record New Macro. A dialog box will come up asking you name the
macro and where to place it. Pick Personal Macro Workbook from the dropdown.
Copy and paste a couple of cells then Stop Recording.

You now have a Personal.xls in your Office\XLSTART folder. You can go to
Visual Basic Editor(Alt+F11) to view the macro you just recorded in a Module.

You can add more macros by recording or by typing/copying them into the
Module.

In this case, copy Don's code into the module.

You can do a File>Save from there or better yet hit ALT + Q to return to the
Excel window.

Then with Personal.xls active, hit Window>Hide.

When you close Excel you will be asked if you want to save Personal.xls. Yes!

It will open hidden next time you start Excel.

NOTE: when assigning macros to buttons or menu items you will have to precede
the macro name with Personal.xls.

i.e. Personal.xls!macroname

An alternative to Personal.xls is to create an add-in(*.xla) with your macros
in it and load it through Tools>Add-ins.

The benefit of this is that you don't have to precede the macro name with the
filename.

A disadvantage is that you will not see the macros in the Tools>Macro>Macros
dialog.


Gord Dibben Excel MVP