Blank Field

E

Eric

I transferred data from one excel file to another. Upon
transferring the data as Value only, the new excel file is
capturing a hidden character in the new sheet. Any idea
how I can tell excel to ignore all blank fields on an
excel spreadsheet ?
 
H

Harlan Grove

I transferred data from one excel file to another. Upon
transferring the data as Value only, the new excel file is
capturing a hidden character in the new sheet. Any idea
how I can tell excel to ignore all blank fields on an
excel spreadsheet ?
Click to expand...

What's the hidden character, as in CODE(CellContainingHiddenCharacter)? And what
do you mean by 'transferred' in actual Excel terminology - copied and pasted,
used formulas, something else?
 
E

Eric

-----Original Message-----
...

What's the hidden character, as in CODE
Click to expand...
(CellContainingHiddenCharacter)? And what
do you mean by 'transferred' in actual Excel terminology - copied and pasted,
used formulas, something else?
Click to expand...
then take all their data and place it into a workbook. I
have a formula that tells excel to count certain cells.
However, it appears whe I did the Copy and Paste
Special "Value" it placed a hidden character in the blank
fields and my workbook isn't calculating data correctly.
i'm now getting #Value in my calculation fields. i know my
formula is correct because if I click on the blank cell
and hit the delete key, the formula works
 
H

Harlan Grove

...
...
However, it appears whe I did the Copy and Paste
Special "Value" it placed a hidden character in the blank
fields and my workbook isn't calculating data correctly.
i'm now getting #Value in my calculation fields. i know my
formula is correct because if I click on the blank cell
and hit the delete key, the formula works
Click to expand...

So it's copy & paste.

When this occurs, locate the 'blank' cell (I'll use X99 as the cells address
below - replace with the actual cell address), but before clearing its contents,
enter this formula in another cell: =CODE(X99). Is this formula's result either
32 or 160 or #VALUE! ? If it's 32 or 160, you could use Edit > Replace,
replacing a normal space or a nonbreaking space, respectively, with nothing. If
the formula returns #VALUE!, you have one of Excel's subtlest 'features' to deal
with - a zero length string without a label prefix. If that's the case, only
macros would spare you from manually clearing all such cells.
 
D

Dave Peterson

Did the original cell contain a formula that evaluated to "".

If you pasted as values, then excel still thinks that cell isn't empty.

You can see the apostrophe by turning on a 123 option:
Tools|options|Transition tab|check "Transition navigation keys"

Take a look at the formula bar for one of those offending cells.

(I change this setting back when I'm done inspecting.)

One way to change these cells to empty is to:
Select all the cells (ctrl-A (twice in xl2003))
Edit|replace
what: (leave blank)
with: $$$$$ (some unique unused characters)

Then reverse it:
Edit|replace
what: $$$$$
with: (leave blank)
 
Top