Saved from a previous post:

If you had formulas that evaluated to "" and then converted to values, you can
see a single apostrophe in the formula bar of one of those cells if you toggle
this setting:

Tools|Options|Transition tab|check the transition navigation keys box
(uncheck after you're done checking.)

I like to clean up that detritus with this technique:

select the range (ctrl-a (twice in xl2003) will get all the cells)
edit|replace
what: (leave blank)
with: $$$$$ (some unique string)
replace all

followed by:
edit|replace
what: $$$$$ (that same unique string)
with: (leave blank)
replace all

If you need a macro, record one when you do it manually.


Jamie Furlong wrote:

Long story, but I'm now 4 hours into a simple task. The formula:
=IF(AND(ISBLANK(B18),ISBLANK(F18)),"~~~~",IF(ISBLA NK(TRIM(F18)),,TRIM(F18)))

All those trims are to make absolutely completely sure that when I PASTE
SPECIAL VALUES from the resultant cells of my formula, I need to make sure
that blanks really are blanks. And they are. In fact, I made all the boxes
TEXT format once I'd pasted them. And I went into each on and checked that
they were blank - no hidden spaces or anything.
STILL goto special won't mark them as blank. I REALLY need this urgently,
I've just blown away my Saturday night to get this finished - I really don't
want to be up much past 2am with this!
I'm totally stuck, I've followed 4 different tutorials, watched a video on
it too - I seem to be doing everything right. What now?
Excel 2000 SP3 Win XP Home SP2

Please, any help REALLY appreciated.

--

Dave Peterson