How can I test if a cell is empty?

  • Thread starter Thread starter Victor Delta
  • Start date Start date
V

Victor Delta

I have a web programme (over which I have no control) which outputs data to
an excel spreadsheet.

Some cells appear empty and I want to highlight them using conditional
formatting. However, when I use the ISBLANK function, it transpires that the
apparently empty cells do have something in them, even though I cannot
identify what it is. The cells appear blank when you select each cell -
however, when I press delete and enter, something is clearly being removed
as the conditional formatting then shows the cells as empty. I hope this
makes sense.

Has anyone else every encountered this situation please?

Is there a way of getting round this either by using a function which will
clear the apparently empty cells, or another function I can use with
conditional formatting to identify the so called empty cells.

Thanks,

V
 
I think I would prefer to just delete them as part of the import
macro......or, ASAP Utilities, a free add-in available at
www.asap-utilities.com has a feature that will delete them.

Vaya con Dios,
Chuck, CABGx3
 
Victor Delta said:
Thanks, I'll give it a try.
Once I know what's in the cells presumably I can then use that with the
conditional formatting?
Click to expand...

This is very odd! I installed Cell View only to discover that the cells in
question are indeed completely empty!

However, in this condition, they do not allow ISBLANK to be true. This only
happens when you select the cell and press backspace followed by enter.

This doesn't seem to make sense. Can anyone understand the logic (or
illogic?) of this - and how I can overcome the problem please?

V
 
Did the cells used to contain formulas that evaluated to ""?

Like
=if(a1>7,"ok","")

And did you convert those formulas to values?

If yes...

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
 
CLR said:
I think I would prefer to just delete them as part of the import
macro......or, ASAP Utilities, a free add-in available at
www.asap-utilities.com has a feature that will delete them.
Click to expand...

Many thanks. Have installed ASAP - looks very useful indeed - but cannot
find the particular facility you are referring to. Can you point me in the
right direction please.

Thanks

V
 
Dave Peterson said:
Did the cells used to contain formulas that evaluated to ""?

Like
=if(a1>7,"ok","")

And did you convert those formulas to values?

If yes...

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
Click to expand...

Dave

Many thanks and full marks. Yes, the apostrophe was there and after I did
the two Find/Replace runs, the conditional formatting immediately indicated
the empty cells as I had originally intended.

Despite years of using Excel, I don't think I've ever used the Transition
tab before. What are Transition Navigation keys etc used for?

Thanks again

V
 
They are used by ex-Lotus users.

MS developed Excel with these features in hopes of luring Lotus users away from
Lotus 1-2-3


Gord Dibben MS Excel MVP
 
A longgggg time ago, Microsoft and Lotus were in a battle for customers. In
order for MS to make it easier for longtime 123 users to make that switch to
excel, MS included some transition settings that made excel behave the same way
123 does.

Try toggling that setting and then hit the Home key, ctrl-home key or tab key.
You'll see a difference in excel's behavior.

If you've never used Lotus 123 (or only use excel), I'd recommend turning all
those transition settings off.

Victor Delta wrote:
 
Dave Peterson said:
A longgggg time ago, Microsoft and Lotus were in a battle for customers.
In
order for MS to make it easier for longtime 123 users to make that switch
to
excel, MS included some transition settings that made excel behave the
same way
123 does.
Try toggling that setting and then hit the Home key, ctrl-home key or tab
key.
You'll see a difference in excel's behavior.
If you've never used Lotus 123 (or only use excel), I'd recommend turning
all
those transition settings off.
Click to expand...

Many thanks - very interesting.

Long time since I last used Lotus 123 (on an early IBM PC using MS DOS) so,
as you say, I'll leave the transition settings off!

V
 
Back
Top