Contextures Blog

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:

1. Select cell B2, and on the Ribbon, click the Home tab
2. In the Number group, click the drop-down arrow for Number format
3. 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.

____________________