Why in some cells text appears as ### and in others as the text it

[Dimitris from Athens Greece]

In some cells, text appears as ###### and in other cells as the text itself.
I want to control, how the text appears. Does anybody knows how can I do it?
Thanks a lot in advance
Dimitris

 

[Sheeloo]

One reason could be...

In Excel 2003, a cell can hold a maximum of 255 characters...
If you enter more than that then you see #########...

 

[francis]

There are 2 reasons for this to appear
1. The length of your data is longer than the column width, adjust the width
2. There are negative time value in it.
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis

 

[Dave Peterson]

I'm guessing it's #3 for you.

And you can fix your formulas by reformatting the cell as General (anything but
Text) and then select the cell, hit F2, then enter. This makes excel think
you're reentering the formula.

Saved from a previous post.

It could mean a few things.

1. The columnwidth is too narrow to show the number.

Widen the column or change the font size of that cell. Or change the
numberformat to General.

2. You have a date/time in that cell and it's negative

Don't use negative dates. If excel was helping you, it may have
changed the format to a date. Change it back to General (or some
other number format).

If you need to see negative date/times:
Tools|options|Calculation Tab|and check 1904 date system
(but this can cause trouble--watch what happens to your dates
and watch what happens when you copy|paste dates to a different
workbook that doesn't use this setting)

3. You have a lot of text in the cell, the cell is formatted as Text.

Format the cell as general.

4. You really have ###'s in that cell.

Clean up that cell.

5. You have # in a cell, but it's format is set to Fill.

Change the format
(format|cells|alignment tab|horizontal box, change it to General.

 

[Dave Peterson]

xl2003 can hold about 32k characters in a cell.

xl95 was the last version with the 255 character limit.

 

[Shane Devenshire]

Hi,

It doesn't matter how long a text item is, it will not display as ###.

 

[Shane Devenshire]

Hi,

Even in 2000 with >1500 characters of text in a cell, (you can only see or
print about 1024 unless you use Alt+Enter) and with the cell formatted as
Text your display will not show ####.

 

[Dave Peterson]

That's not true in xl2003 and below (I think it was fixed in xl2007???).

If the cell is formatted as Text and the length of the string is between 255 and
1024, you'll see those ###'s.

 

[Dave Peterson]

The problem isn't with extremely long text. The problem occurs when the length
of the text is between 255 and 1024 (xl2003 and below--I think xl2007 fixed
this).

 

[Sheeloo]

Tested in Excel 2007 with the following formula
=REPT("A",A1)

It worked fine upto A1=32767 and gave a #VALUE error from 32768...

 

[Dave Peterson]

And if you tried:

=rept("a",300)
and converted to values
then formatted as text

What did you see?

In xl2003, I see ######'s.

Tested in Excel 2007 with the following formula
=REPT("A",A1)

It worked fine upto A1=32767 and gave a #VALUE error from 32768...

 

[Sheeloo]

##############

I see a string of #s from 257 onwards

:-(

 

[Dave Peterson]

Until you got to a length of 1025 I bet. Then it displays nicely.

##############

I see a string of #s from 257 onwards

:-(

 

[Sheeloo]

I am glad I am not a betting man... even if I was I would not bet against you


You are right...

Also I finally 'understood' why I see
=REPT("a",1026)
of the format is general and ######### if it is text

 

[Dave Peterson]

It turns out that this problem comes up quite often.

I am glad I am not a betting man... even if I was I would not bet against you


You are right...

Also I finally 'understood' why I see
=REPT("a",1026)
of the format is general and ######### if it is text

 

[Bob I]

In 2007, if you format the cell as text first, then load it with
characters, it will display ############, change format to General and
it shows up as the correct text.

 

[Dave Peterson]

I thought that I read that this was fixed in xl2007.

Thanks for the correction, Bob.

In 2007, if you format the cell as text first, then load it with
characters, it will display ############, change format to General and
it shows up as the correct text.

 

[Gord Dibben]

Dave

Not fixed, but maybe changed?

In 2003 the ########## showed up when string was between 255 and 1024 chars
and back to normal after that.

In 2007 the ######### show up from 255 to 32767 chars.


Gord

 

[Dave Peterson]

Maybe they/MS thought that if it happened more often, then it would be easier to
explain?????

<vbg>