How do I find and replace null values in Excel 2002 worksheets?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Peter - 27 Oct 2006 00:37 GMT

I have thousands of empty cells which I would like to replace with zeros, but
opening Find and Replace, clearing the Find field and entering 0 in the
Replace field replaces zeros in larger numbers I don't want edited.

Signature

Thank you,
Peter

Reply to this Message

Dave Peterson - 27 Oct 2006 01:01 GMT

Did you leave that What box empty or did you type a spacebar?

> I have thousands of empty cells which I would like to replace with zeros, but
> opening Find and Replace, clearing the Find field and entering 0 in the
> Replace field replaces zeros in larger numbers I don't want edited.
> --
> Thank you,
> Peter

Signature

Dave Peterson

Reply to this Message

Peter - 27 Oct 2006 01:17 GMT

I left the "Find what" field empty. I put a zero (0) in the "Replace with"
field. Had I entered a spacebar in it, I think Excel would not have replaced
any of the empty cells with zeros, which is what I wanted.

Signature

Thank you,
Peter

> Did you leave that What box empty or did you type a spacebar?
>
[quoted text clipped - 4 lines]
> > Thank you,
> > Peter

Reply to this Message

Peter - 27 Oct 2006 01:32 GMT

I think I figured it out, Dave. I tried using: =IF('County Sales
GWh-2'!D2="",0,'County Sales GWh-2'!D2). The formula replaced null values
with zeros in worksheet "County Sales GWh-2" and the non-null values with the
same values from that worksheet.

Signature

Thank you,
Peter Puglia

> I left the "Find what" field empty. I put a zero (0) in the "Replace with"
> field. Had I entered a spacebar in it, I think Excel would not have replaced
[quoted text clipped - 8 lines]
> > > Thank you,
> > > Peter

Reply to this Message

Gord Dibben - 27 Oct 2006 01:41 GMT

F5>Special>Blanks>OK

Type 0 in active cell then hit CTRL + ENTER

Gord Dibben MS Excel MVP

>I have thousands of empty cells which I would like to replace with zeros, but
>opening Find and Replace, clearing the Find field and entering 0 in the
>Replace field replaces zeros in larger numbers I don't want edited.

Reply to this Message

Epinn - 27 Oct 2006 04:53 GMT

In case anyone is interested, F5>Special>Blanks only picks up true blanks but not null strings (="") returned by formulae.

I enter ="" into a blank cell, and it is not replaced by 0.

Epinn

F5>Special>Blanks>OK

Type 0 in active cell then hit CTRL + ENTER

Gord Dibben MS Excel MVP

On Thu, 26 Oct 2006 16:37:02 -0700, Peter <Peter@discussions.microsoft.com>
wrote:

>I have thousands of empty cells which I would like to replace with zeros, but
>opening Find and Replace, clearing the Find field and entering 0 in the
>Replace field replaces zeros in larger numbers I don't want edited.

Reply to this Message