2005.01.06 02:03 AM

Excel #'s Text

If you find yourself in Excel staring at a long string of #'s in a cell you know contains only text (as opposed to formatted data like dates, or numbers, etc., where #'s are a way of life in too-narrow columns), and you've already gone to the trouble of setting the cell to wrap its contents and/or stretched it's column to an unimaginable width, it's probably because the cell you're staring at contains more than 255 characters and its format is set to Text.

Apparently, when a cell's format is set to Text, the cell can be filled with, but cannot display, more than 255 characters, wrapped or not, wide enough or not.

To remedy this, simply set the cell's format to General.

Interestingly, this rule isn't always so straightforward. It seems that if a cell's format is set to Text before it is filled with data, then it will always abide by the 255 character display limit. You can switch its format to General and see its contents, but if you ever switch its format back to Text while the cell contains more than 255 characters, you'll see #'s. However, under some circumstances, if the cell's format is set to General before it is filled with more than 255 characters, you can switch the cell's format to Text and continue to see the data. Unfortunately, even as I sit here staring at a cell now formatted as Text (originally formatted as General) that is properly displaying 17,788 wrapped characters (well, a lot of them anyway), I can't figure out how I did it, and I can't seem to reproduce it in any other cell. Oh well.


Comments

Nice post.

Darren Neimke | 2005.01.06 01:28 PM

We have currency format for a cell in the excel sheet. We have been trying to set autoFitWidth for a cell but no such feature is available. We cant set the width in the xsl as it will be reflected for all the cells in that column. We just want to increase the width in case the characters exceed the cell boundaries. Is there any such provision.

Priya | 2005.02.21 09:47 PM

Hi Priya,

I think I understand what you're asking, but the answer is too long to show here. I am writing a new post that describes how to do this and will post it later today.

ewbi.develops | 2005.02.21 11:48 PM

Oops, never added a link to the new post for Priya:

http://ewbi.blogs.com/develops/2005/02/autofit_an_exce.html

ewbi.develops | 2005.04.05 08:15 PM

Should have read the Excel help. If I had, I would have seen that Excel defines a max number of characters across (width) for cells of 255. We know from experience that formatted cell contents show #s when too wide to fit a cell. We also know that applying the "Text" format to a cell means it is formatted. This means that cells formatted as "Text" containing more content than can be displayed within a 255 character max width will show #s.

ewbi.develops | 2005.07.13 02:54 PM

You're not going mad. A collegue and I found that #'s are displayed on a cell with a formatting of Text where the length is between 255 and 1024 (exclusive).

That is to say, up to 255 characters is fine, and after 1024 is fine.

Just another quirky Excel bug!

Carl Scarlett | 2005.08.09 07:28 PM

That might explain some things. I'll give it a closer look. Thanks!

Btw, I'm not sure this means I'm not going mad. ;p

ewbi.develops | 2005.08.09 08:06 PM

THANK YOU!!! This drove me nuts! There's nothing in Excel help or on microsoft.com about this little issue. Thanks so much!
Lisa

Lisa | 2005.09.02 09:07 AM

man, this is just a testament to the importance of blogs. been hunting for an explanation for 10 min. and finally found yours! thanks a bunch for posting this!

matt | 2006.02.25 01:35 AM

Thanks for a great post. I had tried everything I could think of to make a line of text display as something other than ######.

dooz | 2006.03.06 06:30 AM

Also worth mentioning.

If you copy a cell (or more) with data exceeding 255 chr's from one work-book to another where the workbooks are in different Excel sessions[click on the excel shorcut instead of "New Document" or "Open..." within an existing session], the value are truncated to 255 characters. Experienced this once in the painfull way.
(same for copy to other aplications, ie TextPad)

Created a macro to handle the outra galactic copying using clip-board , - but it's a mess.

Copying a Text formatted cell with value length between 255 and 1024 (when the ###.. are displayed) to ie TextPad result in #########... in TextPad.

---

Now i use MySQL + div languages instead.. heh...

Vindir | 2006.03.09 07:31 AM

Vindir - Good points. Thanks!

ewbi.develops | 2006.03.09 08:22 AM

Dooz - your "Also worth mentioning"s are a life saver :)

Chris.

youcantryreachingme | 2006.05.01 08:54 PM

(make that vindir; sorry dooz - didn't realise that names are attached to the end of posts :)

youcantryreachingme | 2006.05.01 08:56 PM

Very useful and precise info- thanks. Have been struggling to get some data out of an Excel spreadsheet and into mysql and was aware that there was a display problem when a cell contained > 255 chars. Was gobsmacked to discover that export to CSV EXPORTS the infernal ###### as well. Will now reformat to general and try again.

mnemosyne | 2006.07.10 08:15 AM

Thank very much for this insight, i was exhausted cursing excel....

Liviu | 2006.07.25 05:22 AM

Thanks--no M$ help on this and it was exactly what was wrong with my sheet.

Andy Bruce | 2006.07.25 10:32 AM

This posting was infinitely more useful than Microsoft's own help system (for Excel 2003) at troubleshooting and resolving the issue.

Jonathan Dubman | 2007.02.13 12:42 AM

THANK YOU! You're a savior.

VC | 2007.02.21 07:05 AM

Thanks for this. Finally found an answer here after 15 minutes with the useless excel help.

TJ | 2007.02.27 06:06 AM

Had to deal with a lot of text that needed to be lower case and punctuation free. Hit many of the problems noted above, plus one that wasn't noted: global search and replace (upper case A to lower case A) choked on some of the entries. The solution that finally worked most easily for me was:

1) Excel was all General cells
2) Copied the text to be converted to clipboard
3) Pasted into word - all steps until Excel is mentioned again were done in word
4) Converted table to text with paragraph break delimiters.
5) Converted all upper to lower using automatic Word format option
6) Removed punctuation appropriately
7) Copied back into Excel via clipboard.

Then I was able to save my stuff as a .csv file from Excel without problems.

By the way, one other HUGE problem I had with Excel: did a bunch of work, got interupted for a question, clicked on a .csv file to open it (instead of using open with notepad), saw it was in Excel, closed Excel on the .CSV file, and it closed all my work that morning without saving because it defaults to opening with the same instance but does NOT check all open files for changes when closing, just the active window.

No, I am not a fan of Excel.

Richard | 2007.06.01 01:11 PM

Richard, thanks for the tip. And for sharing your pain, we've all been there. :)

ewbi.develops | 2007.06.01 01:38 PM

Thank God for your perseverance in diagnosing this bug!!!

Clarence | 2007.06.13 04:07 PM

Thanks for this article. Didn't realise that it was the "text" setting that caused it.

Derrick | 2007.07.03 01:43 AM

I love archived stuff. Great post. You saved me a lot of money on ulcer treatment :D.

Cz.Dave | 2007.08.20 08:55 AM

Because of this post, I learned about the 255 limit of the text cell. MUCH THANKS! My problem was opening a delimited file with some cells exceeding this limit. Here are what I did to diplay all items correctly.

1. Open file as delimited.
Using the Text Import Wizard:
3.1 Tick delimited (NOT fixed width)
3.2 Choose the appropriate delimiter.
3.3 For EACH (in anticipation of strings/texts possibly exceeding the limit) column's data format, tick TEXT (NOT general)
2. Once file is opened, even if you wrap the text, you will notice "######" in cells that exceed the 255 limit. Select all (CTRL+A), right-click on the mouse and choose FORMAT CELLS... Then, on the NUMBER TAB, click on GENERAL this time.
3. All LONG texts may now be completey viewed. Cells may be adjusted as preferred to accommodate all characters.

Jen | 2007.08.30 04:15 AM

I had the same problem and changed the format in Excel to "Special" and that worked.

Barry | 2007.11.15 08:25 AM

That helped me a lot. Thanks!

Mike | 2007.12.05 01:52 PM

Thanks for this - moust useful!

Edward | 2008.02.06 02:34 AM

Thanks for this - most useful!

Edward | 2008.02.06 02:34 AM

Saved me alot of time! There were ###'s in my csv file. Thanks.

Amy | 2008.03.21 02:19 PM

Thank you!

Johan You | 2008.04.02 12:23 AM

Phew! Fabulous fix to MS's dumb export of ###### into the CSV file. I say "#### Microsoft!" Thanks.

Stephen | 2008.04.15 01:27 AM

Thank you. Saved me a lot of time. I was wondering what was going on as I had longer text displaying fine and shorter text displaying #s.

Mohan | 2008.04.21 06:54 PM

"Great Stuff!"

david | 2008.04.25 04:12 AM

Ha -- this is a blog post that keeps on giving! I've just had the same problem with the ## lines. The "general" setting is a bit painful if you are trying to simulate bullets in your cells by using alt-enters and the "-" sign. Excel interprets it as a formula and returns the "#name?" error.

*sigh* ... what an odd bug :(

case | 2008.06.17 03:29 PM

Just want to add my appreciation for this post. I can go home now. Thanks!

Cate | 2008.06.23 03:38 PM

Man, you just saved my life on my VBA project!
Thanks heaps mate.

Lobbie | 2008.07.03 02:41 AM

Saved my skin!

Douglas | 2008.08.01 07:15 AM

Thanks! Why is this the only place i could find that gave me a solution?!

barnee | 2008.08.06 08:22 AM

I spent an hour trying to figure this out. Excel help is an oxymoron. THANK YOU! My blood pressure is starting to go down now. I can finish this project...

jamie | 2008.08.06 02:01 PM

Thank you so much :)

ephi | 2008.08.16 12:10 AM

Fantastic. I have created an app that was having major problems because I could not print all 1024 characters. Who would have known. Thanks so much.

Ted | 2008.08.18 04:54 PM

Good stuff. Cannot believe such a simple, fundamental bug survived that long.

chaintzean | 2008.08.27 07:40 AM

Thank you! I've been using Excel for years. Never saw this problem in my Mac version but encountered it often in Windows version. Darn maddening. Thanks for the easy fix. Even the MS Help website didn't provide this info!

billsgate | 2008.09.16 01:37 PM

Ditto on the thanks for solving a problem I looked at for some time this morning. Onward and upward now. M$ owes you some tech-support pay.

Jonathan Murray | 2008.10.29 08:03 AM

Like to add that this is also a problem with Filters - any string longer than 255 characters will not appear in the AutoFilter drop-down!

Ric Shumack | 2008.10.30 10:01 PM

Thanks!!!! Excel's help is glaringly poor, given that this is probably a very common error that people make.

Dan400Man | 2008.10.31 01:35 PM

I've been looking for answer to the original question posed here for over a year. Thanks for a concise and helpful answer.

Dana | 2008.11.05 10:07 AM

Thanks for your clear post. Very helpful!

Greg | 2008.11.28 09:03 AM

Righteous post

Scott | 2008.12.03 07:46 PM

Thanks a lot for giving the right solution.

rafeeque | 2009.01.06 12:33 AM

thanks!

lynn | 2009.01.28 08:51 AM

Thanks for the help! Clear and easy post.

Matt | 2009.03.18 03:12 AM

Thanks a lot for this post! Excel does try to drive ppl mad from time to time ;)

Kavin | 2009.03.24 06:56 AM

Thanks! You saved me at least 20 minutes of looking through help files.

Adam | 2009.03.26 04:10 PM

That "Special" format made it for me, thx a million guys.

Mr.Swede | 2009.03.31 11:15 PM

Thanks a lot, great help

Rob | 2009.04.29 03:54 PM

Thank you. Right info at a critical time...

Ram Srinivasan | 2009.06.29 08:01 AM

See http://support.microsoft.com/kb/189897

Registry setting TypeGuessRows is defaulted to 8, so only the first 8 rows are examined and the largest sized cell per column is remembered. If you put text > 255 characters in the first 8 rows things will work. Otherwise, read the KB article and change the TypeGuessRows setting, and see if that does not solve this issue.
Good Luck.

Rick Powell | 2009.07.16 12:59 PM

Thank You! This is a helpful post. Thanks for sharing.

Nuts with ### on text | 2009.07.21 01:44 PM

Thank you! I found it incredibly useful as well!

Ryan | 2009.09.11 01:24 PM

Thank you!!!! This was really helpful!

solnori | 2009.09.30 03:40 PM

I looked in far too many places to find the answer to this. You are a golden god! I hope you don't mind if I sent a comment to MS with your URL telling them that they need to add this to their help.

Very Thankful Brian | 2009.11.02 01:24 PM

Ha! Golden God, at your service. :)

ewbi.develops | 2009.11.02 02:50 PM

Add me to the list of folks grateful for the original post and the persistence of older, pertinent information on the 'net.
You rock!

anotherThankfulReader | 2009.11.11 03:06 PM

This is a great post, thanks.
I just had a coworker come to me and ask about a similar problem with displaying ####### in Excel 2007. Switching to General in the format didn't even work, the only solution was switching the Currency even though it was text in the cell. Looks like the bugs got even weirder in the new version.

Richard | 2010.02.11 08:49 AM

This post helped. Thank you.

Travis | 2010.05.14 05:55 AM

Thanks so much for saving me time and frustration!

Aksunai | 2010.06.21 12:11 PM

First of all thanks for your help. Although I did not spend more than a minute before arriving on the solution (from your blog offcourse), I can understand the pain of not being able to find answers to problems like these. I read through some of the comments, people are really happy with your post since it has helped a lot of them. Your blog's so famous now that it appeared as the first result when I searched for replacing ### with text :) Keep it up!

Prabhjot | 2010.06.22 12:00 PM

Thanks, Prabhjot. I appreciate your comment.

ewbi.develops | 2010.06.22 12:05 PM

Perfect answer. Thank you!

K | 2010.08.09 05:07 PM

Perfect answer. Thank you!

K | 2010.08.09 05:07 PM

Thank you! Found your post via google after a frustrating half hour trying to fix this myself, then searching for a fix or reason why. Microsoft take note!

Paul | 2010.09.13 09:44 AM

Good blog!

handsome | 2010.11.10 02:49 AM

The help that keeps on helping, even five years later! Thanks; saved me time & grief.

Kelly | 2011.01.19 08:16 AM

Thanks!!! You just saved my client team a lot of trouble reformatting my automated excel reports. This is a crazy bug...great blog that keeps giving the love!

Curtis | 2011.01.20 02:37 PM

Thanks - brilliant help

Frank | 2011.06.02 06:21 AM

Thank You! So glad this lives on! I have VB code that puts text in cell then if > 255 chrs it splits the text into sections of 255 chrs max (at ends of sentences) and places sections of text into cells below, until all the text has been distributed into cells (to prevent text truncation to 255 chrs when sheet copied). This worked fine until I increased the MaxLength property of the TextBox in form where data was entered from 1000 chrs to 3000 chrs. VB ground to a halt! Now I can see that I should hold the text in a variable in VB, process from there and only transfer chunks of 255 chrs or less into cells!

King | 2011.06.22 09:39 AM

Finally! The solution to my problem! Thanks a lot!

Dave | 2011.10.12 01:04 AM

Thanks so much for this. I knew it applied to numbers but had never seen it happen for text before. My report is now able to be read which makes it useful :)

Susan Toogood | 2011.10.12 01:20 PM

Great solution, thanks

Sunil | 2011.11.22 12:35 AM

I hate Microsoft.... really - a 255 character limit harkens back to the days when we were constrained by memory and forced to use char* everywhere and allocate our memory directly. In 2007, we're well beyond that and the average user is going to be mystified at the 255 character limit... another great example of retarded design.

Andy | 2011.12.06 08:13 AM

Thanks, it quick to figure out after reading your help!

anjie | 2011.12.20 12:42 AM

Thanks!
You solved my problem

Guest | 2012.01.08 10:32 AM

Thank you! Had this problem before as well and drove me nuts...this time i was determined to find the answer!

Michael | 2012.02.18 05:40 PM

Thank you - that solved my problem and reiterated my hate of all things Microsoft ... what a stupid way to code!

Mah-Jong | 2012.06.12 07:22 AM

Thank you so much!!!!!!!

Eilyn | 2012.06.21 08:00 AM

Thanks great post!! - saved me a lot of time

a | 2012.08.02 08:52 PM

Adding my THANKS to the long list !!!

Guest | 2013.01.24 06:58 PM



Post a Comment

 
  (optional)
  (no html)
 
   


TrackBack

TrackBack URL:  http://www.typepad.com/services/trackback/6a00d8341c7bd453ef00d83434f1ba53ef

Listed below are links to weblogs that reference Excel #'s Text: