How do I change case in Excel?

QABoy · 1 Dec 2006 at 08:07

I've been on holiday for a week and the bloke who's done my job has filled our workshheets with SHOUTING. I thought it would have been a simple process of highlighting all the words, going to the menu and clicking on Change Case like in Word but I can't find anything. Any idea how I do it?

Origin · 1 Dec 2006 at 08:37

This is from Office 20003

Let's say you want to convert text from uppercase to lowercase or from lowercase to proper case to make it more readable. To change the case of text, use the UPPER, LOWER, or PROPER functions.

Example
The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.
Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

A
Name
nancy Davolio
1 Formula Description (Result)
2 =UPPER(A2) Changes text to all UPPERCASE (NANCY DAVOLIO)
=LOWER(A2) Changes text to all lowercase (nancy davolio)
=PROPER(A2) Changes text to Title Case (Nancy Davolio)

QABoy · 1 Dec 2006 at 08:47

Grief

I thought it would be a simple process of choosing about a 1000 cells and clicking on a button that says lower case.

ic1male · 1 Dec 2006 at 09:59

The easiest way is probably this -

If your column of SHOUTING is column A and your first item is in row 1 (cell A1).

At the next empty column, say B, type in cell B1

Code:

=PROPER(A1)

and press return

Then with cell B1 selected, hover your mouse over the black dot in the bottom right hand corner of the cell until the cursor changes to a thin black cross. Left click and then drag your mouse selection all the way down column B until you get to the last cell of column A. Let go and you should have the capitalization sorted.

QABoy · 1 Dec 2006 at 11:15

Thanks for that but typing anything in another cell buggers things up all over the sheet. (every cell is counted).

madmaxx · 1 Dec 2006 at 12:10

Surely the simple solution is to highlight all the cells with SHOUTING in them, paste them into a blank Word Document, and whilst they are highlighted go Format, Change Case. Then select lower case, and viola, you have a number of cells containing lower case writing. Then, with them still highlighted, copy, and go back into Excel, and paste them back over the top of the source cells. Sorted - I've just tried it with a spreadsheet here, and it seems to work ok.
Hope that helps.

meths · 1 Dec 2006 at 14:31

^^^That's a good idea^^^

I was gonna suggest using a small macro to do it but that's much easier.

QABoy · 2 Dec 2006 at 10:10

madmaxx said:
Surely the simple solution is to highlight all the cells with SHOUTING in them, paste them into a blank Word Document, and whilst they are highlighted go Format, Change Case. Then select lower case, and viola, you have a number of cells containing lower case writing. Then, with them still highlighted, copy, and go back into Excel, and paste them back over the top of the source cells. Sorted - I've just tried it with a spreadsheet here, and it seems to work ok.
Hope that helps.

That worked, thanks.