Truncated text when copying text from one excel doc to another
 

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?

Should'nt happen as you described - except there is a problem with a
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 a
limit on the contents of a cell size, but Excel will tell you of this
at the time of the copy.

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

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

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

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



sunny pete Wrote:
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?


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=395285

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.

sunny pete wrote:

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?

--

Dave Peterson

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