[SOLVED] 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?

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


--
BizMark