Cells That Appear Empty?

[Joohn Calder]

Hi

I run Windows 2000 with Excell 2000. I have recently downloaded a large
amouth of data from an Access Database and put in to an Excel spreadsheet.
There are numerous blank cells scattered throughout the data. I discovered
that when doing some formuals that referenced these cells I was getting
errors. I soon discovered that ifI deleted the contents of the blank cell
this would fix the problem, so obviously there is something in the cells that
I cannot see. At first I thought it was probably a "space" that was in the
cell but this was not the case. I also thought maybe it was white formatted
text but this was also not the case. When the curser in in the cell there is
nothing showing in the edit bar. What I did notice was that if I just
"placed" the curser on the cell and placed the curser in the edit bar and
selected ok then it would remove the contents of the cell (whatever they were)

As anyone any ideas on "what" and how I can remove these "invisible" entries
easily without doing it indiviually. I could just sort each column of the
database then delete everthing below the data but as there are many columns
this would take a long time.


Thanks

[RagDyer]

You can try and use "Edit & Replace", *AFTER* you identify the invisible
character.

Say one of these cells was D10.
Enter this in an unused cell:

=CODE(D10)

The value that's returned (32, 10, 160, ... whatever), is what you need to
remove (replace).

Check another of these cells to make sure that they're the same.

NOW, select your range, and:
<Edit> <Replace>

In the "Find What" box,
Hold down <Alt>, and using the num keypad, *NOT* the numbers under the
function keys, enter the *4* digit code that your formula returned:
0032
0010
0160

Of course, nothing will display in the "Find What" box, since these *are
invisible* codes!

Don't put anything in the "Replace With" box.

Then click <Replace All>.

This should convert your empty "looking" cells to be truly empty.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Joohn Calder" <Joohn [email protected]> wrote in message
news:[email protected]...
> Hi
>
> I run Windows 2000 with Excell 2000. I have recently downloaded a large
> amouth of data from an Access Database and put in to an Excel spreadsheet.
> There are numerous blank cells scattered throughout the data. I discovered
> that when doing some formuals that referenced these cells I was getting
> errors. I soon discovered that ifI deleted the contents of the blank cell
> this would fix the problem, so obviously there is something in the cells
that
> I cannot see. At first I thought it was probably a "space" that was in the
> cell but this was not the case. I also thought maybe it was white
formatted
> text but this was also not the case. When the curser in in the cell there
is
> nothing showing in the edit bar. What I did notice was that if I just
> "placed" the curser on the cell and placed the curser in the edit bar and
> selected ok then it would remove the contents of the cell (whatever they
were)
>
> As anyone any ideas on "what" and how I can remove these "invisible"
entries
> easily without doing it indiviually. I could just sort each column of the
> database then delete everthing below the data but as there are many
columns
> this would take a long time.
>
>
> Thanks
>
>
>
>
>
>
>

[Joohn Calder]

Thanks for the quick response

I tried puting the formula you suggested in an empty cell [ =CODE(D10) ] but
it returns a #VALUE! value.

Any other ideas?


"RagDyer" wrote:

> You can try and use "Edit & Replace", *AFTER* you identify the invisible
> character.
>
> Say one of these cells was D10.
> Enter this in an unused cell:
>
> =CODE(D10)
>
> The value that's returned (32, 10, 160, ... whatever), is what you need to
> remove (replace).
>
> Check another of these cells to make sure that they're the same.
>
> NOW, select your range, and:
> <Edit> <Replace>
>
> In the "Find What" box,
> Hold down <Alt>, and using the num keypad, *NOT* the numbers under the
> function keys, enter the *4* digit code that your formula returned:
> 0032
> 0010
> 0160
>
> Of course, nothing will display in the "Find What" box, since these *are
> invisible* codes!
>
> Don't put anything in the "Replace With" box.
>
> Then click <Replace All>.
>
> This should convert your empty "looking" cells to be truly empty.
> --
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
>
>
> "Joohn Calder" <Joohn [email protected]> wrote in message
> news:[email protected]...
> > Hi
> >
> > I run Windows 2000 with Excell 2000. I have recently downloaded a large
> > amouth of data from an Access Database and put in to an Excel spreadsheet.
> > There are numerous blank cells scattered throughout the data. I discovered
> > that when doing some formuals that referenced these cells I was getting
> > errors. I soon discovered that ifI deleted the contents of the blank cell
> > this would fix the problem, so obviously there is something in the cells
> that
> > I cannot see. At first I thought it was probably a "space" that was in the
> > cell but this was not the case. I also thought maybe it was white
> formatted
> > text but this was also not the case. When the curser in in the cell there
> is
> > nothing showing in the edit bar. What I did notice was that if I just
> > "placed" the curser on the cell and placed the curser in the edit bar and
> > selected ok then it would remove the contents of the cell (whatever they
> were)
> >
> > As anyone any ideas on "what" and how I can remove these "invisible"
> entries
> > easily without doing it indiviually. I could just sort each column of the
> > database then delete everthing below the data but as there are many
> columns
> > this would take a long time.
> >
> >
> > Thanks
> >
> >
> >
> >
> >
> >
> >
>
>

[RagDyer]

Is cell D10 one of your problem cells?

I just used it as an example.
It would be one *very big* coincidence if it *was* one of your problem
cells!

A #VALUE! error means the cell of reference is *empty*.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Joohn Calder" <[email protected]> wrote in message
news:[email protected]...
> Thanks for the quick response
>
> I tried puting the formula you suggested in an empty cell [ =CODE(D10) ]
but
> it returns a #VALUE! value.
>
> Any other ideas?
>
>
> "RagDyer" wrote:
>
> > You can try and use "Edit & Replace", *AFTER* you identify the invisible
> > character.
> >
> > Say one of these cells was D10.
> > Enter this in an unused cell:
> >
> > =CODE(D10)
> >
> > The value that's returned (32, 10, 160, ... whatever), is what you need
to
> > remove (replace).
> >
> > Check another of these cells to make sure that they're the same.
> >
> > NOW, select your range, and:
> > <Edit> <Replace>
> >
> > In the "Find What" box,
> > Hold down <Alt>, and using the num keypad, *NOT* the numbers under the
> > function keys, enter the *4* digit code that your formula returned:
> > 0032
> > 0010
> > 0160
> >
> > Of course, nothing will display in the "Find What" box, since these *are
> > invisible* codes!
> >
> > Don't put anything in the "Replace With" box.
> >
> > Then click <Replace All>.
> >
> > This should convert your empty "looking" cells to be truly empty.
> > --
> > HTH,
> >
> > RD
> > ==============================================
> > Please keep all correspondence within the Group, so all may benefit!
> > ==============================================
> >
> >
> >
> > "Joohn Calder" <Joohn [email protected]> wrote in message
> > news:[email protected]...
> > > Hi
> > >
> > > I run Windows 2000 with Excell 2000. I have recently downloaded a
large
> > > amouth of data from an Access Database and put in to an Excel
spreadsheet.
> > > There are numerous blank cells scattered throughout the data. I
discovered
> > > that when doing some formuals that referenced these cells I was
getting
> > > errors. I soon discovered that ifI deleted the contents of the blank
cell
> > > this would fix the problem, so obviously there is something in the
cells
> > that
> > > I cannot see. At first I thought it was probably a "space" that was in
the
> > > cell but this was not the case. I also thought maybe it was white
> > formatted
> > > text but this was also not the case. When the curser in in the cell
there
> > is
> > > nothing showing in the edit bar. What I did notice was that if I just
> > > "placed" the curser on the cell and placed the curser in the edit bar
and
> > > selected ok then it would remove the contents of the cell (whatever
they
> > were)
> > >
> > > As anyone any ideas on "what" and how I can remove these "invisible"
> > entries
> > > easily without doing it indiviually. I could just sort each column of
the
> > > database then delete everthing below the data but as there are many
> > columns
> > > this would take a long time.
> > >
> > >
> > > Thanks
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >

[Joohn Calder]

Thanks again

No D10 is not one of my problems I just used that as an example.

Thanks

"RagDyer" wrote:

> Is cell D10 one of your problem cells?
>
> I just used it as an example.
> It would be one *very big* coincidence if it *was* one of your problem
> cells!
>
> A #VALUE! error means the cell of reference is *empty*.
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
>
> "Joohn Calder" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks for the quick response
> >
> > I tried puting the formula you suggested in an empty cell [ =CODE(D10) ]
> but
> > it returns a #VALUE! value.
> >
> > Any other ideas?
> >
> >
> > "RagDyer" wrote:
> >
> > > You can try and use "Edit & Replace", *AFTER* you identify the invisible
> > > character.
> > >
> > > Say one of these cells was D10.
> > > Enter this in an unused cell:
> > >
> > > =CODE(D10)
> > >
> > > The value that's returned (32, 10, 160, ... whatever), is what you need
> to
> > > remove (replace).
> > >
> > > Check another of these cells to make sure that they're the same.
> > >
> > > NOW, select your range, and:
> > > <Edit> <Replace>
> > >
> > > In the "Find What" box,
> > > Hold down <Alt>, and using the num keypad, *NOT* the numbers under the
> > > function keys, enter the *4* digit code that your formula returned:
> > > 0032
> > > 0010
> > > 0160
> > >
> > > Of course, nothing will display in the "Find What" box, since these *are
> > > invisible* codes!
> > >
> > > Don't put anything in the "Replace With" box.
> > >
> > > Then click <Replace All>.
> > >
> > > This should convert your empty "looking" cells to be truly empty.
> > > --
> > > HTH,
> > >
> > > RD
> > > ==============================================
> > > Please keep all correspondence within the Group, so all may benefit!
> > > ==============================================
> > >
> > >
> > >
> > > "Joohn Calder" <Joohn [email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi
> > > >
> > > > I run Windows 2000 with Excell 2000. I have recently downloaded a
> large
> > > > amouth of data from an Access Database and put in to an Excel
> spreadsheet.
> > > > There are numerous blank cells scattered throughout the data. I
> discovered
> > > > that when doing some formuals that referenced these cells I was
> getting
> > > > errors. I soon discovered that ifI deleted the contents of the blank
> cell
> > > > this would fix the problem, so obviously there is something in the
> cells
> > > that
> > > > I cannot see. At first I thought it was probably a "space" that was in
> the
> > > > cell but this was not the case. I also thought maybe it was white
> > > formatted
> > > > text but this was also not the case. When the curser in in the cell
> there
> > > is
> > > > nothing showing in the edit bar. What I did notice was that if I just
> > > > "placed" the curser on the cell and placed the curser in the edit bar
> and
> > > > selected ok then it would remove the contents of the cell (whatever
> they
> > > were)
> > > >
> > > > As anyone any ideas on "what" and how I can remove these "invisible"
> > > entries
> > > > easily without doing it indiviually. I could just sort each column of
> the
> > > > database then delete everthing below the data but as there are many
> > > columns
> > > > this would take a long time.
> > > >
> > > >
> > > > Thanks
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
>
>

[RagDyer]

Right now, I don't know if you solved your problem , or not!?!?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Joohn Calder" <[email protected]> wrote in message
news:[email protected]...
> Thanks again
>
> No D10 is not one of my problems I just used that as an example.
>
> Thanks
>
> "RagDyer" wrote:
>
> > Is cell D10 one of your problem cells?
> >
> > I just used it as an example.
> > It would be one *very big* coincidence if it *was* one of your problem
> > cells!
> >
> > A #VALUE! error means the cell of reference is *empty*.
> > --
> > Regards,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
-
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
-
> >
> > "Joohn Calder" <[email protected]> wrote in message
> > news:[email protected]...
> > > Thanks for the quick response
> > >
> > > I tried puting the formula you suggested in an empty cell [
=CODE(D10) ]
> > but
> > > it returns a #VALUE! value.
> > >
> > > Any other ideas?
> > >
> > >
> > > "RagDyer" wrote:
> > >
> > > > You can try and use "Edit & Replace", *AFTER* you identify the
invisible
> > > > character.
> > > >
> > > > Say one of these cells was D10.
> > > > Enter this in an unused cell:
> > > >
> > > > =CODE(D10)
> > > >
> > > > The value that's returned (32, 10, 160, ... whatever), is what you
need
> > to
> > > > remove (replace).
> > > >
> > > > Check another of these cells to make sure that they're the same.
> > > >
> > > > NOW, select your range, and:
> > > > <Edit> <Replace>
> > > >
> > > > In the "Find What" box,
> > > > Hold down <Alt>, and using the num keypad, *NOT* the numbers under
the
> > > > function keys, enter the *4* digit code that your formula returned:
> > > > 0032
> > > > 0010
> > > > 0160
> > > >
> > > > Of course, nothing will display in the "Find What" box, since these
*are
> > > > invisible* codes!
> > > >
> > > > Don't put anything in the "Replace With" box.
> > > >
> > > > Then click <Replace All>.
> > > >
> > > > This should convert your empty "looking" cells to be truly empty.
> > > > --
> > > > HTH,
> > > >
> > > > RD
> > > > ==============================================
> > > > Please keep all correspondence within the Group, so all may benefit!
> > > > ==============================================
> > > >
> > > >
> > > >
> > > > "Joohn Calder" <Joohn [email protected]> wrote in
message
> > > > news:[email protected]...
> > > > > Hi
> > > > >
> > > > > I run Windows 2000 with Excell 2000. I have recently downloaded a
> > large
> > > > > amouth of data from an Access Database and put in to an Excel
> > spreadsheet.
> > > > > There are numerous blank cells scattered throughout the data. I
> > discovered
> > > > > that when doing some formuals that referenced these cells I was
> > getting
> > > > > errors. I soon discovered that ifI deleted the contents of the
blank
> > cell
> > > > > this would fix the problem, so obviously there is something in the
> > cells
> > > > that
> > > > > I cannot see. At first I thought it was probably a "space" that
was in
> > the
> > > > > cell but this was not the case. I also thought maybe it was white
> > > > formatted
> > > > > text but this was also not the case. When the curser in in the
cell
> > there
> > > > is
> > > > > nothing showing in the edit bar. What I did notice was that if I
just
> > > > > "placed" the curser on the cell and placed the curser in the edit
bar
> > and
> > > > > selected ok then it would remove the contents of the cell
(whatever
> > they
> > > > were)
> > > > >
> > > > > As anyone any ideas on "what" and how I can remove these
"invisible"
> > > > entries
> > > > > easily without doing it indiviually. I could just sort each column
of
> > the
> > > > > database then delete everthing below the data but as there are
many
> > > > columns
> > > > > this would take a long time.
> > > > >
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> >
> >

[Bill Ridgeway]

If you go to edit a 'blank' cell you may find ' in the cell already. This
is used at the start of the cell to indicate left alignment and in that
position it is a non-printable character. (A second instance would be
printable). These are imported as a filler when importing data (I know not
why) and can cause some confusion.

One way (?the best way) to eliminate these is to sort each column (for ease
of bringing them all together) and pasting a true blank cell over them. The
problem with this is that when sorted back, you will have discontinuous
columns and rows and thing like <Shift><Page><Down> will take you to the
boundary of a blank / used cell not the extreme of the column or row.
Sorting and other functions may also be affected.

Regards.

Bill Ridgeway
Computer Solutions

"Joohn Calder" <Joohn [email protected]> wrote in message
news:[email protected]...
> Hi
>
> I run Windows 2000 with Excell 2000. I have recently downloaded a large
> amouth of data from an Access Database and put in to an Excel spreadsheet.
> There are numerous blank cells scattered throughout the data. I discovered
> that when doing some formuals that referenced these cells I was getting
> errors. I soon discovered that ifI deleted the contents of the blank cell
> this would fix the problem, so obviously there is something in the cells
> that
> I cannot see. At first I thought it was probably a "space" that was in the
> cell but this was not the case. I also thought maybe it was white
> formatted
> text but this was also not the case. When the curser in in the cell there
> is
> nothing showing in the edit bar. What I did notice was that if I just
> "placed" the curser on the cell and placed the curser in the edit bar and
> selected ok then it would remove the contents of the cell (whatever they
> were)
>
> As anyone any ideas on "what" and how I can remove these "invisible"
> entries
> easily without doing it indiviually. I could just sort each column of the
> database then delete everthing below the data but as there are many
> columns
> this would take a long time.
>
>
> Thanks
>
>
>
>
>
>
>

[Ken Wright]

Use Dave McRitchie's Trimall macro anytime you import data externally and it
will clear out all the garbage that can often come with it. Takes a second
or so at the most, and it's one I just wouldn't be without now.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------




"Joohn Calder" <Joohn [email protected]> wrote in message
news:[email protected]...
> Hi
>
> I run Windows 2000 with Excell 2000. I have recently downloaded a large
> amouth of data from an Access Database and put in to an Excel spreadsheet.
> There are numerous blank cells scattered throughout the data. I discovered
> that when doing some formuals that referenced these cells I was getting
> errors. I soon discovered that ifI deleted the contents of the blank cell
> this would fix the problem, so obviously there is something in the cells
> that
> I cannot see. At first I thought it was probably a "space" that was in the
> cell but this was not the case. I also thought maybe it was white
> formatted
> text but this was also not the case. When the curser in in the cell there
> is
> nothing showing in the edit bar. What I did notice was that if I just
> "placed" the curser on the cell and placed the curser in the edit bar and
> selected ok then it would remove the contents of the cell (whatever they
> were)
>
> As anyone any ideas on "what" and how I can remove these "invisible"
> entries
> easily without doing it indiviually. I could just sort each column of the
> database then delete everthing below the data but as there are many
> columns
> this would take a long time.
>
>
> Thanks
>
>
>
>
>
>
>