Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    word to excel (line breaks)

    OK, What I've got is a Word 2000 document that I need to import into Excel 2000. Some lines in Word are broken up by manual line breaks (^l) and some by carriage returns (^p). I want the lines with line breaks at the end to all show up in a single Excel cell, each on its own line within the cell (normally created in Excel by using ALT+ENTER). The lines with paragraph marks will indicate a new cell in Excel (it normally does this anyway). The resulting Excel worksheet will only be 1 column wide.

    I've thought about trying to perform some sort of find/replace in Word to replace line breaks with the character code for ALT+ENTER and then doing a copy/paste or import, but I don't know the code to use or if this is even possible.

    Any suggestions?

    Mark

    (I'm cross-posting this message to the Word forum.)

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: word to excel (line breaks)

    Try replacing your manual line break in Word with a string of nonsense characters. Then in Excel try the following Macro to replace the nonsense string with the ALT + Enter (Chr(10)) character.
    <pre> Cells.Replace What:=" *123456* ", Replacement:=Chr(10), LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    </pre>

    where *123456* is your nonsense string.
    One note - it seems to work better when there are spaces around the nonsense string in the word doc and in the macro. I guess it provides better separation.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    New Lounger
    Join Date
    Feb 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: word to excel (line breaks)

    Catharine,

    It worked great! (I really need to learn VBA...) Have a cookie. <img src=/S/yum.gif border=0 alt=yum width=15 height=15>

    Thanks so much,

    Mark <img src=/S/read.gif border=0 alt=read width=19 height=28>

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Tallahassee, Florida, USA
    Posts
    901
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: word to excel (line breaks)

    <img src=/S/kiss.gif border=0 alt=kiss width=15 height=15>

    Catharine -- thank you so much... I knew I'd seen these somewhere, did not (at the time) add to my faves, but made a mental note. It worked beautifully! Thanks bunches. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    k
    Karen

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: word to excel (line breaks)

    Hey - it all goes 'round you know ...
    Right now, I'm picking Geoff's brain over on the VBA board...
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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