How to clear 'blank' cells

[Guest]

I have imported a text file from a payroll company that is formatted. Each
employee's name is moved to a new column - the first occurrence of a name is
in H6. The second occurrence of a name is in H18. When I try F5, special,
blank, OK = uparrow to cell above, Ctrl, Enter, Excel cannot find blanks -
even though the cell is "blank" there has to be something in it - I just
can't see it. When I press the Delete key in H7-17, and do the F5 routine,
it works. I've tried the Remove All Spaces macro, to no avail. Column H is
a copy, paste special values from another column that has formulas in 7-17.

Any ideas?

TIA

 

[Dave Peterson]

If you use =len(a1) (change the address to one of the problem cells), do you see
0?

I've never seen this problem with text files being imported, but I have seen it
with formulas that evaluate to "" and are converted to values.

like: =if(a1=3,"",a1+7)

This leaves the cell looking blank, but it's not.
(Try =isblank() against one of the offending cells.)

And you can see the "detritus" left behind by toggling a setting.

Tools|options|transition tab|check transition navigation keys.

You'll see an apostrophe in the formula bar with that cell selected.

===
I like this way to clean up that type of "blank" cell.

select the range (or the whole sheet)
edit|replace
what: (leave blank)
with: $$$$$ (some unique value--not used!)
Replace all

Then reverse it:
edit|replace
what: $$$$$ (that same value)
with: (leave blank)
Replace all


==
If you're using VBA, you could record a macro when you do those two Edit|Replace
and plop that into your code to clean up that junk.

 

[Guest]

The $$$ did it, Dave. Thanks so much!

Carole

If you use =len(a1) (change the address to one of the problem cells), do you see
0?

I've never seen this problem with text files being imported, but I have seen it
with formulas that evaluate to "" and are converted to values.

like: =if(a1=3,"",a1+7)

This leaves the cell looking blank, but it's not.
(Try =isblank() against one of the offending cells.)

And you can see the "detritus" left behind by toggling a setting.

Tools|options|transition tab|check transition navigation keys.

You'll see an apostrophe in the formula bar with that cell selected.

===
I like this way to clean up that type of "blank" cell.

select the range (or the whole sheet)
edit|replace
what: (leave blank)
with: $$$$$ (some unique value--not used!)
Replace all

Then reverse it:
edit|replace
what: $$$$$ (that same value)
with: (leave blank)
Replace all


==
If you're using VBA, you could record a macro when you do those two Edit|Replace
and plop that into your code to clean up that junk.