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

Tech-Archive recommends: Fix windows errors by optimizing your registry

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 spread***
> 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/microsoft.public.excel.work***.functions/msg/28dd13ad403b6685
> 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/microsoft.public.word.numbering/msg/8cb8d820b863993d
> 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" <petersod@xxxxxxxxxxxxxxxx> wrote in message
> news:4310EB9A.B6A2D1DE@xxxxxxxxxxxxxxxxxxx
> > 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
.

Quantcast