Change all text on Spread sheet

[Daniel]

Hi

I have Excel 2007, I have a Spread sheet with a few hundred lines and six
columns, the sheet contains cells with
text and numbers, sometimes in the same cell but mostly in separate cells.

The text has all been entered in CAPS, and I need to alter it for better
appearance.

Is there some way I can automatically change the Caps to lower case but
leaving the first letter
on each word in caps?

All help appreciated.

Regards

Daniel

 

[Ron Rosenfeld]

Hi

I have Excel 2007, I have a Spread sheet with a few hundred lines and six
columns, the sheet contains cells with
text and numbers, sometimes in the same cell but mostly in separate cells.

The text has all been entered in CAPS, and I need to alter it for better
appearance.

Is there some way I can automatically change the Caps to lower case but
leaving the first letter
on each word in caps?

All help appreciated.

Regards

Daniel

Perhaps the PROPER worksheet function will do what you want. And, with your requirements, I would run it as a macro, but run this first on a copy of your worksheet to make sure it does what you want.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first be sure the sheet you wish to process is selected and visible. Then <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

=====================================
Option Explicit
Sub FixAllCaps()
Dim r As Range, c As Range
Set r = Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
For Each c In r
c.Value = WorksheetFunction.Proper(c.Text)
Next c
End Sub
==========================

 

[Daniel]

Ron

Thank you so much, this worked exactly as I needed and so much better than I expected, I will be saving this advice for future use.

Regards

Daniel

 

[Ron Rosenfeld]

Ron

Thank you so much, this worked exactly as I needed and so much better than I expected, I will be saving this advice for future use.

Regards

Daniel

Glad to help. Thanks for the feedback.