Converting text Case in Excel

The Problem: You have an Excel spreadsheet of names of clients/students/alumnae, and they are all in UPPER CASE.

The Task: You need to convert them all to “Proper Case” so you can use the list in a mail merge (for example). And, of course, there are about 2,000 names!

The Good News: It’s not as tedious as you might think.

Converting all uppercase text to proper case (as in proper names) is a 3-step process.

Step 1: Making Room for the Conversion

Create a blank column next to the column that contains the upper case text. If both first and last names are in the same cell, you only need 1 extra column. If the first name is in one column and the last name is in another, you will need 2 extra columns.

Here is a 2-column example:

case-2

Step 2: Converting the Text

  • Position your cursor in the blank column, in the cell immediately to the right of the first name you need to convert
  • Click on down-arrow in the Function Icon

    case-3

  • Click on More Functions…
  • This will display the Insert Function dialog box

    case-4

  • If the PROPER function is not listed (as it is in the above example) type the phrase “all caps” in the Search for a function text box.
  • Click on PROPER
  • Click OK
    This will display the Function Arguments dialog

    case-5

  • Click on the selection grid icon

    case-6

  • Then click on any cell containing all UPPERCASE text
  • Press ENTER
    The dialog should now look something like this:

    case-7

  • Click OK
  • Now the two cells should now look like this:

    case-8

  • Next, we will populate this function into all the new empty cells
  • Copy the single converted cell CTRL-C or Ctrl-OpenApple-C (You are actually copying the function)
  • Position your cursor in the top-most cell that remains to be converted and drag your mouse to select all the remaining items in the column that need to be converted

    case-9

  • Paste the function into all selected cells CTRL-V or Ctro-OpenApple-V
  • Now all your text should be converted

    case-10

  • Copy a converted cell and repeat the paste operation in the second column if you have one
  • This example now looks like this:

    case-11

Step 3: Converting the Function into Plain Text

The problem we have now, is that if we delete the upper case column, the Proper Case text in the new column will also disappear. This is because the contents of the column is actually the function that is converting the text – not the text itself – and the function is referring to that upper case column.  For example, if we were to select one of the converted cells, we will see in the text entry box of the spreadsheet a function statement – not the text of the name. (as shown below)

case-12
So we have to do something to convert the function into plain text.

 To do this:

  • Select all converted text
  • Copy it CTRL-C or Ctrl-OpenApple-C
  • Click on Paste > Paste Special >Values
  • Click OK

    case-13

This will replace the function with just the value that it returns. So instead of PROPER(M16), the cell now contains “Doe”. If we were to click on the cell containing converted text, we see the actual text in the text entry box as shown below:

case-14

Now you can delete the columns containing all caps and use the text in any way you wish.

case-15

Quick Reference Guides – Office 2007

guides   Need a little reminder now and then about how to perform a common task in Word, Excel or PowerPoint? Here are some PDF “Quick Reference Guides” for Office 2007.


(Guides courtesy of CustomGuide: Learn on Demand)

Click on the title to download and/or print the PDF file.

Excel

Gridlines and Borders in Excel are Not the Same

They may look the same visually, but they do not act the same.

Gridlines are used to make cell boundaries visible. You can change their color under Office Button>Excel Options>Advanced. You can set them visible or hidden.

grid-1

You can also set them to Print.

borders-3

However – as soon as you change the color or border of a cell – or of any adjoining cell – the gridlines will be lost. So you may end up with a very uneven application of lines and colors within a worksheet.

Borders can have different colors, line types and widths. You can assign different borders to multiple cells in a worksheet. Borders will always print and will not disappear if you change the color of a cell. 

Borders-2

Borders-1

 To set borders for a single cell or a group of cells:

1. Select the cell or group of cells

2. Click on the Border Icon

This will display the borders dialog box (at left)

3. Select the type of border you want

4. Select the type and color of the line

5. As soon as you click outside the dialog box, the border selection will be applied.

 

NOTE: If you sort the data, border and background colors of cells WILL NOT transfer with the sorted data.