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)!

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)!

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)!