Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a table from Word
Hello,
I am trying to copy a table from Word into Excel. The cells contain mostly text. Excel Help says to select the table cells in Word and the "copy" command , then select the top left cell in Excel and use the "paste" command - very simple. However, the data is not pasted fromcell to cell: Many of the cells in the word table contain paragraph breaks. Excel separates data from a single Word cell into multiple cells based on these paragraph breaks. Is there any way to get Excel to ignore the paragraph breaks within the text and simple paste everything from the Word table cell into a since Excel cell? Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a table from Word
I am not aware of any inbuilt method...
What I do is something like the following (which you may have to play around with to get it right); In Word Replace paragraph marks with something which is not likely to be in my cell values like ~ or $... Copy the table and paste in Excel... This avoids the splits into multiple cells.. In Excel I use the formula in adjacent col =SUBSTITUTE(B45,"$",CHAR(10)) Then format the cells with 'World Wrap'... Once everything is the way I want I do Copy/Paste Special|Values... Another way which sometimes meets the requirement is to insert a dummy col in word table with one numbers or something like ROW. This splits (for a row) the cell with max lines, say 10 into 10 cells and merges the 10 cells (for the dummy cell) in the same row... "BenO" wrote: Hello, I am trying to copy a table from Word into Excel. The cells contain mostly text. Excel Help says to select the table cells in Word and the "copy" command , then select the top left cell in Excel and use the "paste" command - very simple. However, the data is not pasted fromcell to cell: Many of the cells in the word table contain paragraph breaks. Excel separates data from a single Word cell into multiple cells based on these paragraph breaks. Is there any way to get Excel to ignore the paragraph breaks within the text and simple paste everything from the Word table cell into a since Excel cell? Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a table from Word
Thanks, I'll try it!
"Sheeloo" wrote: I am not aware of any inbuilt method... What I do is something like the following (which you may have to play around with to get it right); In Word Replace paragraph marks with something which is not likely to be in my cell values like ~ or $... Copy the table and paste in Excel... This avoids the splits into multiple cells.. In Excel I use the formula in adjacent col =SUBSTITUTE(B45,"$",CHAR(10)) Then format the cells with 'World Wrap'... Once everything is the way I want I do Copy/Paste Special|Values... Another way which sometimes meets the requirement is to insert a dummy col in word table with one numbers or something like ROW. This splits (for a row) the cell with max lines, say 10 into 10 cells and merges the 10 cells (for the dummy cell) in the same row... "BenO" wrote: Hello, I am trying to copy a table from Word into Excel. The cells contain mostly text. Excel Help says to select the table cells in Word and the "copy" command , then select the top left cell in Excel and use the "paste" command - very simple. However, the data is not pasted fromcell to cell: Many of the cells in the word table contain paragraph breaks. Excel separates data from a single Word cell into multiple cells based on these paragraph breaks. Is there any way to get Excel to ignore the paragraph breaks within the text and simple paste everything from the Word table cell into a since Excel cell? Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a table from Word
Saved from a previous post:
When I want to transfer a word table to excel, I do this: If your cells in your word table contain paragraph mark or linebreak characters, then excel will bring them over as separate cells. One way around it is to convert those paragraph marks & linebreaks to unique characters, then copy|paste and then convert them back to linefeeds. I like this technique (inside a copy of the word file): Select your table. Edit|replace|Special (show More if required) Find what: (paragraph mark under Special button) replace with: $$$$$ (if $$$$$ doesn't appear in the table) replace all Same thing with Manual Line break (from under Special). Now copy the table into Excel. Edit|Replace Replace what: $$$$$ Replace with: ctrl-j (hit and hold the control key and hit j) replace all. You may have to use Format|cells|Alignment tab|check wrap text Don't forget to close the word document without saving (or hit undo as many times as necessary). Sheeloo wrote: I am not aware of any inbuilt method... What I do is something like the following (which you may have to play around with to get it right); In Word Replace paragraph marks with something which is not likely to be in my cell values like ~ or $... Copy the table and paste in Excel... This avoids the splits into multiple cells.. In Excel I use the formula in adjacent col =SUBSTITUTE(B45,"$",CHAR(10)) Then format the cells with 'World Wrap'... Once everything is the way I want I do Copy/Paste Special|Values... Another way which sometimes meets the requirement is to insert a dummy col in word table with one numbers or something like ROW. This splits (for a row) the cell with max lines, say 10 into 10 cells and merges the 10 cells (for the dummy cell) in the same row... "BenO" wrote: Hello, I am trying to copy a table from Word into Excel. The cells contain mostly text. Excel Help says to select the table cells in Word and the "copy" command , then select the top left cell in Excel and use the "paste" command - very simple. However, the data is not pasted fromcell to cell: Many of the cells in the word table contain paragraph breaks. Excel separates data from a single Word cell into multiple cells based on these paragraph breaks. Is there any way to get Excel to ignore the paragraph breaks within the text and simple paste everything from the Word table cell into a since Excel cell? Thanks. -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a table from Word
Thanks for your help Dave. I was successfully able to transfer the data into
the correct cells, but I encountered another problem: When I replace the $$$$$ with the ctrl-j, I lose all of my text formatting. Some of the text I copied is underlined, some strikethrough, etc. All of the formatting is there when I paste, but it is gone once I use the replace routine to re-insert my paragraph breaks. Any further suggestions? I appreciate your help. Ben "Dave Peterson" wrote: Saved from a previous post: When I want to transfer a word table to excel, I do this: If your cells in your word table contain paragraph mark or linebreak characters, then excel will bring them over as separate cells. One way around it is to convert those paragraph marks & linebreaks to unique characters, then copy|paste and then convert them back to linefeeds. I like this technique (inside a copy of the word file): Select your table. Edit|replace|Special (show More if required) Find what: (paragraph mark under Special button) replace with: $$$$$ (if $$$$$ doesn't appear in the table) replace all Same thing with Manual Line break (from under Special). Now copy the table into Excel. Edit|Replace Replace what: $$$$$ Replace with: ctrl-j (hit and hold the control key and hit j) replace all. You may have to use Format|cells|Alignment tab|check wrap text Don't forget to close the word document without saving (or hit undo as many times as necessary). Sheeloo wrote: I am not aware of any inbuilt method... What I do is something like the following (which you may have to play around with to get it right); In Word Replace paragraph marks with something which is not likely to be in my cell values like ~ or $... Copy the table and paste in Excel... This avoids the splits into multiple cells.. In Excel I use the formula in adjacent col =SUBSTITUTE(B45,"$",CHAR(10)) Then format the cells with 'World Wrap'... Once everything is the way I want I do Copy/Paste Special|Values... Another way which sometimes meets the requirement is to insert a dummy col in word table with one numbers or something like ROW. This splits (for a row) the cell with max lines, say 10 into 10 cells and merges the 10 cells (for the dummy cell) in the same row... "BenO" wrote: Hello, I am trying to copy a table from Word into Excel. The cells contain mostly text. Excel Help says to select the table cells in Word and the "copy" command , then select the top left cell in Excel and use the "paste" command - very simple. However, the data is not pasted fromcell to cell: Many of the cells in the word table contain paragraph breaks. Excel separates data from a single Word cell into multiple cells based on these paragraph breaks. Is there any way to get Excel to ignore the paragraph breaks within the text and simple paste everything from the Word table cell into a since Excel cell? Thanks. -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a table from Word
That's one of the problems with the edit|Replace.
The only solution I know is to have a macro that loops through each cell and replaces the unique string with the vblf and applies the formatting to each character (character by character). Or leave the data in MSWord??? BenO wrote: Thanks for your help Dave. I was successfully able to transfer the data into the correct cells, but I encountered another problem: When I replace the $$$$$ with the ctrl-j, I lose all of my text formatting. Some of the text I copied is underlined, some strikethrough, etc. All of the formatting is there when I paste, but it is gone once I use the replace routine to re-insert my paragraph breaks. Any further suggestions? I appreciate your help. Ben "Dave Peterson" wrote: Saved from a previous post: When I want to transfer a word table to excel, I do this: If your cells in your word table contain paragraph mark or linebreak characters, then excel will bring them over as separate cells. One way around it is to convert those paragraph marks & linebreaks to unique characters, then copy|paste and then convert them back to linefeeds. I like this technique (inside a copy of the word file): Select your table. Edit|replace|Special (show More if required) Find what: (paragraph mark under Special button) replace with: $$$$$ (if $$$$$ doesn't appear in the table) replace all Same thing with Manual Line break (from under Special). Now copy the table into Excel. Edit|Replace Replace what: $$$$$ Replace with: ctrl-j (hit and hold the control key and hit j) replace all. You may have to use Format|cells|Alignment tab|check wrap text Don't forget to close the word document without saving (or hit undo as many times as necessary). Sheeloo wrote: I am not aware of any inbuilt method... What I do is something like the following (which you may have to play around with to get it right); In Word Replace paragraph marks with something which is not likely to be in my cell values like ~ or $... Copy the table and paste in Excel... This avoids the splits into multiple cells.. In Excel I use the formula in adjacent col =SUBSTITUTE(B45,"$",CHAR(10)) Then format the cells with 'World Wrap'... Once everything is the way I want I do Copy/Paste Special|Values... Another way which sometimes meets the requirement is to insert a dummy col in word table with one numbers or something like ROW. This splits (for a row) the cell with max lines, say 10 into 10 cells and merges the 10 cells (for the dummy cell) in the same row... "BenO" wrote: Hello, I am trying to copy a table from Word into Excel. The cells contain mostly text. Excel Help says to select the table cells in Word and the "copy" command , then select the top left cell in Excel and use the "paste" command - very simple. However, the data is not pasted fromcell to cell: Many of the cells in the word table contain paragraph breaks. Excel separates data from a single Word cell into multiple cells based on these paragraph breaks. Is there any way to get Excel to ignore the paragraph breaks within the text and simple paste everything from the Word table cell into a since Excel cell? Thanks. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying/pasting accurately from a Word table (carriage returns) | Excel Discussion (Misc queries) | |||
Merged Cells when copying a table from Word to Excel | Excel Discussion (Misc queries) | |||
Excel Word copying info from Excel to Word without gridlines / bor | Excel Discussion (Misc queries) | |||
Link table from excel to word using word VBA | Excel Discussion (Misc queries) | |||
copying from word | Excel Discussion (Misc queries) |