Strange phenomenon with General vs. Text formatting

[Bob]

1) Copy the following paragraph to cells A1 and A2 (be sure to remove the
line breaks that this web site adds):

Placing this project in yellow as Company ABC has NOT provided as yet, the
T&C1 to the MOM. Both have been overdue since mid-June. Escalations within
Company ABC and from Company XYZ leadership has not changed the status.
Meanwhile, Company ABC PM is reporting that INSTAR test support is an issue.
Sahni has escalated numerous times on both issues.

2) Format A1 with the General format; format A2 with the Text format.
Notice that A1 looks normal, but A2 shows “###########################â€

3) Now copy the following paragraph to cells A3 and A4 (again be sure to
remove the line breaks that this web site adds):

10/31 [tm0622] - Request for swBRD to be baselined via e-mail, because it is
late. 10/23 - swBRD - not baselined, T&C (1) to be delivered 7 days from
baselining.

4) Format A3 with the General format; format A4 with the Text format.
Notice that A1 and A2 both look normal!

Can anyone tell me what is going on here? Any help in solving this mystery
would be greatly appreciated.

Thanks,
Bob

 

[_]

Bob...probably have to fill-in all cells in the worksheet for a test-run so
some genius will solve the mystery today?

 

[tim m]

I get the same results as you when tested. After doing a quick web search, I
believe that perhaps you have exceeded a character limit per cell (255 I
think). Once i removed some characters from the longer paragraph it reverted
to showing the text rather than the #'s If you add a bunch of chracters to
your shorter paragraph it to will display #'s once it reaches a certain
length.

 

[Dave Peterson]

The problem (###'s) occur when the cell is formatted as text and the length of
the string in that cell is between 255 and 1024 characters.

Anything outside those will show ok.

1) Copy the following paragraph to cells A1 and A2 (be sure to remove the
line breaks that this web site adds):

Placing this project in yellow as Company ABC has NOT provided as yet, the
T&C1 to the MOM. Both have been overdue since mid-June. Escalations within
Company ABC and from Company XYZ leadership has not changed the status.
Meanwhile, Company ABC PM is reporting that INSTAR test support is an issue.
Sahni has escalated numerous times on both issues.

2) Format A1 with the General format; format A2 with the Text format.
Notice that A1 looks normal, but A2 shows “###########################â€

3) Now copy the following paragraph to cells A3 and A4 (again be sure to
remove the line breaks that this web site adds):

10/31 [tm0622] - Request for swBRD to be baselined via e-mail, because it is
late. 10/23 - swBRD - not baselined, T&C (1) to be delivered 7 days from
baselining.

4) Format A3 with the General format; format A4 with the Text format.
Notice that A1 and A2 both look normal!

Can anyone tell me what is going on here? Any help in solving this mystery
would be greatly appreciated.

Thanks,
Bob

 

[Bob]

Tim,
Yes, the first "paragraph" is greater than 255 characters (it's actually 323
characters long). So why does it display okay when the cell is formatted as
General, but not okay when formatted as Text? I would have thought that I
would get "#############" regardless of the format.
Bob

I get the same results as you when tested. After doing a quick web search, I
believe that perhaps you have exceeded a character limit per cell (255 I
think). Once i removed some characters from the longer paragraph it reverted
to showing the text rather than the #'s If you add a bunch of chracters to
your shorter paragraph it to will display #'s once it reaches a certain
length.

1) Copy the following paragraph to cells A1 and A2 (be sure to remove the
line breaks that this web site adds):

Placing this project in yellow as Company ABC has NOT provided as yet, the
T&C1 to the MOM. Both have been overdue since mid-June. Escalations within
Company ABC and from Company XYZ leadership has not changed the status.
Meanwhile, Company ABC PM is reporting that INSTAR test support is an issue.
Sahni has escalated numerous times on both issues.

2) Format A1 with the General format; format A2 with the Text format.
Notice that A1 looks normal, but A2 shows “###########################â€

3) Now copy the following paragraph to cells A3 and A4 (again be sure to
remove the line breaks that this web site adds):

10/31 [tm0622] - Request for swBRD to be baselined via e-mail, because it is
late. 10/23 - swBRD - not baselined, T&C (1) to be delivered 7 days from
baselining.

4) Format A3 with the General format; format A4 with the Text format.
Notice that A1 and A2 both look normal!

Can anyone tell me what is going on here? Any help in solving this mystery
would be greatly appreciated.

Thanks,
Bob

 

[Bob]

Dave,
Thanks for the info! Can you tell me where this info is documented in
Excel's Help file? I'm trying to figure out what is significant about a
string being greater than 255 characters, but less than 1024 (from a
formatting perspective)?
Thanks again,
Bob

The problem (###'s) occur when the cell is formatted as text and the length of
the string in that cell is between 255 and 1024 characters.

Anything outside those will show ok.

1) Copy the following paragraph to cells A1 and A2 (be sure to remove the
line breaks that this web site adds):

Placing this project in yellow as Company ABC has NOT provided as yet, the
T&C1 to the MOM. Both have been overdue since mid-June. Escalations within
Company ABC and from Company XYZ leadership has not changed the status.
Meanwhile, Company ABC PM is reporting that INSTAR test support is an issue.
Sahni has escalated numerous times on both issues.

2) Format A1 with the General format; format A2 with the Text format.
Notice that A1 looks normal, but A2 shows “###########################â€Â

3) Now copy the following paragraph to cells A3 and A4 (again be sure to
remove the line breaks that this web site adds):

10/31 [tm0622] - Request for swBRD to be baselined via e-mail, because it is
late. 10/23 - swBRD - not baselined, T&C (1) to be delivered 7 days from
baselining.

4) Format A3 with the General format; format A4 with the Text format.
Notice that A1 and A2 both look normal!

Can anyone tell me what is going on here? Any help in solving this mystery
would be greatly appreciated.

Thanks,
Bob

 

[Dave Peterson]

I've never seen it in the help file.

And the guess that makes the most sense to me came from Tom Ogilvy. At one
time, the largest number of characters you could put in a cell was 255 (or
256???). When that was increased, someone missed updating somewhere that would
avoid the trouble.

And the 1024 number could be a result of the maximum length of a formula (in
R1C1 reference style).

Dave,
Thanks for the info! Can you tell me where this info is documented in
Excel's Help file? I'm trying to figure out what is significant about a
string being greater than 255 characters, but less than 1024 (from a
formatting perspective)?
Thanks again,
Bob

The problem (###'s) occur when the cell is formatted as text and the length of
the string in that cell is between 255 and 1024 characters.

Anything outside those will show ok.

1) Copy the following paragraph to cells A1 and A2 (be sure to remove the
line breaks that this web site adds):

Placing this project in yellow as Company ABC has NOT provided as yet, the
T&C1 to the MOM. Both have been overdue since mid-June. Escalations within
Company ABC and from Company XYZ leadership has not changed the status.
Meanwhile, Company ABC PM is reporting that INSTAR test support is an issue.
Sahni has escalated numerous times on both issues.

2) Format A1 with the General format; format A2 with the Text format.
Notice that A1 looks normal, but A2 shows “###########################â€Â

3) Now copy the following paragraph to cells A3 and A4 (again be sure to
remove the line breaks that this web site adds):

10/31 [tm0622] - Request for swBRD to be baselined via e-mail, because it is
late. 10/23 - swBRD - not baselined, T&C (1) to be delivered 7 days from
baselining.

4) Format A3 with the General format; format A4 with the Text format.
Notice that A1 and A2 both look normal!

Can anyone tell me what is going on here? Any help in solving this mystery
would be greatly appreciated.

Thanks,
Bob