(Written for versions - 97 and 2002)
Using UPPER, LOWER and PROPER Text Functions in
Exel to Change Case
I needed to change some columns of text within an Excel spreadsheet, from PROPER case (i.e. text that has the appropriate capitalization), to all capital letters.
Excel Help, my Excel reference books and an Internet search did not provide detailed instructions. And the instructions offered were "glitchy." They did however give enough direction for me to come up with the following steps. I cannot say what a preferred method, that actually works, would be. However, these steps work well.
I have made them fairly detailed for the beginning Excel user and have also included images of a sample Excel spreadsheet to illustrate the text. After doing this once, you will see how straightforward and easy it is to convert Text Case in Excel.
Changing the Text Case
1. Open the Excel Spreadsheet.
2. Create an empty column for the capitalized text. To do this:
Click on any cell in the column to the right of the column of text that you want to change. Go to: Insert...Columns and Click. You should have an empty column, which will have the same cell formatting as the column to the left. Image 1
3. In the empty column, click on the cell that is next to the first name that you want to change case. Image 2 In this cell: type =UPPER(cell reference for the adjacent, left cell, i.e. A2, B2, etc.) Notice that what you are typing is also appearing in the formula bar, above. Image 3. Press: ENTER.
4. You now have the first name on your list entered in the new column in all capital letters. To apply this to the remainder of the column: Click the cell with the all capital text. Notice that it now has a bold outline and in the lower right corner there is a small rectangle. Image 4.
5. Place your cursor over the small rectangle and you will see that your cursor changes shape (crosshair). Left Click and Drag down the column to the end of the text in the left column. Image 5. Release the mouse button and your all capitals list is done. Don't forget to type in your column heading, as it will be blank. Image 6.
6. Repeat these steps for the remaining columns you need to change the text case. In this example the text was converted from PROPER to UPPER case. These steps work for the UPPER, LOWER and PROPER text functions. Just insert which ever change you need in the first cell's formula before the (cell reference).
Hide and Unhide Columns
7. Next, depending upon which data you need to print, you can hide those columns you won't use. To do this:
Click on any cell within the column you need to hide. Then go to: Format...Column...hide and Click. Keep doing this to each column you want hidden, before printing. Image 7. Image 8.
8. The Format...Column...unhide feature of Excel doesn't always work and if you have the first column in your spreadsheet hidden, it won't work at all. Image 9. (Note tip highlighted in red) So, if you want to unhide any column, first: go to Edit...and click on Go To. This will bring up a dialog box in which you will enter a cell reference for the column you want to unhide. Image 10. Type this in the box next to Reference on the bottom. For example, if you want to unhide Column A, just type in A1. If you want to unhide Column C, type in C1, etc. Then Click OK on the dialog box. Next go to Format...Column...unhide and your column will be visible.
Images for Instructions
1 - 10
Copyright 2014 Castlejb.com