phoffric
asked on
How to Copy Word Table to Excel so that the number of Rows are the same?
Attached below is a Word doc with a sample table that I try to copy to Excel. One row in the Word table may become multiple rows in Excel. That causes other problems when processing the Excel table. Ideally, the copy to Excel gives one row for each ACTION FIELD entry as it does in this Word table.
Word Table showing first 8 rows:
Excel Table after copying first 8 rows has 22 rows. I would like Excel to have only 8 rows, since that is what Word had.
Both the full sample Word table, and the attempted copy to Excel (with 22 rows) is attached.
Word_to_Excel_Issue.docx
ExcelHasTooManyRows.xlsx
Word Table showing first 8 rows:
Excel Table after copying first 8 rows has 22 rows. I would like Excel to have only 8 rows, since that is what Word had.
Both the full sample Word table, and the attempted copy to Excel (with 22 rows) is attached.
Word_to_Excel_Issue.docx
ExcelHasTooManyRows.xlsx
Martin Liss
I'm guessing that if you did a global search for the newline character (Chr(13)) and replace it with a space that you could maintain the structure when imported into Excel.
ASKER
>> global search for the newline character (Chr(13))
In Word, I presume? How to use that (Chr(13)) in a global search?
What I did try is Ctrl/H. Special -> Manual Line Break; and replaced that with a period followed by a space. Almost got there. Although the first row, 2nd Column did shrink a little, it didn't shrink completely. Got:
In Word, I presume? How to use that (Chr(13)) in a global search?
What I did try is Ctrl/H. Special -> Manual Line Break; and replaced that with a period followed by a space. Almost got there. Although the first row, 2nd Column did shrink a little, it didn't shrink completely. Got:
version date.
Additional information is shown belowASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The solution is to do a global replace for both the manual line break and the paragraph mark. Thank you Martin.
ASKER
The solution is to do a global replace for both the manual line break and the paragraph mark. Thank you Martin.
When I get to work, I'll record a macro that does these two global replaces. Hope it works at work.
When I get to work, I'll record a macro that does these two global replaces. Hope it works at work.
ASKER
How do you use the chr(13) to do the global replace?
In the Find & Replace dialog (that you can open with Ctrl+H)
In the "Find what:" box, press and hold the Alt key, and then enter the numbers 013 from the number pad that's on the right side of most keyboards of the keyboard. 013 is the carriage return character. You could also try 010 which is the linefeed character. If either one removes anything, follow it up with the other one.
In the "Find what:" box, press and hold the Alt key, and then enter the numbers 013 from the number pad that's on the right side of most keyboards of the keyboard. 013 is the carriage return character. You could also try 010 which is the linefeed character. If either one removes anything, follow it up with the other one.