Results 1 to 2 of 2
Thread: Moving text (2000)
2003-08-18, 17:53 #1
- Join Date
- Jan 2001
- Thanked 0 Times in 0 Posts
Moving text (2000)
I have inherited a spreadsheet that has data as follows:
Cell A1 Cell B1 Cell C1
ABC Company <blank> 440-444-1234
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.
2003-08-18, 18:17 #2
- Join Date
- Jul 2002
- Pittsburgh, Pennsylvania, USA
- Thanked 342 Times in 335 Posts
Re: Moving text (2000)
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.