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.

______________

20 thoughts on “Get Rid of Number Signs in Excel”

  1. Thank you so much! This problem has been very frustrating and now, using your advice, it is fixed. Thanks, again!

  2. I seem to have a slightly different issue. The cell is set for the Date format. The entry field at the top shows the date (1/22/1921) but the cell shows the generalized number (7693). If I change the format of the cell to General, the cell entry remains unchanged but now the entry field at the top of the screen shows 7693. What is bothersome is that this only occurs with certain cells in a long table, and I cannot find any difference between those cells and the others.

  3. My problem is that when I open the excel file I see numbers not #. When others open my files, they see #. Is there something I can do. It’s awkward when I send a file to my manager and he can’t read the numbers without reformatting it.

  4. I am having the same problem. I see the text data fine, I send the excel file to a friend and when she opens it some (not, and not many) cells are full of hash tags. If she puts her cursor on the hash tags it shows her what the data is. In our situation I am using a Mac and she is using a windows Microsoft computer, any suggestions?

  5. Deb, I am extremely new to Excel, I have Excel 2016. I am trying to set up a financial sheet for our Veterans Of Foreign Wars Auxiliary. I have my header done, each of my individual columns set up and label. I have it set on general. I typed in my beginning balances, my first row I typed in the amount and it took it fine, there is plenty of room in the column for the figures but when I click on the next column to enter figures my first column turns to pound signs. Any Help is greatly appreciated. I have never worked with Excel so am totally lost. Also trying to add auto sum??

    1. @Sandi, What happens if you make the first column twice as wide as it is now? Does that fix the problem?
      If you point to the cell with the pound signs, does a little box appear, showing the number that is in the cell?
      What format is in the cell? To find out, click on a cell with pound signs, then look on the Home tab of the Ribbon. You should see a Number section, with a word in it, such as “General” or “Number”

  6. Can anyone tell me how I can turn off the(C)or(R)signs that default to their company or registration signs please?

    1. @Maritta, in Excel, click the File tab, at the top left, then click Options.
      Click the Proofing category, then click the AutoCorrect Option button near the top right
      In the list of AutoCorrect items, click on (c) then click Delete
      Do the same for (r)
      Click OK twice, to exit.

  7. I have a different issue, mine is that the first left side of a number in a column contains only one # sign, the person that sent it to me did it because they did not want excel to think it was a mat formula so all I want it is the cells to be date format mm/you but when I do try to convert it it does not take away the # and if I do the text to column then remove the preceding # it will change the dates to 9/21/1904 ?

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.