This is a common problem and one without a quick-fix as far as I know.

I had a reason to do the same thing and I had to write a VBA routine to read the contents of any cells with more than 255 characters in them and store in a list, then open the destination workbook and re-plot the recorded text values.

Something like (I'm just tapping this out off the top of my head, I'm only 90% sure it will work, but tweak it if necessary):

Sub CopyLongText(xSourceSheet as Worksheet,xDestinationsheet as Worksheet)

Dim tLongCells As String

For Each xCell in xSourceSheet.UsedRange.Cells
If Len(xCell.Text) 'greater than' 255 then
tLongCells = tLongCells & xCell.Address & "|" & xCell.Text & "|"
End If
Next xCell

xDestinationsheet.Workbook.Activate
xDestinationsheet.Activate

While tLongCells 'greater than' ""
posa = instr(1,tLongCells,"|")
posb = instr(posa+1,tLongCells,"|")
tAddress = left(tLongCells,posa-1)
tText = mid(tLongCells,posa+1,posb-posa-1)

If IsError(Evaluate("=" & tText)) then
Range(tAddress).NumberFormat = "@"
End If
Range(tAddress).value = tText

tLongCells = mid(tLongCells,posb+1)
Wend
End Sub


Hope this helps.

Regards,
BizMark