I saw this question in Twitter this week:
Anyone know a way to get an Excel cell to take over 255 characters w/o converting to pound signs ###? Wreaking havoc on something I’m trying
Cell Full of Pound Signs
First, what do you call those symbols – ###?
- Pound signs? Hashtags? Number signs? Octothorpes?
Anyway, whatever you call them, in the screenshot below, you can see an example of this 255 characters problem, in cell B2.
Identical Cell Content
Here’s what’s on the worksheet, shown above:
- Cells B2 and B3 both contain an identical long string of characters.
- In column A, a LEN formula calculates the count of characters in B2 and B3
- The formula results in cells A2 and A3 are exactly the same:
- There are 2695 characters in each cell
Why do the cell strings behave differently though?
- In cell B3 the long text overflows into the next column
- In cell B2 only the pound signs are visible.
Long Numbers
Aside from long text strings, there is another reason that causes pound signs to appear in a cell.
Those pound signs appear if a number is too long to display in a cell, and widening the column would fix that problem.
Fix the Long Text Problem
However, in this case the cells contain text, not numbers. Widening the column won’t help.
In example shown above, cell B2 is formatted as Text, and that’s what is causing the long text string problem.
There is a limit to what can show in a cell formatted as Text.
Change Cell Number Format
To fix this problem, you can change the cell’s format to General.
To change the format, follow these steps:
- Select cell B2, and on the Ribbon, click the Home tab
- In the Number group, click the drop-down arrow for Number format
- Click on General.
- Note: If you look down the list of formats you’ll see that Accounting format and Text format show pound signs, instead of the sample text.
After you change the format, the long text string will overflow into the adjoining cell on the right.
____________________
This is awesome tips … thanks so much
I second Dinh’s thanks – many thanks!
Thanks!!
Solved my problem. Thanks a lot!
Okay, seriously, if I could kiss you right now I would. MS website was no help at all. Thanks!!!
Thank you so much! I was pulling my hair out working on a customer survey with text in the cells, and it just didn’t make sense.
You’re welcome! Glad the tip helped you with your customer survey.
Thank you so much for the help!!
It worked – Thank you!
Thank you!!! You saved my day!
You’re welcome! I’m glad the tutorial helped you.
Thank u.. Its Fixed.. Thank u…
Thank you! Thank you! Thank you! I was trying all kinds of things until I googled “Excel cell ###” and found your blog.
Thanks for tip! It helped me.
Thank you!!! I was going nuts trying to fix the ####!!
Would Have Taken Me 3 Days to Figure Out! Thank you so much!
Do you have any suggestions for when the General data type also shows ##### instead of my text string? I’ve tried toggling back and forth between Text and General and it’s still #### in the cell.
Jenny, try adding a few lines breaks in the cell (press Alt + Enter)
Thanks heaps !!! save a lot of time