Moving text (2000)

[Sherri S]

I have inherited a spreadsheet that has data as follows:

Cell A1 Cell B1 Cell C1
ABC Company <blank> 440-444-1234
Cell A2
1234 Main Street, Cleveland, OH 44123

For each record, I need to move the address info over one cell to the right and up one cell (to cell B1 in this example). I'm trying to write a macro to do this but haven't figured it out. Any hints would be appreciated.

[sdckapr]

1) you can do it manually: (make a copy of the sheet FIRST in case you make a mistake, though this method allows UNDO, where a macro will not)

enter in number 1 in D1, in D2: =D1+1.
copy D2 down to fill your data
In E1 enter 1, in E2 enter 2, In E3 enter =E1 and copy E3 down the col.
Highlight cols D&E and copy- paste special-values

Copy select A2 to endof data and copy to B1
Sort by Primary Col E and sec Col D
Go to first cell in E with a 2 and delete all the last rows (they have just addresses) the ones have all of them.

Additionally I would do a text to column and separate the Address, City, State and Zip into separate columns (first separate with comma delimiter) then separate the state-zip using a space as delimiter.
Make sure you MOVE the telephone numbers to a column before you do a text-to-columns to avoid overwriting.

Steve