All CAPS to lowercase in Excel or Word 2

[dogleg43]

How can I change Excel files that are all CAPITAL letter to all lowercase letters. Most of these are then put into tables in MS-Word. Word has the option of changing everything to upper case, but not the other way.

 

[WMO]

Does your spreadsheet contain formulae?
It can be done with VBA.

 

[dogleg43]

Excel does have a function to do this rather easily.

Go to the INSERT/FUNCTION menu and in the popup window scroll down to the "LOWER" choice. Then select the cell you want to convert. If you have consecutive cells to convert just do some dragging or copying.

 

[electricpete]

I always type in +lower(<text in quotes>) or +lower(<CellReference>)

I didn’t realize there was such a thing as insert/function. That looks like a very handy way to find the function you want and get prompted for the arguments along with some help. Especially for complex functions with lots of arguments that are difficult to remember.

Star for you dogleg


=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[IRstuff]

Word indeed has the ability to change to all lower case:

Format|Change Case

You can also customize your toolbars to include this functionality as button and it will cycle through the choices that the menu command has, although, lower case is the first choice that pops up with all caps.

TTFN

 

[crossframe]

In MS Word, there is a "Change Case" option in the "Format" pull down. Choose "Sentence case" for normal sentence capitialization. In the final word document, watch for correct spaces between sentences, though.

 

[jhardy1]

Download a free copy of ASAP Utilities for Excel from

http://www.asap-utilities.com

- this has dozens of really useful add-ins for Excel, including converting all selected cells to lowercase, uppercase, make first character uppercase, make last character superscript, etc, etc.

Once you have tried it, you will never look back!

 

[electricpete]

you don't need an add-in to do this in excel.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[jhardy1]

electricpete,

True enough, you don't NEED an add-in - but ask yourself, which is REALLY easier and more efficient:

a) Write a VBA macro.

b) Go to a suitable blank cell; select "Insert | Function | Text | Lower", point to the first cell to be converted and click "OK". (Alternatively, just type in "=LOWER(CellReference)".) Now drag to create a full array of the data which is to be converted to lower case. This creates a copy of the whole range of data at a new location, concerted to lower case.

c) Download ASAP Utilities. Select a range of cells, select "ASAP Utilities | Text | Convert lowercase" and click "OK" - this converts the selected range in-situ - no duplication.

Just my $0.02 worth. Given that it's free, and gives you some 300 useful functions, why not try it?

(And no, in case you are wondering - I am not on commission - just a convert to a great free utility!)

 

[bltseattle]

I found a useful, simple macros for this at:

http://www.cpearson.com/excel/case.htm

You can enter these into your personal.xls and then assign them to a button or menu item.

So why do this when there is ASAP? It is true ASAP will provide these functions. But my experience with ASAP was that it had many functions I would never use, and some of its "hot-key" assignments conflicted with my own macros. If you have the expertise, or are willing to invest a couple of hours into learning how, build your own ASAP-like menu using macros defined in PERSONAL.XLS and Excel's built-in ability to customize toolbars, which allows a new drop down menu - ie "My Menu" - to be added. This way you can do more than ASAP utilities, such as formatting page setup with your firms' info in the header/footer, printing your timesheet, or whatever it is you do in Excel. You can still load up ASAP as an add-in for the specialized functions it does so well (I like the table of contents creator), then unload it if necessary.

 

[bpeirson]

For those of you who have the problem of working in CAPS for most of the day then switching to another program (such as Outlook). I often type 2 or 3 sentences without looking at the screen. When I do I see that all my text is in CAPS. I can now select all the text and copy it to the clipboard. I switch to excel and click a button which runs this code.

Sub ConvertToLowerCase()
Range("F3") = ""
Set mydata = New DataObject
mydata.GetFromClipboard
Range("F3") = mydata.GetText(1)
Range("F3").Value = LCase(Range("F3"))
Range("F3").Cut
End Sub

After the code is done I switch back to the other program and paste the converted text.

This code is a modification of the code at:

http://www.cpearson.com/excel/case.htm

 

[johnwm]

If you have Excel, you probably also have Word. Paste your text into a new Word doc and try:
Alt [OEU] ENTER FOR UPPER CASE
Alt [OEL] ENTER for lower case
Alt [OET] ENTER For Title Case
Alt [OES] ENTER For sentence case

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk

 

[electricpete]

Thanks for the clarification Julian. I'll have to try ASAP sometime.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[joerd]

Johnwm,

Have you ever tried Shift-F3 in Word?


Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[johnwm]

Thanks joerd, sure have! We all tend to use the shortcuts that we first learn, even when alternatives are available. When I do trainig on use of Office products I try to show 2 or 3 ways of doing the normal tasks, but most people seem to latch on to their favourite, and then stay with that (and its brothers)

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk

 

[joerd]

I agree with that statement. I learned the Shift-F3 actually in wordperfect, almost 20 years ago.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.