Cell 255 character limit and formula

[Guest]

I have a spreadsheet with descriptions that are well over the 255 character
limit for a cell. I need to move that data to another sheet and add
characters to the beginning and end of the string. The formula that I use is
=concatenate("!!<",CellDataFromOriginalSheet,">!!) that formula works for
all cells in the original sheet with the exception of those that are longer
than 255 characters. It truncates those that are longer. If I do a simple
range copy from the original sheet to the new sheet then all of the cell
contents are copied over and it will not truncate. How can I add the
beginning !!< and ending >!! characters to the entire content of the sheet?
Now before you tell me to edit each line seperately please be advised that
there are 3,046 lines of description that need to be copied to the new sheet.

I was going to try a find and replace but there isn't any one character that
I can search on at the beginning.

 

[Ken Wright]

="!!<"&Sheet1!A1&">!!"

seems to work fine for me, as does

=CONCATENATE("!!<",Sheet1!A1,">!!")

Original cell on sheet 1 was 405 characters / Dest cell on sheet2 is 411
characters

Didn't try in 97, but works in 2000/2002/2003

 

[Guest]

Hi Ken,
Thank you very much for the quick reply. I tried it again with the
following line in Sheet1!A1, and used the concatenate statement. The result
follows the original line.

-----------------------------original line start------------------
This engagement ring is not for everyone but for those that appreciate the
beauty of a fancy cut rock, it the ring for them. With its fancy oval cut
12mmx10mm quality CZ it speaks of exalted beauty and at 4 cts of the value of
the wearer. This engagement ring is made from highly polished quality
sterling silver and it is made to order so you can have it made for your
loved one perfect from the start. This ring makes a perfect promise ring and
since some may confuse it for white gold it is also one of the most
affordable solutions for someone on a tight budget.Beauty, tradition, and
love can all be yours and your loved one with this very special ring.Not only
is this piece gorgeous, but being custom made just for you makes it extra
special, plus being our own line you can be sure that there are not going to
be many others who have it. Combine that with this amazing affordability and
you have a piece that’s beautiful in all ways.Please note that the item is an
extreme close-up so you can gauge its fine details, and the item may be
smaller than it appears. Please allow 3-14 days for delivery. <p>This
items is custom made to order and not stocked unless otherwise indicated in
the description or inventory report. It can take 3-30 days to make but
usally 14-21 days on average.</p>
---------------------------original line
end---------------------------------------------

I just tried it again, and I think it is actually working. When it displays
on the new sheet it is trucated at the "...close-up so you can guage its
fine" but when I save it as a CSV and then reopen it, it seem to save the
entire text with the !!< on the front and the !!> on the end... For some
strange reason the whole line is actually built but it doesn't display the
entire cell.

Curious... got any ideas why it wouldn't display the entire cell? Try it
on your copy please, let me know if you can see the entire line.

Thanks

 

[Dave Peterson]

Are your sheets in the same workbook?

If yes, then your (corrected) formula:
=concatenate("!!<",CellDataFromOriginalSheet,">!!")

Worked ok for me.

If the worksheets were in different workbooks, then the fromula worked ok for me
as long as the "sending" workbook was open.

When I closed that sending workbook and recalculated that formula, the results
got truncated to 255 characters. (That's the way excel works.)

 

[Guest]

Dave,
Yes the sheets are in the same workbook. As I said it works, just doesn't
look as though it does. After I run the formula down through all 3000 rows,
some display correctly, some do not. However, when I save the file as a CSV,
and then reopen it using the CSV everything is there the way it should
be...Perplexed...

 

[Ken Wright]

Help / Keyword = Specifications

Length of cell contents (text)32,767 characters. Only 1,024 display in a
cell; all 32,767 display in the formula bar.

 

[Dave Peterson]

And just to add to Ken's reply.

Maybe it'll be as easy as widening the column or increasing the rowheight???