Cell displays ################# instead of the text

[Rahul]

Hi,

I have run into an issue that when the text in the cell is greater than a
certain number of characters, it is displayed as "#####################". If
I change the cell type to "General" instead of "Text" then it displays fine.
How can I fix this so that the type can be "Text" and still display
properly.

Thanks
Rahul

 

[Dave Peterson]

Try changing the cell's format from Text to General (or anything but text).

 

[Andrew Rossmann]

Try changing the cell's format from Text to General (or anything but text).

Problem is, General appears to truncate text that is longer than about
256 characters or so.

 

[Peo Sjoblom]

I have never seen that?

 

[Rahul]

That works but I am populating a list programatically and if there is data
that is more than 256 and less that 1024 characters then the list looses any
formatting that the user may have done

 

[Gord Dibben]

Then it is up to you to programatically format the cells to General when you
populate.

If you need help with that post the code you are currently using to populate the
cells.


Gord Dibben MS Excel MVP

That works but I am populating a list programatically and if there is data
that is more than 256 and less that 1024 characters then the list looses any
formatting that the user may have done

 

[Dave Peterson]

I've seen it seem to truncate characters when you get close to 1000
characters--but not at 255.

The workaround for that is to put alt-enters every 80-100 characters.

 

[Dave Peterson]

What kind of formatting is lost?

That works but I am populating a list programatically and if there is data
that is more than 256 and less that 1024 characters then the list looses any
formatting that the user may have done

 

[Andrew Rossmann]

I've seen it seem to truncate characters when you get close to 1000
characters--but not at 255.

The workaround for that is to put alt-enters every 80-100 characters.

Another issue is that it can mangle numbers. I often read in Bill-Of-
Material style data that can contain part numbers that are 12-digit
numbers:
232270412345
Using General, it displays in exponential format:
2.3227E+11
Worse, if I export in some formats, it outputs as exponential, making
it useless as I lose resolution. Reading the above will give:
232270000000
Also, part numbers may have one or more leading zeroes, which are
dropped and completely lost.

 

[Dave Peterson]

If you keep the format the cell General, excel will do what it wants--including
scientific notation.

But you have alternatives...

#1. Give the cell a Number format (with 0 decimal places, if you want)
#2. Give the cell a custom number format like: 000000000000
(12 digits with leading 0's)
#3. Treat the cell as text
a. Precede your entry with an apostrophe: '001234
b. Preformat your cell as Text

Both #3 options will treat your entry as text. Arithmetic functions may not
work the way you want.

And if you save your file as a text file (.txt or .csv), you'll see the leading
0's in the text file (using notepad), but you'll have to be careful if you
reopen that text file in excel.

 

[Andrew Rossmann]

If you keep the format the cell General, excel will do what it wants--including
scientific notation.

But you have alternatives...

#1. Give the cell a Number format (with 0 decimal places, if you want)
#2. Give the cell a custom number format like: 000000000000
(12 digits with leading 0's)
#3. Treat the cell as text
a. Precede your entry with an apostrophe: '001234
b. Preformat your cell as Text

Both #3 options will treat your entry as text. Arithmetic functions may not
work the way you want.

And if you save your file as a text file (.txt or .csv), you'll see the leading
0's in the text file (using notepad), but you'll have to be careful if you
reopen that text file in excel.

That is the main issue I tend to have. I often import text or CSV files
generated by others. I've set up Excel to always confirm import
conversions, and generally set all columns to text. I can always change
it if needed. That manufacturer part number can be many types. Not just
12NC, but various forms of text, too.

The other problem I have is getting binary Excel files, but somebody
imported without setting the proper import settings, leaving me with a
mess.

 

[Dave Peterson]

I think everyone can feel your pain.

Our part numbers were up to 2 characters, hyphen, 6 numbers, hyphen, up to 4
characters.

Some customers/vendors would jam all the characters together, dropping the
hyphens and losing the leading spaces/0's.

My eyeballs would hurt for weeks when trying to compare/contrast part numbers.