change case

[Guest]

I am trying to change the case on a spreadsheet from upper case to proper
case. I have used the "Proper" formula, but it is not changing the cells I
am instructing it to change. It is putting the info into another cell. If I
try to put the formula in the cell that already has the text, it deletes the
text. Can anyone shed some light? Or, is there a site with step-by-step
instructions.

 

[Guest]

Well, that's how excel works.

If your data is in A1, type =PROPER(A1) in B1 then copy and paste values in
A1.

Dave

 

[Guest]

Thanks! I was trying to put it in an empty cell above, but I added a column
and was able to enter the formula in the cell next to it. And it worked! Then
I copied the formula for all the cells below that needed to be change.

 

[Gord Dibben]

Once you get the case the way you like using the PROPER formula copied down,
copy that column and Paste Special(in place)>Value>OK>Esc.

Delete the original column.


Gord Dibben MS Excel MVP

 

[David McRitchie]

Hi Ronnie,
You need a macro to do what you want. A function can only return
a value, so if you don't want the little dance routine to convert formulas
to values and remove the original column, you would need to use a
macro.

With a macro all you need to do is select the cells to be converted.
If the macro is properly written it will restrict itself to the used range
(one way is with SpecialCells), then you can select entire columns
or the entire worksheet. If it is not properly written converting a
single column could take 3 minutes (65536 cells on 600mHz with 128MB RAM).
You should hardly notice converting 4000 cells in a column.

Proper, and other Text changes -- Use of SpecialCells
http://www.mvps.org/dmcritchie/excel/proper.htm#proper