Highlighting blanks via GO TO SPECIAL is not highlighting blank cells - HELP, I'm totally stuck.

Long story, but I'm now 4 hours into a simple task. The formula:
=IF(AND(ISBLANK(B18),ISBLANK(F18)),"~~~~",IF(ISBLANK(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.

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(ISBLANK(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

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" <[email protected]> wrote in message
news:[email protected]...
> 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(ISBLANK(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

You made an unfortunate choice with the tilde ~ and it was even worse that you
used an odd number in your replaces.

~ is a special character.

* is a wildcard that represents anything
? is a wildcard that represents any one character.

To find/replace the asterisk, you give it ~* (kind of an escape character).
Same thing with ? (use ~?). So to tell excel that you don't want to use ~ as an
escape sequence character, you use two of them ~~.

If only you had trusted and used the $$$$$ <vbg>.




Jamie Furlong wrote:
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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(ISBLANK(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

--

Dave Peterson

Oh NOOO!!! I was thinking "OK, he's used $ as a variable, an example".
So I knew that . did something, as did * and various other things, so I
thought the ONE THING that would be benign would be the good old tilde!
In the end, to get it done, I did the "via notepad" trick. This morning,
with the pressure off, I did more searching and came across this link:
http://support.microsoft.com/default...b;en-us;214103
- so they know about it! The easiest thing seems to be:
sort the data to get the "blanks" together, then use "CLEAR > CONTENTS", but
then I get another problem. I can't undo the sort back to the state it was
before, because it wasn't sorted into any state. And I just noticed that
even though I could undo three actions back, it will also undo the
intermediate actions

Finally found the bug reporting page by signing in with Passport, and guess
what? It's an 800 number in the USA. Am I going to try and explain it to the
person on the phone?!
http://support.microsoft.com/gp/contactbug

Downloaded OpenOffice - that behaves far more nicely with respect to the '
situation, but guess what? It doesn't have "GO TO BLANKS", so I'm stuffed
again.

Thwarted at every turn. The KB article says the ' problem applies only to
Excel 2000.

Downloading Office 2003 now....will let you know if problem is fixed. How
can something so simple be so hard?!?

(Now I know how Bush must feel while trying to form a simple sentence
("Families is where our nation finds hope, where wings take dream."))


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> You made an unfortunate choice with the tilde ~ and it was even worse that
> you
> used an odd number in your replaces.
>
> ~ is a special character.
>
> * is a wildcard that represents anything
> ? is a wildcard that represents any one character.
>
> To find/replace the asterisk, you give it ~* (kind of an escape
> character).
> Same thing with ? (use ~?). So to tell excel that you don't want to use ~
> as an
> escape sequence character, you use two of them ~~.
>
> If only you had trusted and used the $$$$$ <vbg>.

> Jamie Furlong wrote:
>>
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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(ISBLANK(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
>
> --
>
> Dave Peterson

If I have to put sort data, but make sure I can put it back in the original
order, I insert a helper column.

I put
=row()
in the top cell and drag down.
Then select that column
edit|copy
edit|paste special values
(to convert to values)

Then I can sort by anything I want. And when I'm done, I just resort by that
helper column (and delete it later, if I want).

Another option may be to apply data|filter|autofilter, show blanks and select
that range and edit|clear contents.

But there are lots of times, this junk is left over in multiple columns.
Sorting or filtering would take lots longer than the mass change stuff.

ps. excel 2003 behaves the same way as xl2k as xl97 as.....

Jamie Furlong wrote:
>
> Oh NOOO!!! I was thinking "OK, he's used $ as a variable, an example".
> So I knew that . did something, as did * and various other things, so I
> thought the ONE THING that would be benign would be the good old tilde!
> In the end, to get it done, I did the "via notepad" trick. This morning,
> with the pressure off, I did more searching and came across this link:
> http://support.microsoft.com/default...b;en-us;214103
> - so they know about it! The easiest thing seems to be:
> sort the data to get the "blanks" together, then use "CLEAR > CONTENTS", but
> then I get another problem. I can't undo the sort back to the state it was
> before, because it wasn't sorted into any state. And I just noticed that
> even though I could undo three actions back, it will also undo the
> intermediate actions
>
> Finally found the bug reporting page by signing in with Passport, and guess
> what? It's an 800 number in the USA. Am I going to try and explain it to the
> person on the phone?!
> http://support.microsoft.com/gp/contactbug
>
> Downloaded OpenOffice - that behaves far more nicely with respect to the '
> situation, but guess what? It doesn't have "GO TO BLANKS", so I'm stuffed
> again.
>
> Thwarted at every turn. The KB article says the ' problem applies only to
> Excel 2000.
>
> Downloading Office 2003 now....will let you know if problem is fixed. How
> can something so simple be so hard?!?
>
> (Now I know how Bush must feel while trying to form a simple sentence
> ("Families is where our nation finds hope, where wings take dream."))
>
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > You made an unfortunate choice with the tilde ~ and it was even worse that
> > you
> > used an odd number in your replaces.
> >
> > ~ is a special character.
> >
> > * is a wildcard that represents anything
> > ? is a wildcard that represents any one character.
> >
> > To find/replace the asterisk, you give it ~* (kind of an escape
> > character).
> > Same thing with ? (use ~?). So to tell excel that you don't want to use ~
> > as an
> > escape sequence character, you use two of them ~~.
> >
> > If only you had trusted and used the $$$$$ <vbg>.
>
> > Jamie Furlong wrote:
> >>
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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(ISBLANK(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
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Dave, you're a star! Of course, once I followed your instructions and used
the $$$$ then it worked fine.
Bit of a learning curve! I think most of my problems are now solved. Thanks
again.

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> If I have to put sort data, but make sure I can put it back in the
> original
> order, I insert a helper column.
>
> I put
> =row()
> in the top cell and drag down.
> Then select that column
> edit|copy
> edit|paste special values
> (to convert to values)
>
> Then I can sort by anything I want. And when I'm done, I just resort by
> that
> helper column (and delete it later, if I want).
>
> Another option may be to apply data|filter|autofilter, show blanks and
> select
> that range and edit|clear contents.
>
> But there are lots of times, this junk is left over in multiple columns.
> Sorting or filtering would take lots longer than the mass change stuff.
>
> ps. excel 2003 behaves the same way as xl2k as xl97 as.....
>
> Jamie Furlong wrote:
>>
>> Oh NOOO!!! I was thinking "OK, he's used $ as a variable, an example".
>> So I knew that . did something, as did * and various other things, so I
>> thought the ONE THING that would be benign would be the good old tilde!
>> In the end, to get it done, I did the "via notepad" trick. This morning,
>> with the pressure off, I did more searching and came across this link:
>> http://support.microsoft.com/default...b;en-us;214103
>> - so they know about it! The easiest thing seems to be:
>> sort the data to get the "blanks" together, then use "CLEAR > CONTENTS",
>> but
>> then I get another problem. I can't undo the sort back to the state it
>> was
>> before, because it wasn't sorted into any state. And I just noticed that
>> even though I could undo three actions back, it will also undo the
>> intermediate actions
>>
>> Finally found the bug reporting page by signing in with Passport, and
>> guess
>> what? It's an 800 number in the USA. Am I going to try and explain it to
>> the
>> person on the phone?!
>> http://support.microsoft.com/gp/contactbug
>>
>> Downloaded OpenOffice - that behaves far more nicely with respect to the
>> '
>> situation, but guess what? It doesn't have "GO TO BLANKS", so I'm stuffed
>> again.
>>
>> Thwarted at every turn. The KB article says the ' problem applies only to
>> Excel 2000.
>>
>> Downloading Office 2003 now....will let you know if problem is fixed. How
>> can something so simple be so hard?!?
>>
>> (Now I know how Bush must feel while trying to form a simple sentence
>> ("Families is where our nation finds hope, where wings take dream."))
>>
>>
>> "Dave Peterson" <[email protected]> wrote in message
>> news:[email protected]...
>> > You made an unfortunate choice with the tilde ~ and it was even worse
>> > that
>> > you
>> > used an odd number in your replaces.
>> >
>> > ~ is a special character.
>> >
>> > * is a wildcard that represents anything
>> > ? is a wildcard that represents any one character.
>> >
>> > To find/replace the asterisk, you give it ~* (kind of an escape
>> > character).
>> > Same thing with ? (use ~?). So to tell excel that you don't want to
>> > use ~
>> > as an
>> > escape sequence character, you use two of them ~~.
>> >
>> > If only you had trusted and used the $$$$$ <vbg>.
>>
>> > Jamie Furlong wrote:
>> >>
>> >> 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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(ISBLANK(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
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson