I'm really grateful to you for trying to help, and I can now see that this
little tip has clearly worked for some other people, but SOMETHING is still
wrong!
For example:
FIND (blank)
REPLACE ~~~~~

FIND ~~~~~
REPLACE (blank)
leaves me with ~ visible and '~ in the formula bar, and yes, I've been back
and forth and tried it with and without that transition key.

FIND ~ finds nothing, even when ~ is left in the cell, as does FIND '~

Googling a bit more revealed that this bug is catching out quite a few
people, and probably explains why I totally had to give up on a spreadsheet
about 8 months ago.
If so many people know about it, how come there isn't a fix yet?

Anyway, I tried something else:
http://groups.google.co.uk/group/mic...dd13ad403b6685
says:
--------------
"After you've done Paste Special Values, select the range and run this
1-line macro:
Selection.Value = Selection.Value
This will make the cells with the "" in them truly blank. "
--------------

Macros aren't my specialty, so I found this post:
http://groups.google.co.uk/group/mic...b8d820b863993d
which says:

--------------
From just a general part of the document (nothing
selected), go Tools / Macro / Visual Basic Editor
Click in the window down low called "Immediate"
Type this exactly:
ActiveDocument.ConvertNumbersToText
then press Enter
(You may notice that, as you type, a balloon of options
pops up. You can double-click on "ConvertNumbersToText"
and that will help, but you still have to press Enter to
activate it.)
It'll seem like nothing has happened ... but ...
Go File / Close and Return to Microsoft Word
--------------

Of course, before doing that, I'd made my selection and changed the line
from
ActiveDocument.ConvertNumbersToText
to
Selection.Value = Selection.Value
but still no better.

BUT WAIT! I just had a cunning plan - I copied the entire column including
the ~ that was left over from the back and forth find and replace, pasted it
into notepad, did a find and replace on that, then pasted the entire column
back in - hey presto, it actually seemed to work!

But honestly, this is a ridiculous thing to have to do. Does MS have a bug
tracking system or some place I can add my name to the list (I'm guessing
this bug is already on a list to do somewhere!)

Does anyone know if it's fixed in Office 2003? 'cos during my many many many
hours of travelling the net to try and find an answer, I noticed that you
could have 60 days of 2003 to play with. If someone can confirm that this is
fixed, I'll go with that version.

Thanks again for help so far.

"Dave Peterson" wrote in message
...
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