Pasting multi-line data from Word into Excel

I am trying to paste multi-line text from Word into Excel.

Excel (97) decides that the data would look nicer in separate cells, breaking between cells at each Line Break character.

This isn't what I want!  I have tried replacing hard line breaks with soft line breaks in Word, but it doesn't help.

For some strange reason, Excel uses ALT-ENTER as its soft line break, whereas Word uses SHIFT-ENTER.  But I can't generate ALT-ENTER in Word (the key combination doesn't work) and if I paste an ALT-ENTER from Excel into Word, it appears as a ENTER hard line break.

Any ideas how to get Excel to paste one block of text (containing ENTER characters) as one cell?
MarkyMarkDAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve KnightIT ConsultancyCommented:
Depends how much you need to paste but yif you F2 to edit a cell then paste it will go in complete with line feeds...

Steve
0
pauloaguiaCommented:
It has exactly the same effect as Steve's suggestion but you can also edit/paste/whatever in the formula text bar (the one just above the worksheet next to the = sign).

Paulo
0
MarkyMarkDAuthor Commented:
Thanks for the comments so far, and they are OK as far as they go, but I want to paste lots of individual cells from a Word table into an Excel worksheet.  The solutions you have given don't let me do that and it would be very onerous to do it one cell at a time.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MarkyMarkDAuthor Commented:
Thanks for the comments so far, and they are OK as far as they go, but I want to paste lots of individual cells from a Word table into an Excel worksheet.  The solutions you have given don't let me do that and it would be very onerous to do it one cell at a time.
0
Steve KnightIT ConsultancyCommented:
OK, try this, as long as you only need the text, not the fonts etc. :-)

This VBA routine to run in Word will take the currently selected part of the document and export any tables (complete with line breaks) to a CSV file which you can import into Excel.  Select All the Word document then run this to export all tables.

Each table is seperated by a blank row then "NEXT TABLE" then a blank row.

If not sure about anything, please ask.

hth

Steve

Sub CopyTablesToCSV()

Rem SKnight 21-Feb-2003
Rem Steps through each table in selection and creates CSV file
Rem to import into Excel including multi-line cells etc.

Dim fr, cols, rows, c, r As Integer
Dim t As Table

fr = FreeFile
Open "c:\import.csv" For Output As #fr

For Each t In Selection.Tables
  cols = t.Columns.Count
  rows = t.rows.Count
  For r = 1 To rows
    For c = 1 To cols
      t.Cell(r, c).Select
      If c > 1 Then Print #fr, ",";
      Print #fr, Chr$(34); ReplaceCR(Selection.Text); Chr$(34);
    Next c
    Print #fr, ""
  Next r
  Print #fr, ""
  Print #fr, "NEXT TABLE"
  Print #fr, ""
Next ' next table.

Close #fr
Shell "excel C:\import.csv", vbNormalFocus

End Sub

Function ReplaceCR(ByVal strIn As String) As String

'remove CR chr$(13) and replace with LF chr$(10)
a% = 1
Do While InStr(a%, strIn, Chr$(13)) > 0
   a% = InStr(a%, strIn, Chr$(13))
   Mid$(strIn, a%, 1) = Chr$(10)
Loop

' Remove chr$(7), seems to be at end of each cell
If Right$(strIn, 1) = Chr$(7) Then strIn = Left$(strIn, Len(strIn) - 1)

ReplaceCR = strIn
End Function
0
Steve KnightIT ConsultancyCommented:
MarkyMark, Any luck with that code?

Steve
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MarkyMarkDAuthor Commented:
Yes, thanks very much Steve and sorry for the delay.  I need to work out how to apply this code so that my users can do the transfer themselves, but it does the job very nicely.

I only need to copy one table, so I can simplify it somewhat, but I need to copy it into a specific place in a spreadsheet so that will take a little more work - but I can handle that part of the VBA myself.
0
Steve KnightIT ConsultancyCommented:
Great, sorry to have chased but a lot of people ask and are never heard from again otherwise....

Thanks for the points & grade.


Steve
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.