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

[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.

 

[Dennis Tucker]

Try making the column width larger.

 

[Bob Phillips]

Those are hash signs not pound signs, a pound is £

 

[Fred Smith]

Because you haven't given it enough room to display the result. Widen the
column, or use a smaller font.

Regards,
Fred

 

[Dennis Tucker]

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.

 

[Gord Dibben]

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

 

[Dana DeLouis]

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

= = = = =

 

[Dana DeLouis]

... when a macro reads the contents

of the same cell, ##### is returned instead of the proper value.

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

 

[Gord Dibben]

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

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


Gord

 

[Chip Pearson]

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.

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

 

[Gord Dibben]

Thanks for the contribution Chip.

But I'm sure you didn't inhale<g>


Gord

 

[Joe User]

Those are hash signs not pound signs, a pound is £

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 -----

 

[Bob Phillips]

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).

I think that I know that, seeing as I have many in my wallet.

 

[Dennis Tucker]

Thanks! The issue I was having was when I was using the .Text output. I
need to use the .Value output instead.

 

[Joe User]

Thanks! The issue I was having was when I was using the .Text output.
I need to use the .Value output instead.

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 ----

Thanks! The issue I was having was when I was using the .Text output. I
need to use the .Value output instead.