Octothorpeia

January 22, 2008 3:45 PM

Excel 2003 question -- why do some of my cells show ####### in the contents?

I have a cell filled with some text. The cell format is set to Text. Some of the cells are showing ####### for no explainable reason.

Normally when I see a spreadsheet doing this, I assume there's a huge word that isn't fitting into the displayed cell, but all I have in there is regular words. No matter how far I drag out the column width, I just get an even longer #################### that won't go away. The "entry" window at the top correctly shows the text I have in there. I can't seem to find any options that deal with this problem.

FWIW I have "word wrap" turned off as I don't want the displayed cell to obscure other spreadsheet content. I just want to have a normal cell and just see the first few words displayed.

posted by crapmatic to computers & internet (26 comments total)

Is it a negative number formatted as a date?
posted by milkrate at 3:49 PM on January 22

Nevermind. But that's what it does with negative dates. Try I'd try other formats.
posted by milkrate at 3:52 PM on January 22

No, there are no numbers in this cell... it is just plain text.
posted by crapmatic at 3:52 PM on January 22

What happens when you Autofit the column? How much whitespace is after the text? (place the cursor at the end of the text in the entry window, press shift+end, then hit delete to clear the blue highlighted selection) Lastly, have you tried clearing out the field entirely and typing something new?
posted by samsara at 3:58 PM on January 22

For me, that always has to do with a formula gone bad. Is the first character an actual letter? (Not an equals sign, or anything possibly related to math?)
posted by iguanapolitico at 4:02 PM on January 22

That reminds me...for text make sure you preface it with a '
posted by samsara at 4:03 PM on January 22

My guess is that the column is not wide enough to show the whole contents of the cell.
posted by flabdablet at 4:05 PM on January 22 [1 favorite]

That happens if your cell is too small for what is in it often. so if you have a really long word in there, and the cell is very small, you get a bunch of #####s, just make the cell bigger and you'll be able to see what is in there.
posted by JonahBlack at 4:06 PM on January 22 [1 favorite]

Double-click the icon that you drag to auto-expand the column width.

Other than that, I'm stumped. Excel is buggy.
posted by puddleglum at 4:10 PM on January 22

Check for spaces at the end of your text (in this case lots of spaces) or as I like to call them the bane of vlookups.
posted by Octoparrot at 4:37 PM on January 22

Sometimes I put an apostrophe in front of the string when Excel's acting wonky. It forces Excel to treat a number like text. You shouldn't need that though.
posted by mullacc at 4:49 PM on January 22

flabdablet and jonah have it - your column's not wide enough.
posted by robinpME at 5:17 PM on January 22

This is how Excel cusses back at you.
Most likely, your cell is formatted weird and it doesn't know what to do with the information that you entered.
Go to Format> Cells...> Number and then click on the type of information you are trying to enter. If it is just text, try hitting General. Sometimes I have to click on Date or something random, then set it back to what it was supposed to be. I have also ran into the problem where I have to delete the entire row or column and then insert a new one just to be sure that I get out any formatting that I didn't intend.
posted by idiotfactory at 5:26 PM on January 22

If all the other cells around it are normal, use fast format to copy the formating to your wonky cell.
posted by saffry at 5:43 PM on January 22

Just widening the cell will solve this problem 99% of the time.
posted by bingo at 6:37 PM on January 22

If you're convinced that the item should fit into the column width, check the font size of the text in the cell. Reset it to 10 point or something.
posted by blue_wardrobe at 7:06 PM on January 22

I wish you people would read the question. OP has text, not numbers in there. OP has tried widening the column. If it started with an equal sign, it might come up with ### but that would resolve to #Name? on the increase of column width.

OP, I cannot replicate your problem on my spreadsheet. What is the exact text in that field? Feel free to email me your Excel spreadsheet so I can have a go at it.
posted by b33j at 7:52 PM on January 22 [1 favorite]

I'll verify the OP isn't insane; I've had this happen to me lots. I often seem to get it when I've pasted some text into a cell (as opposed to typing it directly). I've been watching this thread with interest, hoping somebody would know the answer instantly. I suspect that the "Excel is buggy" answer might be the best that we get.
posted by web-goddess at 8:26 PM on January 22

I'm also curious to have a crack at it. Email in profile.
posted by flabdablet at 11:41 PM on January 22

It might help if you can recreate the problem with some dummy data (assuming you wouldn't want to share your current spreadsheet) and post some pictures of it.
posted by concrete at 1:16 AM on January 23

I also encounter this problem, despite having formatted as text and despite having widened my column. I sometimes resort to taking the text out and pasting it back in a bit at a time until I find the characters that are freaking excel out, but I've never figured out the pattern. I hope someone here will know.
posted by daisyace at 4:33 AM on January 23

this happens to me a lot. don't know why. format the cell as "special" instead of date or number or text or whatever, and it shows up fine.
posted by misanthropicsarah at 7:18 AM on January 23

Try putting the formula =LEN(A1) into a cell formatted for numbers (with A1 replaced with the actual address) to get the size of the string. Then try = RIGHT(A1,10) to get the last ten characters or =TRIM(A1) to get the string without trailing blanks.
posted by DanSachs at 7:25 AM on January 23 [1 favorite]

i think flabdablet happens. this is what happens when there's a formula in a cell but there's not enough room to display the results. widen your columns.
posted by Soulbee at 10:13 AM on January 23

oh. should have previewed i guess. sorry.
posted by Soulbee at 10:14 AM on January 23

I've had this problem before. Here's how I think I fixed it.
1. Click on the cell with the #######
2. Click in the "entry" window where the correct text is displayed. Select all the text and copy. This copies just the text (no formatting or other funny stuff) to the clipboard.
3. Create a new blank Excel workbook.
4. Click in any cell in the blank document, click in the "entry" window and paste.
5. Hopefully your correct text shows up in the cell now. Click that cell and copy.
6. Click the cell with the ###### and paste.

It's really astonishing how many people said "just widen the column".
posted by Dec One at 12:27 PM on January 23