To separate names without the need for a formula:

1. Select Column A (or the range of cells containing the list of names).
2. From the Data menu, select Text to Columns.
3. In Step 1 of 3 dialog box, select Delimited (if not checked by default).
4. In Step 2, select the Space checkbox.
5. In Step 3, select (or manually type) cell B1 in the Destination box.
6. Click Finish.

Note: Names that include a middle initial will separate to three columns; no middle initial will separate to two columns. You may wish to edit out middle initials prior to separating.

Also useful for formatting an addressbook in the .csv format in which case the delimiter will be the comma instead of the space.

In case you're copying-pasting names directly from the sheet and the case is a problem, you can also use the formula =UPPER(cell) =LOWER(cell) and =PROPER(cell) and then copy the formula for the rest of the rows.

Could you explain this please, Cheryl? You're so much better at it. I only tie myself up in knots every time I try to do it...

Jabberwocky, you were doing great till you stopped!

When you have a worksheet that has an odd assortment of upper or lowercase characters, the =PROPER function will convert text so that the first letter of all words are uppercase and everything else is lowercase. =LOWER converts everything to lowercase; =UPPER converts everything to uppercase.

For example, you have a name/address listing where names in Column A are all uppercase. Column B is the address, so you want to insert another column between A and B to make room for the formula to convert the text. In the first cell in the new Column B, type =PROPER(A1) and press Enter. You will see your text in cell A1 copy to the new column in the new case. Now you can use the AutoFill handle to fill the rest of Column B with the formula (or use keyboard shortcuts). After you've converted Column A, highlight the entire Column A and go to Format/Column and select Hide. You can also place your mouse pointer on the line between the column letters A and B, right-click, then select Hide.

Notes: The case doesn't matter when you type the words proper, upper, or lower in your formula -- Excel will automatically convert your formula to uppercase. When you click in a cell in the new Column B, you will see the underlying formula in the Formula Bar.

Edited because I meant to also add this shortcut: To repeat a formula down a range of cells, double click the bottom right corner of the first cell in the range and the formula will be automatically entered for the rest of the column.

I usually like to do a copy and Paste Special, values only, of the formula cells, to get the formula out of the loop, so I don't end up with errors somewhere down the road if I move the database, delete/change the referenced column of original values, etc. That's not necessary if you are going to keep everything in the original spreadsheet with the reference column hidden, but I find that when I forget to convert to values it somehow always chomps me on the posterior at some later point.