Refreshing Blank Cells as Blank

  • Thread starter Thread starter Jim May
  • Start date Start date
J

Jim May

I've just performed a huge Copy PasteSpecial Values and a great number of the
cells (the pre-formula) equated to "" (a zero string). Now I need to access
these same cells using F5 SprecialCells Blanks, but get message "No cells
found" unless
I do an F2 (edit) on each cell. Of course I'd prefer not to have to do
that. How can I "convert" these zero string cells to blank cells?

TIA,
Jim
 
Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

===============
You may want to change the original formula so that it's a bit easier:

=if(somethingistrue,somecalc,na())

Then instead of getting that "empty" looking cell, you'd end up with #n/a's.

And since you're converting to values, it's just one edit|Replace to get rid of
the #n/a's.
 
Back
Top