Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I change text to a different case
Each person on my list has put their own name in themselves, in upper and
lower and a combination of both cases. How do I get it to be one or the other without re-typing each name? (there are thousands)! |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I change text to a different case
Let's say the names are in A1:A1000
In B1 (insert a new column if needed) enter =PROPER(A1) Copy this down to B100 - the quickest way is to double click the fill handle (little solid square in lower right corner of active cell) Now you have a column with "bad" data and one with "good" but the good data comes from formulas that rely on the bad data. Select the good data; click the Copy tool and (with out moving the selection) use Edit | Paste Special and specify Values in the dialog. Now the "good" values are text not formulas So you can now delete column A best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Raoul" wrote in message ... Each person on my list has put their own name in themselves, in upper and lower and a combination of both cases. How do I get it to be one or the other without re-typing each name? (there are thousands)! |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I change text to a different case
Raoul
Bernie has given you the method using the PROPER function. I realize you posted in "newusers" group so this may be over the top. If you want to change all at once without using the PROPER function and have to deal with formulas use this macro. Sub optProper_Click() '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 If 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 Thu, 19 Jul 2007 04:30:01 -0700, Raoul wrote: Each person on my list has put their own name in themselves, in upper and lower and a combination of both cases. How do I get it to be one or the other without re-typing each name? (there are thousands)! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change text case | Excel Worksheet Functions | |||
Change the text from lower case to upper case in an Excel work boo | Excel Discussion (Misc queries) | |||
Change Text Case | Excel Worksheet Functions | |||
How do I change the case of text | New Users to Excel | |||
How do I change existing text from lower case to upper case | Excel Discussion (Misc queries) |