limitations within excel!!

[Ian Mac]

On 2002-04-14 10:51, Chris Davison wrote:

I dunno what this means !

Me either

[Ian Mac]

recently the explaination for:

=OFFSET($C$1,0,0,MATCH(REPT("z",255),$C:$C))

is that 255 is the limit for a text string???!! I don't understand why that is!

as the limitations for a FORMULA are 255 BUT the limits for a text string (according to the help files) is 1024 (this only applies to 97, tested).

I'm at home now and =REPT("z",30000) seems to cope admirably, i.e. it doesn't return a #VALUE! error. and when I use LEN(A1) on this it returns 30000. so what makes 255 the largest text string???

I sort of know the answer, BUT to the people it's been given it's VERY misleading, because 255 is NOT the largest.

any answers????

_________________
Share the wealth!!
Ian Mac

[ This Message was edited by: Ian Mac on 2002-04-12 01:01 ]

[Ian Mac]

My mistake on th limits thing, according to the help files the following is the limits that Excel place upon th user,

Column width 0 (zero) to 255 characters
Row height 0 to 409 points
Maximum length of cell contents (text) 32,000 characters
Maximum length of formula contents 1,024 characters
Maximum number of sheets in a workbook Limited by available memory (default number of sheets is 3; the maximum number of sheets in a default workbook is 255)

[Chris Davison]

On 2002-04-12 01:00, Ian Mac wrote:
My mistake on th limits thing, according to the help files the following is the limits that Excel place upon th user,

Column width 0 (zero) to 255 characters
Row height 0 to 409 points
Maximum length of cell contents (text) 32,000 characters
Maximum length of formula contents 1,024 characters
Maximum number of sheets in a workbook Limited by available memory (default number of sheets is 3; the maximum number of sheets in a default workbook is 255)

Ian,

that was me wasn't it ?

it was *my* understanding of the formula, not a gospel version of the ultimate truth

It's good to see it being questioned though. Reading through, it looks like you're right, in theory it should be extended to 32,000 "z"s......

As Mark pointed out though in his original post, this doesn't help the efficiency of the formula : it may be better to incorporate an actual max text string value of the range first.

HOwever :

text string "asdfghj", sorted alphabetically, is before "zzz"

so in a list, zzz will be last (giving us the bottom limit of the range)

ergo, a string of 255 "z"s would only ever be replaced at the bottom of a list by a string of more than 255 characters, the first 255 of which must be "z"s.

Since the max column width is static at 255 charcaters, both would appear in a drop down data validation list as 255 "z"s, with the remainder of each being chopped off, visually.

So it would be pointless having them as the user would not be able to differentiate between the two.

All of this *assumed*, the user would only ever have a list that had 255 charcters maximum, thus the formula is applicable, even if the explanation is a bit misleading !

As you can tell, I'm no expert at all, still learning

cheers Ian
Chris


[ This Message was edited by: Chris Davison on 2002-04-13 05:49 ]

[Yogi Anand]

Hi Chris:
Practically speaking, for me even the use of a string of 2 to 3 Zs would do it. I think the significance here is on the facilitation of establishing the range dynamically!

[Chris Davison]

facilitation of establishing the range dynamically!

I dunno what this means !

[MrExcel]

April 14th

[P_Guru]

I have a related problem:

I have a merged set of cells containing explanatory text. Word wrap is on, but even though there is space for a tenth wrapped row, the ninth row extends, and is thus not visible.

When I print the document, some of the missing text prints, but not all. About 1250 characters print, the rest are ignored, even if I reduce the font size (not that I want to, but to get round it).

I can't find any settings that contribute to this, so have had to merge some other cells underneath and experiment with pasting and layout to make the print *look* correct.

Is there any better method?