Truncated text when copying text from one excel doc to another

S

sunny pete

launch two excel documents
Enter a long text string into any cell >500 characters
Copy the cell and paste it to any cell in the other document

Result: the pasted text is truncated losing the end of the original text
string.

How can I fix this?
 
B

Bryan Hessey

Should'nt happen as you described - except there is a problem with
cell formatted as TEXT and a cell data length 512 to 1024 characters
(the cell is displayed as ##### symbols)

From a worksheet, if you Move & create-a-copy a worksheet there is
limit on the contents of a cell size, but Excel will tell you of thi
at the time of the copy.

For a cell, or range of cells, or whole worksheet, the Copy and Past
into a new sheet should copy all data up to 32,768 characters pe
cell.

The limit on what is displayed in the cell is 1,024 characters, or 40
points of row height.

To see the full cell contents select the cell and check the formul
bar.

Test the row height problem by reducing the font size for the cell, an
remember to restore the setting afterwards
 
D

Dave Peterson

Make sure you open both workbooks in the same instance of excel.

open the first any way you want.
but then File|open (your second workbook)

Then do the copy|paste

==
If you're opening the second workbook by double clicking on it in windows
explorer and it opens in a second instance--and you want to stop this
behavior...

Sometimes one of these works:

Tools|Options|General|Ignore other applications (uncheck it)

--- or ---

Close Excel and
Windows Start Button|Run
excel /unregserver
then
Windows Start Button|Run
excel /regserver

The /unregserver & /regserver stuff resets the windows registry to excel's
factory defaults.
 
B

BizMark

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 t
read the contents of any cells with more than 255 characters in them an
store in a list, then open the destination workbook and re-plot th
recorded text values.

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

Sub CopyLongText(xSourceSheet as Worksheet,xDestinationsheet a
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,
BizMar
 
Top