This workaround will work for you. Basically it swaps out the line break character in Word and replaces it with the one Excel requires.
Word refused to play ball and I could not get it to do the complete replacement - it doesn't seen to like special Char(10) which is the the one we need in Excel! So for the moment the job is done in 2 parts, replace special Char(13) in Word with the string xxx, and then replace string xxx in Excel with special Char(10).
What you need to do to try it out is:
1 Confirm which character WORD is using for your line break . I suspect it will be
^p and you can test this by opening your WORD document and searching for
^p (using search and replace) and it should take you to the first line break
2 Then use "search and replace" in WORD and replace the relevant incidences of
^p with
xxx
(you may need to do this in blocks, otherwise all your text will end up in one cell!)
3 Paste the amended text into cell A1 in EXCEL
4 Either use the formula
=SUBSTITUTE(A1,"xxx",CHAR(10)) in cell B1 (ensure that WrapText is selected for B2) or you could use the macro below adapted to your needs.
NOTE
xxx is case sensitive
Char(10) in Excel is equivalent of
Chr(10) in vba
Replace function in VBA is the eqivalent of the
Substitute function in Excel
[
Sub LineBreak()
Dim str1 As String, str2 As String
str1 = Range("A1").Value
str2 = Replace(str1, "xxx", Chr(10))
Range("B1").Value = str2
End Sub