Change from Upper to Lower Case

[BradP]

I am trying to convert a large amount of text from upper case to proper
case. The only way I can find to accomplish this is through the
function wizard, but that turns the cell into a formula, which I don't
want. How can I simply change the font from upper to proper case. I
know this is possible in Word, and would like to see if it is possible
in Excel. The following is an example of what I would like to do:

From: SMITH RESIDENCE
To: Smith Residence

 

[Paul B]

Brad, here is a macro that will do it, if you put it in your personal
workbook it will be available to use on all your workbooks

Sub TextConvert()
'By Ivan F Moala
‘will change the text that you have selected,
‘if no text is selected it will change the whole sheet
Dim ocell As Range
Dim Ans As String

Ans = Application.InputBox("Type in Letter" & vbCr & _
"(L)owercase, (U)ppercase, (S)entence, (T)itles ")

If Ans = "" Then Exit Sub

For Each ocell In Selection.SpecialCells(xlCellTypeConstants, 2)
Select Case UCase(Ans)
Case "L": ocell = LCase(ocell.Text)
Case "U": ocell = UCase(ocell.Text)
Case "S": ocell = UCase(Left(ocell.Text, 1)) & _
LCase(Right(ocell.Text, Len(ocell.Text) - 1))
Case "T": ocell = Application.WorksheetFunction.Proper(ocell.Text)
End Select
Next

End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **

 

[Frank Kabel]

Hi
you'll need VBA for this. See the following site for some macros
http://www.mvps.org/dmcritchie/excel/proper.htm

 

[brianwa]

Try this. Then run it down your list.

=proper(A1)

B

 

[Alan]

You can use the PROPER function, in an empty area do
=PROPER(A1)
and drag it to all of your text, then Copy > Paste Special > Values and
overwrite the original,
Regards,

 

[BradP]

Thank