Why doesn't what I enter in the function bar appear in the cell?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris Mitchell - 18 Nov 2007 21:45 GMT

I have a workbook that contains several copies of a master template
worksheet.

On one of these sheets when I enter text, it appears correctly in the
function bar, but appears as a string of &s in the cell. This has only just
occurred, and as far as I know used to work OK, i.e. it used to appear as
text in the cell.

What could cause this and what do I need to do to put it right?

Reply to this Message

Dave Peterson - 18 Nov 2007 22:57 GMT

You wrote that the text appears as apersands (&). Did you really mean
octothorpes (#)?

If yes, then try formatting that cell as General--or anything but Text.

> I have a workbook that contains several copies of a master template
> worksheet.
[quoted text clipped - 5 lines]
>
> What could cause this and what do I need to do to put it right?

Signature

Dave Peterson

Reply to this Message

Chris Mitchell - 19 Nov 2007 05:47 GMT

You might be right about # vs &, the file is on a different PC, so I can't
check at the mo. However I did look at this before posting, and other cells
in the same worksheet are formatted as text and everything is OK.

How/why might odd cells behave differently when they have the same
formatting?

> You wrote that the text appears as apersands (&). Did you really mean
> octothorpes (#)?
[quoted text clipped - 11 lines]
>>
>> What could cause this and what do I need to do to put it right?

Reply to this Message

Dave Peterson - 19 Nov 2007 12:20 GMT

Excel has trouble displaying the string in a cell formatted as text when the
length of that string is between 256 and 1024.

So I bet that the other cells that appear ok don't have strings where the
lengths are between 256 and 1024.

> You might be right about # vs &, the file is on a different PC, so I can't
> check at the mo. However I did look at this before posting, and other cells
[quoted text clipped - 22 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Chris Mitchell - 19 Nov 2007 14:05 GMT

Thanks Dave.

Spot on with the reformatting suggestion.

FYI one of the two affected cells only had 215 characters, but the other had
over 400. These are the longest strings in the worksheet.

I'm about to make a global change to avoid this happening in the future.

Thanks again.

> Excel has trouble displaying the string in a cell formatted as text when
> the
[quoted text clipped - 33 lines]
>> >
>> > Dave Peterson

Reply to this Message