ignoring empty???

[sybmathics]

Hi all,

one of my students presented me with the following:

"I get a list from another application.
There is a column that has either value "yes" or looks like empty.

Now, when I want to jump to the next row that has value "yes" I use the
shortcut key Ctrl+ArrowDown.
However, sometimes when a cell looks like empty there may be a space,
with the result that when I need to jump to the next "yes" cell I end up
in a cell that has a space. (one that looks empty)
So, I used the function Trim to get rid of all the superfluous spaces in
an extra column. And also pasted the result as values.
But now the strangiest thing occurs:
When I use Ctrl+Arrowdown my selection immediately jumps to the last
cell in the hole column, where you would expect the selection to be the
next cell with a "yes".
And when you check the now looking like empty cells they really are
completely empty, but someway or another Excel still doesn't regard them
as empty. with the result that you immediately jump to the last row,
instead of to the next not-empty cell.

I have have made a few checks myselfe and experienced the same thing.

When you want a workaround for this problem you first need to understand
where the problems comes from.
I can't figure this one out.

Anybody got any ideas???


Any suggestion is greatly appreciated.


Sybolt

 

[Sandy Mann]

What do you get if you use:

=LEN(A15)

(A15 is just an example use one of your *empty looking* cell references)

What do you get with:

=CODE(A15)


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Gord Dibben]

"from another application"

Which would that be?

Perhaps the spaces are non-breaking html spaces.......0160 character

TRIM and CLEAN will not get rid of these.

Try Edit>Replace

What: Alt + 0160 using NumPad

With: nothing

Replace all.


Gord Dibben MS Excel MVP

 

[sybmathics]

Sandy Mann schreef:

What do you get if you use:

=LEN(A15)

(A15 is just an example use one of your *empty looking* cell references)

What do you get with:

=CODE(A15)

yes, tried that, but the result stays the same.

as a matter of fact i have tried of possible workarounds, but they all
end up in the same problem. Where I cannot understand why, and more
cannot explain how to solve the problem.

 

[sybmathics]

Gord Dibben schreef:

"from another application"

Which would that be?

Perhaps the spaces are non-breaking html spaces.......0160 character

TRIM and CLEAN will not get rid of these.

Try Edit>Replace

What: Alt + 0160 using NumPad

With: nothing

Replace all.


Gord Dibben MS Excel MVP

I have tried most of your suggestions already.

But it looks like it doesn't matter where the data come from.

If you simply create in a new workbook a colum where sometimes in a cell
you have an entry, some times you have a blank cell and sometimes you
have a lookalike empty (with a space or spaces).
When you jump around with the key combination Ctrl+Arrowdown you would
select the last cell with an entry so also the cells with a space. Now,
if you remove the spaces with the trimfunction and next paste the result
as values, you are not able to jump to the next filled cell, but you
will end up in the last cell in the column.

I even tried saving the file and closing and then reopening, which
sometimes helps. But no.

I'm really puzzled about this one.

Try it, Gord.

This is a strange one.


Greets,

Sybolt

 

[Sandy Mann]

yes, tried that, but the result stays the same.

But what returns do you get from these formulas?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Dave Peterson]

If you used: =trim(a1) and it returned nothing (essentially ="" as a formula),
then converted these formulas to values, then this would apply:

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

 

[sybmathics]

OK, Dave.

Replacing solved the problem.

Thanks a lot.

Sybolt