Finally clear - I think ;-)
The offending "character" was a Word "space" within the word table
fields. But it seems that not all spaces are equal. I think I figured
out what was causing the odd [to me] behavior.
What I find interesting, is that the Word "space" was not fully visible
to the Excel sheet - not in the formula bar - not it the cell- and
cannot be "deleted" or backspaced over. You cannot copy it to notepad.
The =count formula *does* reveal its existence.
Also, it seems that table justification transfers from Word, since my
offending text character, 123.4 was right justified in the Excel sheet -
which is why it was hard to notice as text. If you enter 123.4 [on a
european region box]into a cell in a new row, Excel sees that as text
and automatically left justifies, but the toolbar does not display the
"justification". If you right justify it it moves to the right in the
cell and the justification is visible in the toolbar. But if then, in
the formula bar, you add spaces after the 123.4 the spaces are there in
the bar as additional text, but that does not result in the cell
displaying the spaces. The visible characters remain right justified -
no spaces. Excel obviously does not want you using spaces to align what
is in the cell
So in the word table, the "spaces" were visible [in Word] when exposed
under the view options [seen as a horizontal colon character] but I
suspect are actually cruff from some previous Word right indent
configuration on that table. I presume they are a "hidden" space created
by word when the right indent was applied at some point, and for
whatever reason, got orphaned. I can find no option in Excel that would
"expose" the character other than the =count formula. Since they are a
hidden Word element, maybe that accounts for why you cannot delete or
remove them from an Excel cell using the obvious means - you can only
overwrite - which is why the zero's did the trick.
Thank you for the assistance
Excel will always treat an empty as zero. But cells that look empty
aren't necessarily. If you have a formula in them, even one that returns
a null string (""), the cell is not empty.
Regards,
Fred
Hi Fred,
Maybe I am missing something. Select show formulas in options and some
of the C and D cells are still blank [in my original sheet] - the
formula was in E cells - and they are visible. This is table data that
was copied from a Word document. The #VALUE! error seems to also be
caused by the blank cells after some 30 odd rows or so - and I presumed
there is a limit on how may empties it can digest as zero's before
complaining. [Excel 2000]
Not trying to be argumentative - just want to be sure I understand what
is causing the behavior. This is about as basic as a spreadsheet can
get. Filling in zeros seems to have resolved the issue - there are some
170+ rows in all.
vjk
