Text not displayed when cell formatted as text.

  • Thread starter Thread starter Duncan Bachen
  • Start date Start date
D

Duncan Bachen

I ran into a really bizarre problem today, and I'm wondering if anyone
else has experienced it.

I had a cell which was formatted as text (wrap text on). I filled it
up when typing, but there was still space left in the cell.

However, upon moving focus to another cell, the first cell showed
####, as if it couldn't display all the text.

Changing the font size had no effect. I simply had smaller ###
displayed.

I had to decrease the amount of text in the cell before it would be
displayed.

Here's the kicker, when I changed the cell format to General, the text
displayed properly, and again proved that there was additional blank
space for more text to be entered.

I wound up leaving it that way, but it was really strange. It's as if
when the cell is formatted as text, Excel is miscalculating it's
length.

Feel free to take this off-list if you want to discuss it personally.


-D
 
I've had the same problem before as well. I just re-sized the cell and all
worked ok. I don't know whys' or what-fors', but...............
Dan
 
You want weird....

Try putting text in a cell that has a length of exactly 255 characters.
I used: =rept("asdf ",51)
copy|paste special|values
format the cell as text.

Ok so far, right?

Now add a single character into that cell making it 256 characters.
You've got ######'s.

Now put this formula in another cell:
=rept("asdf ",204)
Convert it to values and format it as text (1020 characters)
You've got ####'s again.

Now edit that cell and add 4 characters (1024 total).
hit enter and you've still got ####'s.

Edit it once more and and add one single character (1025 total).
hit enter and, hey, you've got text.

1-255 characters work ok
256-1024 characters give ####'s
1025+ and you're ok again.

Tom Ogilvy guessed that it had something to do with early versions of excel that
only supported 255 characters in a cell and that maybe the 1024 character limit
for formulas (measured in R1C1 reference style).

It seems like a very reasonable explanation to me.

===
It's not an infrequent question in the newsgroup. But it's surprising when you
see it the first time. I know I was surprised.
 
You want weird....

Try putting text in a cell that has a length of exactly 255 characters.
I used: =rept("asdf ",51)
copy|paste special|values
format the cell as text.

Ok so far, right?

Now add a single character into that cell making it 256 characters.
You've got ######'s.

Now put this formula in another cell:
=rept("asdf ",204)
Convert it to values and format it as text (1020 characters)
You've got ####'s again.

Now edit that cell and add 4 characters (1024 total).
hit enter and you've still got ####'s.

Edit it once more and and add one single character (1025 total).
hit enter and, hey, you've got text.

1-255 characters work ok
256-1024 characters give ####'s
1025+ and you're ok again.

Tom Ogilvy guessed that it had something to do with early versions of excel that
only supported 255 characters in a cell and that maybe the 1024 character limit
for formulas (measured in R1C1 reference style).

It seems like a very reasonable explanation to me.

===
Click to expand...

So the bottom line is that it's semi-known wonkiness that isn't a
pressing issue. :)

Thanks for your help.


-D
 
Duncan Bachen said:
So the bottom line is that it's semi-known wonkiness that isn't a
pressing issue. :)

Thanks for your help.


-D
Click to expand...

Just ran into this situation myself, and was very pleasantly surprised to
find the answer in a newsgroup, given that the program help was useless.

Using Excel 2003 and I had a field with 302 characters of text that showed
the ####'s. I started trimming down the field and once I got it down to
just over 250 characters (sorry I didn't notice the exact number), it
displayed fine. Since I needed to show all 302 characters, I padded it with
trailing spaces to get up to 1025 characters and now it shows fine.

Sure would be nice if MS would fix this, or at least say something about it
in the help file!

John Schneider
 
I thought I had tried that during my testing, but evidently not. That works
fine!
 
Back
Top