Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
EaglesNest
 
Posts: n/a
Default Cell 255 character limit and formula

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.
  #2   Report Post  
Ken Wright
 
Posts: n/a
Default

="!!<"&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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"EaglesNest" wrote in message
...
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.



  #3   Report Post  
EaglesNest
 
Posts: n/a
Default

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 thats 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. <pThis
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

"Ken Wright" wrote:

="!!<"&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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"EaglesNest" wrote in message
...
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.




  #4   Report Post  
Dave Peterson
 
Posts: n/a
Default

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.)

EaglesNest wrote:

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.


--

Dave Peterson
  #5   Report Post  
EaglesNest
 
Posts: n/a
Default

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...

"Dave Peterson" wrote:

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.)

EaglesNest wrote:

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.


--

Dave Peterson



  #6   Report Post  
Ken Wright
 
Posts: n/a
Default

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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"EaglesNest" wrote in message
...
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...

"Dave Peterson" wrote:

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.)

EaglesNest wrote:

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.


--

Dave Peterson



  #7   Report Post  
Dave Peterson
 
Posts: n/a
Default

And just to add to Ken's reply.

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



EaglesNest wrote:

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...

"Dave Peterson" wrote:

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.)

EaglesNest wrote:

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.


--

Dave Peterson


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Create an if-then formula in Excel to limit column total? Nancy M Excel Discussion (Misc queries) 2 February 13th 05 11:47 PM
Defined range problem Pat Excel Discussion (Misc queries) 8 January 17th 05 12:25 PM
IF Function Help due to 7 limit John F Excel Worksheet Functions 11 January 12th 05 11:07 PM
Limiting characters in a cell Colin Hayes Excel Worksheet Functions 7 December 30th 04 04:20 PM
How can i work a formula for time limit? Roze Excel Worksheet Functions 2 November 25th 04 03:41 PM


All times are GMT +1. The time now is 03:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"