Concatenating more than 255 error

[mahmed1]

Hi,

I am unable to concatenate more than 255 characters. How can i get round this.
The x at the end is just for example where i can make the text more than 255 characters. I just wanted to know how i would get around this problem

=TEXT(INT(NOW()),"dd/mm/yy")&" "&TEXT(MOD(NOW(),1),"h:mm")&" - "& "Go to town, take the 2 little ones with me.Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx."

 

[Jonmo1]

You can concatenate more than 255...
The problem is that the cell can only DISPLAY 1024 characters.
Not aware of any limit at 255
And that has nothing to do with concatenate...
And I don't think there's any way around it.


And this has nothing to do with the issue, but...

This
=TEXT(INT(NOW()),"dd/mm/yy")&" "&TEXT(MOD(NOW(),1),"h:mm")
can be reduced to
=TEXT(NOW(),"dd/mm/yy h:mm")

 

[Glory]

Does the limit at 1024 characters prevent a cell from actually holding those characters (eg, accesible through VBA)? See the post #5 in this thread to understand why I'm asking; I'm wondering if I gave a bad suggestion.

 

[Jonmo1]

No, the cell can hold something like 32000 characters (see the excel help files, search for limits).
But the cell can only DISPLAY 1024

This also varies per version of excel..
I'm in 2003

Also I belive it's the column width that is limited to 255.
So if the cell is formatted only 1 row in hieght, then you will only see 255

 

[xenou]

Good explanation. I've tested it now, based on Jonmo's comments.

This displays all characters (I can see the "Z" at the end)
=REPT("X",1023)&"Z"

This loses the 1025th character (I cannot see the "Z" at the end)
=REPT("X",1024)&"Z"

But if I wrap the cell contents and increase the width and height, I can get all characters from both formulas. I'm using Excel 2003.

ξ

 

[shg]

If you intersperse some line feeds, you can see lots more characters. Here's almost 9000:

=REPT(REPT("z", 254) & vbLf, 35)

EDIT: vbLf:=CHAR(10)