Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    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
    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.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts