Pasting multi-line data from Word into Excel

Posted on 2003-02-19
Medium Priority
Last Modified: 2008-02-01
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?
Question by:MarkyMarkD
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 43

Expert Comment

by:Steve Knight
ID: 7980702
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...


Expert Comment

ID: 7981995
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).


Author Comment

ID: 7992390
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.
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


Author Comment

ID: 7992996
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.
LVL 43

Expert Comment

by:Steve Knight
ID: 7994689
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.



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)

' 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
LVL 43

Accepted Solution

Steve Knight earned 400 total points
ID: 8016846
MarkyMark, Any luck with that code?


Author Comment

ID: 8017402
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.
LVL 43

Expert Comment

by:Steve Knight
ID: 8017714
Great, sorry to have chased but a lot of people ask and are never heard from again otherwise....

Thanks for the points & grade.


Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question