Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change all text on Spread sheet
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change all text on Spread sheet
On Thu, 30 Aug 2012 16:35:09 -0700 (PDT), Daniel wrote:
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 ========================== |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change all text on Spread sheet
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change all text on Spread sheet
On Thu, 30 Aug 2012 18:28:46 -0700 (PDT), Daniel wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to change the column width on a spread sheet? | Excel Worksheet Functions | |||
Is there a way to change all dates in a spread sheet | Excel Discussion (Misc queries) | |||
Change the sign on all numbers in a spread sheet | Excel Discussion (Misc queries) | |||
HOW DO YOU CHANGE AN ENTIRE SPREAD SHEET FROM LOWER CASE TO UPPER | Excel Discussion (Misc queries) | |||
How do I change spread sheet data into address labels? | New Users to Excel |