Results 1 to 8 of 8
  1. #1
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Cut and Paste Excel <> Word (2003)

    I have a problem cutting and pasting between Excel and Word, and would be grateful for advice on how to proceed - before I have to resort to writing code.

    I have a very long list in Excel, with two columns of information. Many of the entries in the right hand column have multiple paragraphs, entered by using Alt-Enter in Excel. I copy the two columns and paste them into word, giving me a two column table with manual line breaks where Excel had the paragraph breaks. When I copy this table and paste it back to excel, each cell with manual line breaks appears as 2 cells, and the corresponding left hand column cells have been merged.

    I need to copy the data there and back to support other things I am doing, for example we need to do a spell check and there is mixed character formatting which Excel's spell check screws up. I don't want to do this manually as there are about 700 rows and it fills about 60 pages in Word.

    Any suggestions of how I can successfully copy this data back from Word to Excel after manipulating it in Word?

    StuartR

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cut and Paste Excel <> Word (2003)

    Before copying the edited table in Word, replace all manual line breaks with a placeholder sequence that doesn't occur by itself in the table. For example, enter <code>^l</code> in the Find what box and <code>@@@@</code> in the Replace with box.
    After replacing, copy the table and paste into Excel.
    Then replace the placeholder sequence with Alt+010 (the code for Alt+Enter in Excel).

  3. #3
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Cut and Paste Excel <> Word (2003)

    Hans,

    Unfortunately doing a global replace in Excel removes all manual font formatting from any cell where a replacement happens, which is exactly the problem I was trying to avoid in the first place!

    StuartR

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cut and Paste Excel <> Word (2003)

    I'm afraid nothing springs to mind directly.

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Cut and Paste Excel <> Word (2003)

    Hmmm, what if you turned on track changes before doing the spell check, and then used a VBA procedure to read the changes and implement them in the Excel sheet? Not sure it's worthwhile for an infrequent task...

  6. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Cut and Paste Excel <> Word (2003)

    I think my easiest solution will be to take Hans' idea of replacing all line breaks in word with a recognisable string, and then to search for these in Excel and type the Alt-Enter manually. This will preserve all the cell layouts and character formatting with the least effort.

    Thanks for all the ideas.

    StuartR

  7. #7
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Cut and Paste Excel <> Word (2003)

    I did the deed today, there were 187 instances of a manual line break in word, and it took nearly two hours to type Alt-Enter over my placehoders in Excel.

    Thanks

    StuartR

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cut and Paste Excel <> Word (2003)

    Yuck! I wish I had thought of something better! <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

Posting Permissions

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