Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing upper case characters to upper/lower
I have a file of names and addresses that were entered in all upper case. Is
there a fast way to change this file into upper and lower case? Thanks for any help you can give me. I have read some of the former answers regarding this and they refer to use of macro's....I a novice user and need more guidance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing upper case characters to upper/lower
By "upper and lower" I assume you mean Proper Case.
By formula enter =PROPER(cellref) in a hellper cell and copy down or across. When happy, select these cells and Copy then Paste SpecialValuesOKEsc. Delete original cells. By macro........... Sub Proper_Case() 'David McRitchie, programming, 2003-03-07 Dim rng1 As Range, rng2 As Range, bigrange As Range Dim Cell As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual On Error Resume Next Set rng1 = Intersect(Selection, _ Selection.SpecialCells(xlCellTypeConstants)) Set rng2 = Intersect(Selection, _ Selection.SpecialCells(xlCellTypeFormulas)) On Error GoTo 0 If rng1 Is Nothing Then Set bigrange = rng2 ElseIf rng2 Is Nothing Then Set bigrange = rng1 Else Set bigrange = Union(rng1, rng2) End If If bigrange Is Nothing Then MsgBox "All cells in range are EMPTY" GoTo done End If For Each Cell In bigrange Cell.Formula = Application.Proper(Cell.Formula) Next Cell done: Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Since you're not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... First...create a backup copy of your original workbook. To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + r to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. Run or edit the macro by going to ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. Gord Dibben MS Excel MVP On Mon, 17 Dec 2007 13:54:28 -0800, Richard Zignego wrote: I have a file of names and addresses that were entered in all upper case. Is there a fast way to change this file into upper and lower case? Thanks for any help you can give me. I have read some of the former answers regarding this and they refer to use of macro's....I a novice user and need more guidance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing file in all upper case to upper and lower case | Excel Discussion (Misc queries) | |||
Change from mixed caps and upper lower to all upper lower case | Excel Worksheet Functions | |||
Change the text from lower case to upper case in an Excel work boo | Excel Discussion (Misc queries) | |||
How do I convert all upper case excel sheet into upper and lower . | Excel Discussion (Misc queries) | |||
changing lower case to upper case | Excel Discussion (Misc queries) |