Why does it display #### in my cell?

  • Thread starter Thread starter Lindsey-Star
  • Start date Start date
L

Lindsey-Star

In cell I25 I have the following formula: =SUM(I11:I14)
All it displays is #####, but when I hover over it, it gives me the correct
answer on a popup tip-like window which is $3,400. So, if it knows the
answer, why is it displaying with the pound signs instead? I think I've
tried everything.
 
Because you haven't given it enough room to display the result. Widen the
column, or use a smaller font.

Regards,
Fred
 
In my opinion, the hash marks are a mistake made by Microsoft. For normal
text, when you enter a string that is larger than the cell can display it
just runs over the cells next to it.

Display-wize, it's not a big deal to me because it indicates that my column
width is too small.

However, it is a huge deal to me because when a macro reads the contents of
the same cell, ##### is returned instead of the proper value.
 
Depends upon location Bob.

In Canada # is known as a pound sign.

As in "For more options please press a number key followed by the pound
sign"


Gord
 
Just for fun...
On a worksheet ...
Insert > Symbol, (under Basic Latin) the # has the name "Number Sign"

= = = = =
 
... when a macro reads the contents
of the same cell, ##### is returned instead of the proper value.
Click to expand...

Hmmm. Not sure what you have set up. Is there anything here that will
help?

Sub Demo()
Range("A1").Value = 12345678901234#
Columns("A:A").ColumnWidth = 2 'Narrow to display ##
Debug.Print Range("A1").Text
Debug.Print Range("A1").Value
End Sub

Returns:
##
12345678901234

= = = = =
Dana DeLouis
 
Right.........number or pound sign.

"hash" is what my mother used to make from leftover roast beef and potatoes.


Gord
 
Right.........number or pound sign.

The proper name of the # character is 'octothorpe'.
"hash" is what my mother used to make from leftover roast beef and potatoes.
Click to expand...

I consumed a lot of hash in my college days, but it wasn't the kind my
Mom made.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
 
Bob Phillips said:
Those are hash signs not pound signs, a pound is £
Click to expand...

According to Webster's Ninth New Collegiate Dictionary (1991), in the US,
"#" is used for number and for pounds (weight). And yes, £ is used for
pounds (currency).


----- original message -----
 
Joe User said:
According to Webster's Ninth New Collegiate Dictionary (1991), in the US,
"#" is used for number and for pounds (weight). And yes, £ is used for
pounds (currency).
Click to expand...

I think that I know that, seeing as I have many in my wallet.
 
Thanks! The issue I was having was when I was using the .Text output. I
need to use the .Value output instead.
 
Dennis Tucker said:
Thanks! The issue I was having was when I was using the .Text output.
I need to use the .Value output instead.
Click to expand...

Well, there are good reasons to use .Text sometimes. I had assumed that's
what you meant.

So your point was well-taken insofar as any VBA code that uses .Text (for
good reason) needs to be prepared to deal with "#" symbols in numeric cells.

But I would not call that a mistake in design. It is simply an arbitrary
decision. My complaint is: I think that Excel is inconsistent in its
handling of column width adjustments.


----- original message ----

Dennis Tucker said:
Thanks! The issue I was having was when I was using the .Text output. I
need to use the .Value output instead.
Click to expand...
 
Back
Top