Upper Case Vs: Lower case

B

Bill Carpenter

Each month I receive a spreadsheet approx 3000 lines long. A column is the
"city" name. It downloads all different ways. Some all CAPS some case
sensitive some all lower case. Each month I correct each city manually
typing it correctly. Is there a way I can highlight the column and do this
all at once, so fror example it would convert to San Francisco? This is
taking much to long the manual way. Thanks for any tips or help.
 
B

Bill Carpenter

Absolutely amazing. But that is Excel. I had time to do this this morning,
and worked like a charm. Is there some way I can save the macro in
Tools-macro-macro for all work books, and not have to copy each time? Thanks
so much!
 
G

Gord Dibben

Bill

Global macros can be saved in your Personal.xls or in an add-in.

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 the 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
 
P

PlayingToAudienceOfOne

Copy the following macro:

Sub Change_Case()
Dim ocell As Range
Dim Ans As String
Ans = Application.InputBox("Type in Letter" & vbCr & _
"(L)owercase, (U)ppercase, (S)entence, (T)itles ")
If Ans = "" Then Exit Sub
For Each ocell In Selection.SpecialCells(xlCellTypeConstants, 2)
Select Case UCase(Ans)
Case "L": ocell = LCase(ocell.Text)
Case "U": ocell = UCase(ocell.Text)
Case "S": ocell = UCase(Left(ocell.Text, 1)) & _
LCase(Right(ocell.Text, Len(ocell.Text) - 1))
Case "T": ocell = Application.WorksheetFunction.Proper(ocell.Text)
End Select
Next

End Sub
 
Top