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

Check out this nefty add-in from Chip Pearson........it clearly identifies
the invisible control characters.

http://www.cpearson.com/excel/CellView.htm

Vaya con Dios,
Chuck, CABGx3




"Victor Delta" wrote:

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

"CLR" wrote in message
...
Check out this nefty add-in from Chip Pearson........it clearly identifies
the invisible control characters.

http://www.cpearson.com/excel/CellView.htm

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?

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" wrote:

"CLR" wrote in message
...
Check out this nefty add-in from Chip Pearson........it clearly identifies
the invisible control characters.

http://www.cpearson.com/excel/CellView.htm

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?

V

"Victor Delta" wrote in message
...
"CLR" wrote in message
...
Check out this nefty add-in from Chip Pearson........it clearly
identifies
the invisible control characters.
http://www.cpearson.com/excel/CellView.htm
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?

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(a17,"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

Victor Delta wrote:

"Victor Delta" wrote in message
...
"CLR" wrote in message
...
Check out this nefty add-in from Chip Pearson........it clearly
identifies
the invisible control characters.
http://www.cpearson.com/excel/CellView.htm
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?

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

--

Dave Peterson

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

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" wrote in message
...
Did the cells used to contain formulas that evaluated to ""?

Like
=if(a17,"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

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

On Wed, 8 Aug 2007 21:03:36 +0100, "Victor Delta" wrote:

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?

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:
<<snipped

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

--

Dave Peterson

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

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