Excel nightmare


Posted by Ian McLeod on July 18, 2001 8:35 AM

I've got about 7,000 names, all done up like "John Doe", and the management now wants them redone as "DOE, John". I can't figure out how to automate it, and typing them all manually is giving me the screaming willies. Especially since they're expecting another 7 or 8,000 over the next few days. (!!)

Any ideas?

Ian

Posted by Aladin Akyurek on July 18, 2001 8:45 AM

Let "John Doe" be in A1.

In B1 enter: =UPPER(RIGHT(A1,LEN(A1)-SEARCH(" ",A1)))&", "&LEFT(A1,SEARCH(" ",A1)-1)

Copy down this formula as far as needed. When ready, select all cells in B with names, copy, then do a Paste Special|Values. Keep a copy of the formula somewhere before doing the last step.

Aladin


Posted by Always Looking on July 18, 2001 9:50 AM

Aladin - Nice one !!!!!!!!!!!!!!!



Posted by Ian McLeod on July 18, 2001 11:16 AM

THANK YOU!!! :)

Saved my bacon, this did (not to mention my eyesight. <LOL>)

Thanks again!