Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1307
  • Last Modified:

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?
0
MarkyMarkD
Asked:
MarkyMarkD
  • 4
  • 3
1 Solution
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now