Uppercase text to Proper text

[Ken Hoagland]

I'm new at this so please excuse me.

I have an excel spreadsheet in Office 97.
It's all in upper case letters and I need it in
upper/lower case letters.

Can anyone tell me how I can simply change a column (say
column "C") from uppercase text to Proper text?

Example: MR & MRS KEN HOAGLAND to Mr & Mrs Ken Hoagland

Must I do a formula in code module or is there a simple
way to do this? If not, how do I do it any other way?

 

[Alan]

Make a backup copy of your file first and try this on the COPY, don't
experiment with the original

With the data in column C
In D1 (or in any blank column)
=PROPER(C1)
and drag down to the end of the range in column C,
Highlight the whole of the data in column D, that is the non blanks from D1
Right click > Copy,
Click C1, right click > Paste Special > Values > OK
Delete or Clear contents in column D
HTH
Regards,
Alan

 

[Don Guillett]

one way to try
sub makeproper
for each c in columns(3)
c.value=application.proper(c)
next
end sub

 

[Anders S]

Ken,

Assuming your list starts in C1,

- save the file
- select column D and do Insert>Columns
- in D1 enter =PROPER(C1)
- select D1 and drag the little square in the bottom right corner of the cell as far as needed
- select column D and do Copy
- do Edit>Paste Special, click Values + OK
- delete column C if you don't need it anymore

HTH
Anders Silvén

 

[Peo Sjoblom]

If it is a onetimer you can just use a help column say D and use a formula
like

=PROPER(C1)

reselect the formula and double click the lowere right corner to copy dow
the formula, while select copy the contents, then in plaxe do edit>paste
special as values. Delecte column C.
If it is on a regular basis you can use macro like this

Sub PropCase()
Application.DisplayAlerts = False
Dim R As Range
For Each R In Selection.Cells
If R.HasFormula Then
R.Formula = "=PROPER(" & Mid(R.Formula, 2) & ")"
Else
R.Value = Application.Proper(R.Value)
End If
Next
Application.DisplayAlerts = True
End Sub


slect and copy it, open the workbook, press Alt + F11, click insert module
and paste the above,
press Alt + Q to close the VBE. select the range with text and press Alt +
F8 to run the macro

 

[Wave]

A simple solution is to enter a formula referencing the
first cell of the column you want to change in the
following format:

=proper($a1)

then copy this formula all the way down the column where
you want the data to be.

Next, what you might want to do is then copy the entire
column to a new column, but paste it with "values" only.

Good luck

 

[David McRitchie]

Hi Ken,
A macro solution is what you need to change the contents of a
column in place. But you want to be able to select the entire
column and not have to be real careful that you select only cells
that need to be changed. The difference is that if you try to
select an entire column and don't restrict the range to the used
cells it can take several minutes per column. Suggest you
use the Make_Proper macro at
http://www.mvps.org/dmcritchie/excel/proper.htm
you will see several time savings items documented on the
web page or you can just use the macro and not worry about it.
Use of SpecialCells automatically restricts usage to used range.

Instructions to install and use a macro are on my getstarted.htm
page, but there are links on the page at the top for that.