Microsoft Office Forums Returns in Word table text become multiple rows when pasted in Excel

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-09-2019, 08:53 AM
blorence blorence is offline Returns in Word table text become multiple rows when pasted in Excel Windows 7 64bit Returns in Word table text become multiple rows when pasted in Excel Office 2013
Novice
Returns in Word table text become multiple rows when pasted in Excel
 
Join Date: Jan 2019
Posts: 5
blorence is on a distinguished road
Question Returns in Word table text become multiple rows when pasted in Excel

I'm working on improving invoicing for a company I work with, and I'm trying to make their Word tables more practical for my use in Excel. I want to create a template in Word that will be easily pasted into Excel (and then importable into my own database).



Below is what I've created in Word:


Which works unless someone uses returns/paragraph breaks (which they are wont to do, especially with addresses). Below is what it looks like when I paste the above into Excel:


This makes the table totally unsortable without a lot of processing. I'd love to be able to sort by name. Is there a way to prevent cells from creating new rows in a Word table when there are line breaks? Maybe a way to force only one line of text per cell (that would be wrapped within the cell)?

Thanks!
Reply With Quote
  #2  
Old 01-09-2019, 01:38 PM
macropod's Avatar
macropod macropod is offline Returns in Word table text become multiple rows when pasted in Excel Windows 7 64bit Returns in Word table text become multiple rows when pasted in Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,543
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

The behaviour is normal and cannot be changed. That said, you can sort a table in Word.

In any event, if you want to import the data into a database, you'll need separate fields for the address, locality, state and postcode.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 01-09-2019, 02:01 PM
blorence blorence is offline Returns in Word table text become multiple rows when pasted in Excel Windows 7 64bit Returns in Word table text become multiple rows when pasted in Excel Office 2013
Novice
Returns in Word table text become multiple rows when pasted in Excel
 
Join Date: Jan 2019
Posts: 5
blorence is on a distinguished road
Default

Hmm. That may just have to do. Thanks!
Reply With Quote
  #4  
Old 01-09-2019, 03:46 PM
macropod's Avatar
macropod macropod is offline Returns in Word table text become multiple rows when pasted in Excel Windows 7 64bit Returns in Word table text become multiple rows when pasted in Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,543
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

If you're wedded to having the data in Excel, you could use a macro like:
Code:
Sub Demo()
Application.ScreenUpdating = False
Dim xlObj As Object, xlWkBkObj As Object
With Selection.Tables(1).Range
  'Pre-format the table
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "[^13^l]"
    .Replacement.Text = Chr(182)
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .MatchWildcards = True
    .Execute Replace:=wdReplaceAll
  End With
  'Copy the table
  .Copy
End With
ActiveDocument.Undo
'Start Excel if not running
On Error Resume Next
Set xlObj = GetObject(, "Excel.Application")
' Start Excel if it isn't running
If xlObj Is Nothing Then Set xlObj = CreateObject("Excel.Application")
With xlObj
  'Add a workbook
  Set xlWkBkObj = .Workbooks.Add
  With xlWkBkObj.Sheets(1)
  'Paste the data
    .Paste '.Range("A1")
    'Post-format the data
    With .UsedRange
      .HorizontalAlignment = 1 'xlGeneral
      .WrapText = False
      .Columns.AutoFit
      .Replace Chr(182), Chr(10)
      .Columns.AutoFit
      .Rows.AutoFit
    End With
  End With
  .CutCopyMode = False
  .Visible = True
End With
Application.ScreenUpdating = True
End Sub
For PC macro installation & usage instructions, see: http://www.gmayor.com/installing_macro.htm
For Mac macro installation & usage instructions, see: https://wordmvp.com/Mac/InstallMacro.html
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 01-23-2019, 09:27 AM
blorence blorence is offline Returns in Word table text become multiple rows when pasted in Excel Windows 7 64bit Returns in Word table text become multiple rows when pasted in Excel Office 2013
Novice
Returns in Word table text become multiple rows when pasted in Excel
 
Join Date: Jan 2019
Posts: 5
blorence is on a distinguished road
Default

Forgot to say this code works like a dream! Thanks so much.
Reply With Quote
  #6  
Old 01-23-2019, 11:24 AM
blorence blorence is offline Returns in Word table text become multiple rows when pasted in Excel Windows 7 64bit Returns in Word table text become multiple rows when pasted in Excel Office 2013
Novice
Returns in Word table text become multiple rows when pasted in Excel
 
Join Date: Jan 2019
Posts: 5
blorence is on a distinguished road
Default Could a macro insert a column that contains the filename of the document in every cell?

I have invoices sent to me in the form of Word tables, each with the filename as the date of the invoice. The table itself does not contain the date, so I am wondering if there is a way to insert a column (preferably on the left) where the cells contain the filename (the date) in every row. Eventually I combine each file to one spreadsheet, so having the date in the first column would be fantastic.

I receive 7 invoices at once each week, so any "today" functions aren't really useful.

Any thoughts?
Reply With Quote
  #7  
Old 01-23-2019, 01:18 PM
macropod's Avatar
macropod macropod is offline Returns in Word table text become multiple rows when pasted in Excel Windows 7 64bit Returns in Word table text become multiple rows when pasted in Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,543
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Is this related to: https://www.msofficeforums.com/word-...iple-rows.html ?
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #8  
Old 01-23-2019, 01:21 PM
blorence blorence is offline Returns in Word table text become multiple rows when pasted in Excel Windows 7 64bit Returns in Word table text become multiple rows when pasted in Excel Office 2013
Novice
Returns in Word table text become multiple rows when pasted in Excel
 
Join Date: Jan 2019
Posts: 5
blorence is on a distinguished road
Default

It is, although it doesn't have to be done in conjunction with the Word-to-Excel task we discussed there. Apologies if I shouldn't have made a new thread, it just seemed like a different topic to me.
Reply With Quote
  #9  
Old 01-23-2019, 01:57 PM
macropod's Avatar
macropod macropod is offline Returns in Word table text become multiple rows when pasted in Excel Windows 7 64bit Returns in Word table text become multiple rows when pasted in Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,543
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Threads merged.

It would have been helpful if you'd said up-front what the full requirements are. In addition to needing the additional column, you're evidently pasting multiple tables into the same Excel workbook, so pasting into a new, empty, workbook isn't the end of the matter. Presumably you also need to add the data to below the existing data. At which point it becomes better to automate this from Excel so multiple documents can be processed instead of doing things piecemeal.

Accordingly, please provide the full details of what you're trying to achieve.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Tags
enter, filename as document text, insert column, paste, rows, vba in word

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
a table pasted in word from excel is not similar to the original table in excel Mukapa Word Tables 1 01-18-2016 07:31 AM
Modify a just-pasted Excel table p45cal Word VBA 2 09-26-2015 01:02 PM
Excel Multiple Rows Merged into Word (Vertical Format) ats1025 Mail Merge 3 11-19-2013 02:21 PM
Returns in Word table text become multiple rows when pasted in Excel Add multiple rows to Excel 2010 table at cursor position C J Squibb Excel Programming 12 11-07-2013 07:35 AM
connectors between cells in pasted Excel table eNGiNe Word 7 10-16-2013 11:58 PM


All times are GMT -7. The time now is 04:54 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft