Step 7: Finishing Up

Picture of Finishing Up
Now you don't want formulae, you want names. We're almost finished. Highlight Cells D2 through E6. Inside the highlighted area, click your right mouse button. The border should change to a moving dotted line. Select Copy from the shortcut menu. Then, right mouse again inside the highlighted area, but this time select Paste Special. Click on the Values radio button, and select OK. Check any cell that had a formula (like D2 or E5); the formula should have been replaced by the formula's result.

Click on the A at the top of column A, and holding down you left mouse button drag to column C. Right mouse in the highlighted area and select Delete; you will be left with your list of names with the first and last names in different columns.

This is a flexible technique that you can apply to a lot of situations. Have fun!
very informational...... Thanks....!
KEUrban (author)  dilip.markule3 years ago
Glad you found it useful.
Thanks, very informative and nicely done.............making understand excel language in simple English..................Gotta query............can i post
KEUrban (author)  haroonshaikh7864 years ago
Of course, post your question. We're all here to help.
ttibbenham4 years ago
I like your technique. Excel also has a built-in 'text to columns' feature ( under Data in Excel 2007 ) which does something similar.
KEUrban (author)  ttibbenham4 years ago
Thanks for your comment. Text-to-columns works in a lot of cases; this technique can give you more control. You can also use it to modify the text. For instance, instead of pulling the entire first name, you could pull just the first character and then add in a period. It would be like this (I'm assuming the first name is in cell A5):


The ampersand is a shortcut for concatenating (sticking together) two pieces of text, in this case the first character on the left, along with (by the &) a period (inside the quotes). You can't make those kinds of on-the-fly changes to your data using text-to-columns.
mhacy4 years ago
Great! thanks!
KEUrban (author)  mhacy4 years ago
You are very welcome. Glad you found this useful.
confused736 years ago
Thanks a million !!! Working on a project and this saved time.
KEUrban (author)  confused736 years ago
Glad I could help!
nailz76 years ago
thanks, learnt something new today
CAR_RAMROD6 years ago
I always knew this was possible in Excel, I just never knew how. Very well explained, I am certain i will use this.
mnapier616 years ago
Excellent! Thanks!