Get Rid of Number Signs in Excel

Get Rid of Number Signs in Excel

In your early days of working in Excel, you probably saw the occasional cell full of number signs (you might call them pound signs or hash marks). Here’s what you can do to get rid of number signs in Excel.

Number Signs in Excel

Usually, you’ll see cells full of number signs if the column is too narrow to show the full number. It could be misleading if Excel displayed part of a number, so number signs are displayed instead, to alert you to the problem.

NumberSigns01

Widen the Column

To fix that problem, widen column C, so there is room for the full number. The quickest way to make the column the correct width is to double-click the line between the headings for columns C and D.

NumberSigns02

Text Cells Filled With Number Signs

What if the column is wide, and the cell contains text, not a number – and it’s full of number signs?

In the screen shot below, cell B2 is selected. In the formula bar, you can see the text from cell B2, and the letters are displayed correctly there.

NumberSigns03

However, instead of showing the text, cell B2 is filled with number signs, in a single row at the top of the cell.

  • Cell B2 is set for Wrap Text, but the number signs are not wrapping.
  • Cell B2 is formatted as Text, instead of Number or General.

Wider Column Doesn’t Help

There is a lot of text in that cell, but Excel should be able to handle up to 32,000 characters. In cell A2, there is a LEN formula: =LEN(B2)

The result of that formula shows that there are 1100 characters in cell B2. That is well under the Excel limit.

In this case, widening the column doesn’t fix the problem – you’ll just end up with a wider column of number signs.

Fix Text Cells With Number Signs

In this case, the problem is the cell’s Number format — it is set as Text.

To fix text cells that are showing number signs:

  • Set the cell format to General, instead of Text.

It’s a strange solution, because Text seems like the logical choice, when formatting a cell that contains text.

NumberSigns04

Here is the same cell, with General format.

NumberSigns05

The good news is that this problem seems to be fixed in Excel 2010.

______________