Pasting cells with hard returns from Word to Excel 2010

[richardexcel]

I have a Word table the contains cells with hard returns. I want to paste the table into Excel so that the hard returns within a cell are preserved. I've tried the two methods below but they aren't working:
Method 1 - I replaced the hard returns in Word with "xx", pasted into Excel and then replaced the "xx" with "^p".
Method 2 - Instead of replacing the "xx" with "^p", I hit Alt-F to get into the VB editor and then hit Ctrl-G to get ito the immediate window, and then typed "selection.replace "xx", chr(10), and then hit enter to run it but instead of running it it just entered a hard return.
What am I doing wrong?

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this. Do Method 1 again, but in Excel when doing the Replace of the xx, in the 'Replace with' box press Ctrl+j instead of ^p

 

[VoG]

Maybe with a macro

Sub ImportWordTable1()
'Import one table to current sheet
Dim wdDoc As Object
Dim wdFileName As Variant
Dim TableNo As Integer 'table number in Word
Dim iRow As Long 'row index in Excel
Dim iCol As Integer 'column index in Excel
wdFileName = Application.GetOpenFilename("Word files (*.doc),*.doc", , _
"Browse for file containing table to be imported")
If wdFileName = False Then Exit Sub '(user cancelled import file browser)
Set wdDoc = GetObject(wdFileName) 'open Word file
With wdDoc
    TableNo = wdDoc.tables.Count
    If TableNo = 0 Then
        MsgBox "This document contains no tables", _
            vbExclamation, "Import Word Table"
    ElseIf TableNo > 1 Then
    TableNo = InputBox("This Word document contains " & TableNo & " tables." & vbCrLf & _
        "Enter table number of table to import", "Import Word Table", "1")
    End If
    With .tables(TableNo)
'copy cell contents from Word table cells to Excel cells
        For iRow = 1 To .Rows.Count
            For iCol = 1 To .Columns.Count
                On Error Resume Next
                Cells(iRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
                On Error GoTo 0
            Next iCol
        Next iRow
    End With
End With
Set wdDoc = Nothing
End Sub

 

[richardexcel]

Welcome to the MrExcel board!

Try this. Do Method 1 again, but in Excel when doing the Replace of the xx, in the 'Replace with' box press Ctrl+j instead of ^p

Thanks - it worked but it also put an extra space between each line. How can I get rid of these spaces? Also, what is Ctrl-J a shortcut for?

 

[Peter_SSs]

Thanks - it worked but it also put an extra space between each line. How can I get rid of these spaces?

I did not get extra spaces.

Excel Workbook
	A	B	C	D
1
2		Item 1Item 2	Item 3Item 4Item 5
3		Item 6Item 7	Item 8
4
Table from Word

Are you sure when you did the second replace that you put "xx" in the 'Find what' box not just "x"?

Are you sure in your Word table that there is only one paragraph marker between your lines of data in the table cells?

Also, what is Ctrl-J a shortcut for?

Linefeed or CHAR(10)

 

[richardexcel]

I did not get extra spaces.

Excel Workbook
	A	B	C	D
1
2		Item 1Item 2	Item 3Item 4Item 5
3		Item 6Item 7	Item 8
4
Table from Word

Are you sure when you did the second replace that you put "xx" in the 'Find what' box not just "x"?

Are you sure in your Word table that there is only one paragraph marker between your lines of data in the table cells?


Linefeed or CHAR(10)

I replaced the xx with Ctrl-J. I've tested it a several times now and each time I do a new copy and paste, a new space is added between the lines (within an Excel cell) - I'm now up to 10 spaced between lines! This happens even if I use a new Excel workbook or a new Word Table.

How do I tell if there is only one paragraph marker between the lines of data in the table cells?

Also, is there any way to preserve automatic bullets or dashes?

 

[Peter_SSs]

How do I tell if there is only one paragraph marker between the lines of data in the table cells?

A couple of ways.

1. In Word, click the show/hide paragraph markers button. It should be the top right button in the 'Paragraph' group on the Home ribbon.

2. When you replace the paragraph marker (^p) with xx, do you see more than 2 'x's together in your table? "xxxx" would indicate 2 successive paragrapgh markers.

Also, is there any way to preserve automatic bullets or dashes?

I don't know if/how you could preserve bullets as well as the multiple lines in one cell. The issue is that the bullets apply to a paragraph.

If you remove the paragraph markers, then you reduce the number of paragraphs in your cell to 1 which reduces the bullets to 1. When you reverse the replace in Excel this will not re-introduce the bullets to the newly created 'paragraphs' in your Excel cell.

If you don't remove the paragraph markers in word, the bullets will come over to Excel but, as we already know, each bullet point (because it is a separate paragraph) comes into a different cell in Excel.

Having said all that, this is not a strong area of mine so hopefully somebody else may have a way to achieve what you want.